Joer wrote:Bump!
I have an excel question for something that I can't work out how to Google and was hoping someone here could point me in the right direct.
Sheet A contains our list of jobs - Column B contains a data validation list for our customers on Sheet B
Sheet B contains our admin on our customers
What I need to happen is that if our example cell of B2 contains Customer A from the data validation list, then in cell B6 for invoicing notes, it pulls over the corresponding text relating to Customer A from the admin sheet.
Anyone got any ideas on what I need to be Googling to make that happen or know how to do that themselves?
Without the exact data in front of me I'm going to have to extrapolate with examples.
Here's sheet 1
And sheet 2
We know that the names on sheets 1 and 2 are the same, but in a different order. We want to find the addresses and pull the addresses from sheet 1 into sheet 2.
First we use the MATCH functionality to find out where the names in Sheet2, A2 are in Sheet 1, column A. This will give us a reference we can use later. It looks like this, and this is what it returns
That tells us that "Gary" is in the 10th row of data in our function. But hold on - "Gary" is actually in Row 11 of the sheet. Thats because we didn't include our heading in the lookup array (Sheet1!A2:A11 <- note the A2 not A1). We don't want to include the title, because we aren't looking for it, so what we can we do? Well we know that it's only one row, so we can say that for every result, +1 to it and you get the real row. You can imagine what an 11 looks like so lets move on.
OK so we want to use this for every row on sheet 2, so we need to make that look up array constant. Lets do that by plonking $s in. Then drag it on down to the bottom of the row.
That's the easy bit done. We know what row the data is on, so we can use that in our next formula. This time we will use an INDIRECT. This basically returns the content of a cell, but what it also allows you to use a string to determine where it should pull the data from. This allows you to concatenate data together (using &). Like this:
So the INDIRECT here is going to look for "B"&1, which concatenated together is "B1". Note that whenever you use text in an INDIRECT you need to use quotation marks. It then returns the contents of B1 - Addresses 2.
But we want it to look at Sheet 1, so we say
Now, we can put these two statements together. In the first formula, we used MATCH to get the row - so lets use that row information in our INDIRECT. But lets not just reference the cell with the data, let's combine the whole formula into one.
Hope that helps you out.