Excel Grand Master Required

danza

danza

danza

Soldato
Joined
28 Mar 2005
Posts
13,679
Location
Drunken badger punching
Right, I've been tasked with making it difficult to balls up data entry in a spreadsheet at work. Some of us are epic at Excel, I am not quite so (yet). The idea with the task is that there are separate columns for each snippet of information that makes up the designation of a product, which will make it a lot harder to make duplicate entries, balls-up formatting or make mistakes. Hopefully.

I have a front page, sheet 1, and I need the highlighted column to auto-populate based on inputs on sheets 2 & 3 where columns A to E are concatenated into column F. What I want to happen, is when a number is typed into column A on sheets 2 & 3, some magical function on sheet 1 in the cells in column B checks whether that number exists in column A on either sheets 2 or 3, and if so, it fetches the full description from the same row in column F, and dumps it next to the corresponding No. in column A, sheet 1.

I've messed about a bit with 'IF' & 'OR' etc., but I can't make it jive. Anyone got any ideas?

mPIgFz4.png


12qmdpZ.png


c6UKYxp.png
 
It's been a while but something like this should work if you can move Sheet3 into Sheet2.

=INDEX(Sheet2!G2:G6, MATCH(A2,Sheet2!A2:A6, 0))
 
danza said:
Cheers, but unfortunately the sheets need to stay separate.
Click to expand...

index match works across different sheets

something like:

index(column f, match( cell on sheet 2 with number, column with number corresponding to value in column f))

you can calculate for each subsection you want then concatenate, or keep it in 1 cell just by having &" "& between each index sub-formula (you can replace the space inbetween the quotation marks with whatever you want to use to seperate each part of the code such as - or . )

the short version is match looks for the value of the cell you've given it in the range it's given, then returns a number based on how far down that range it is. index takes that number and looks that many cells down it's range and gives you the value there, you can add anything you like to the index portion.

if you want to make it truly foolproof i'd suggest looking into drop down boxes for your input values, you can have a code generator on the front page (or a seperate page) that'll mean people have to pick the prescribed options. if you want to get really fancy you can delve into the deep world of macros to give you a button to automatically copy a generated code to the last empty cell on your order list
 
Last edited:
Put this in cell B2 on sheet 1 and copy down:
=IFERROR(IFERROR(INDEX(Sheet2!G:G,MATCH(A2,Sheet2!A:A,0)),INDEX(Sheet3!G:G,MATCH(A2,Sheet3!A:A,0))),"Not Found")

I created a skeleton of the sheets you screenshotted above to create it, so that should work.

To explain, it's basically saying if it can't find the number in sheet 2, it will look in sheet 3 and if it can't find it in either sheet, it will return 'Not Found'.
 
Ace, cheers! The added IFERROR is probably what I'd have been asking about tomorrow when I couldn't get rid of a load of #N/As.

Thanks for the detailed descriptions, most helpful. Macros could be the next step, but I'll learn to walk first. I've got tomorrow to sort out a really messy spreadsheet, tidy it up and make it closer to foolproof than it currently is.
 
danza said:
Ace, cheers! The added IFERROR is probably what I'd have been asking about tomorrow when I couldn't get rid of a load of #N/As.

Thanks for the detailed descriptions, most helpful. Macros could be the next step, but I'll learn to walk first. I've got tomorrow to sort out a really messy spreadsheet, tidy it up and make it closer to foolproof than it currently is.
Click to expand...

try the drop down boxes first- theyre much easier than macros.

i'll be honest whilst i've used macros a lot, and can do some editing/tweaking of them, mostly i just find something that's approximating my need on stack exchange (stack overflow? something like that) and tweaking it to fit my needs.
 
danza said:
Right, I've been tasked with making it difficult to balls up data entry in a spreadsheet at work
Click to expand...

Step back and think. Is Excel the right tool for the job here? Might you be better off using Access? Avoid free text entry as much as possible, and if you do allow it, remember to sanitise it by performing a conversion to lower or upper or proper case, trimming spaces, etc before trying to do a match.
 
I really need to go on a proper Excel course. At work I frequently make very basic spreadsheets but then some wizz comes along and blows my stuff to pieces with his work. I'm amazed at how powerful Excel can be in the right hands.
 
Right, Tingle's function works great for searching in 2 sheets (sheets 2&3), but what do I do to it in order to add extra sheets to search through (I have many extra sheets)? Everything I do seems to end up with the error message of too few arguments.
 
I'd use a vlookup to bring through the information.

It's also possible to use contextual drop downs using Cell Validation rules, i.e. if you select a certain product type subsequent drop downs have the appropriate possible entries available in another cell.
 
danza said:
Right, Tingle's function works great for searching in 2 sheets (sheets 2&3), but what do I do to it in order to add extra sheets to search through (I have many extra sheets)? Everything I do seems to end up with the error message of too few arguments.
Click to expand...

depends how you want to do it.

you could have a calculation sheet with an index match formula in each column then replace the number in the search range (the sheet#! where the # is the sheet number replaced by &"column()"& which will return the column number the formula is in as a substitute for just typing the sheet number) this will only work if your sheets have a consistent naming scheme (like for example the default sheet 1, sheet 2, sheet 3 etc). this might be better if your planning to add sheets in future.

or you could simply have again a bunch of index-match functions in one cell to search each sheet in turn, with an iferror value of "" (so if it cant find it on the sheet its looking for it returns nothing) each linked by just an & so:
iferror(index(.....,match(sheet 2....,....,....)),"")&iferror(index(.....,match(sheet 3....,....,....)),"")&iferror(index(.....,match(sheet 4....,....,....)),"") etc all in one cell
 
As far as I can tell, you're going to need to use VBA if you want to search many sheets. The logic would be to create a function, loop through each sheet, on each sheet loop through the 'No.' column until you get to the bottom, if it's found then exit the loop and return the value next to it (the full description). Otherwise, if you get through all the sheets and it still isn't found, return 'Not Found'.
 
The index and match combo is powerful enough to do what you want. I agree with Quartz though, if you want it to be a bit more restricted and return values fast with a simple data entry form Access would be better. If its designed right and uses indexes instead of repeating information, you can make it fast and effective.

I have seen with work projects that spiral out of control when you are going from hundreds of records into thousands that a database is far better. Excel for rapid development and looking fancy, Access for volume.
 
adolf hamster said:
depends how you want to do it.

you could have a calculation sheet with an index match formula in each column then replace the number in the search range (the sheet#! where the # is the sheet number replaced by &"column()"& which will return the column number the formula is in as a substitute for just typing the sheet number) this will only work if your sheets have a consistent naming scheme (like for example the default sheet 1, sheet 2, sheet 3 etc). this might be better if your planning to add sheets in future.

or you could simply have again a bunch of index-match functions in one cell to search each sheet in turn, with an iferror value of "" (so if it cant find it on the sheet its looking for it returns nothing) each linked by just an & so:
iferror(index(.....,match(sheet 2....,....,....)),"")&iferror(index(.....,match(sheet 3....,....,....)),"")&iferror(index(.....,match(sheet 4....,....,....)),"") etc all in one cell
Click to expand...


As I've got older and started running teams rather than being a member of them, I prefer not to use formulas that do multiple things in one go.

While they're great when you write them, often I go back and have to unpick even my own work because I can't remember what bit of cleverness I thought of that day I built a model!

Never mind when you're working with people who aren't as good with Excel, they have no chance of fixing or amending your work.

I appreciate everything should be documented, but in reality it never happens to the extent it should and you end up with black boxes that do stuff but no one really knows how or has the time to find out until it's too late.
 
You must log in or register to reply here.
Back
Top Bottom