Lookup data on another sheet 1

[cdiwindows]

I have a spreadsheet with several hundred tabs. On one of the "master" tabs, I have a list of products and one of the columns is the tab name on which the information resides.

Under "Formula", I need to retrieve cell J7 from the sheet name under the column "Sheet"

Item Desc Sheet Formula
2040 Item1 M124 ='M124'!J7
1258 Widget3 U234
1278 Widget7 X123

How do I insert the value of one cell into a formula within the single quotes?

Thanks for your help.

 

[SkipVought]

Hi,

Don't worry about quotes!!!

just start your formula and then select the sheet and range. Excel puts it in your formula just right!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cdiwindows]

Skip,
When I entered =H14!J7 (H14 is the cell the sheet name resides), I get an "Update Values: H14" dialog box come up. I clicked cancel and get a #REF! error. The cell address is then in where sheet name is, not the value that is in H14.

 

[SkipVought]

The ONLY thing that you should have 'entered' is the EQUAL sign! You should NOT type anything else in this case!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cdiwindows]

I will need to copy this formula down for a couple hundred rows, each looking at a different sheet, but the same cell on each sheet (all will look at J7). I have to enter formulas in 7 other columns all referencing the the sheet specified in column 14.

 

[SkipVought]

You need ought to take a class in basic Excel.

with J7 selected in your formula. hit the F4 key 4 times and observe what happens.

The DOLLAR SIGN $, means ABSOLUTE. See About cell and range references in Excel Help.

All you need do is 1) COPY the cell with this formula, 2) select the cells you want the formula to be in, ALL skeen-teen-zillion rows, 3) Edit > Paste.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

please forgive me for not reading your original post closer.
[tt]
Item Desc Sheet Formula
2040 Item1 M124 ='M124'!J7
1258 Widget3 U234
1278 Widget7 X123

[/tt]
In the Formula column, I assume D2,
[tt]
=INDIRECT(C2&"!J7")
[/tt]
So sorry!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cdiwindows]

Thank you Skip. I was just trying to restate what I was looking to do when you posted again. That is exactly what I was looking for.

 

[SkipVought]

Sorry that I jumped to conclusions.

Glad its working for you. Have a nice weekend!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}