I am trying to use the value of one cell to define where another cell looks to get another value.
Fer ex:
A B C D E
1 [ ]
2
3 [ ]
If I insert “5” into cell A1, how do I get cell C3 to look up the 5th row, 1st col of page two and return the value of that cell? But C3 needs to be dynamic. That is, if I change A1 to “4”, I need Excel to look up what’s in the 4th row.
Using the “CELL” function, I am running into trouble nesting the “ADDRESS” function. ADDRESS works fine on its own, but nesting a perfectly working ADDRESS just doesn’t work - and I can’t figure out why. Trying to form a text string and use that in CELL doesn’t work, either.
Ex: Indirect(“pagetwo!A”&A1) would give you the value stored in Pagetwo’s A5 if the value “5” were in A1
A simpler formula could be Indirect(A1) but then A1 would then have to have the text string “A5” entered in to it.
I use Excel 2007 so please forgive me if the Indirect function isn’t available to you.
I went nuts with this function when I discovered it. Combining some concatenation with this and a combo box and you can create a report sheet that grabs data off of one chosen page from a bunch. I had separate pages of data for each month of the year and one page that would create a summary report for whichever month was entered into a cell. I used a combo box to select the month from a list, drop it into cell A1 and then every formula on the sheet used an indirect statement to instantly grab info from the chosen data sheet. It worked much faster than using a macro to do the same work.
If you number the columns you can do the same thing with Hlookup
A B C D E F
1____1 2 3 4 5
2[4]6 6 8 9 10
3___[9]
C3 =HLOOKUP(A2,$B$1:$F$2,2,0) THE 2 SIGNIFIES THE ROW YOU WANT RETURNED AND THE ZERO ON THE END FORCES AN EXACT MATCH.