Excel referencing problem

[shep6]

Hi there

Bit of a difficult one to explain, but here goes....

I have 5 sheets in my workbook, the first sheet is a summary sheet which contains data held on any one of the the other 4 sheets. The user selects which sheet they want to see data for via a combo box. I then populate a cell dynamically with the sheet name. My question is how do I use the value in the cell I have populated to be included in the cell reference.

eg. Combo box contains: Sheet2
Sheet3
Sheet4
Sheet5

The user selects Sheet3, I then populate cell "L8" on Sheet1 with "Sheet3", I do this via a vlookup function.

Cell "b7" on Sheet1 (the summary sheet), needs to be populated with the value that is in B7 on Sheet3
(ie Sheet3!B7).

I can get "B7" on Sheet1 to say "=Sheet3!B7", but that is all, it doesn't return the actual value on Sheet3.

Any help would be appreciated.

cheers

 

[xlbo]

You need to use the indirect function:
Presumably, In B7, you have:
=L8 & "!B7"

Should read:
=indirect($L$8 & "!B7&quot

HTH Rgds
~Geoff~

 

[shep6]

Thanks for this, it solves half my problem, the only problem is that I have around 700 rows on the summary sheet, so would like to just be able to drag down and automatically populate with the next row.

eg, on the next row down on the summary sheet it would be:

=indirect($L$8 & "!B8&quot then the next row would be
=indirect($L$8 & "!B9&quot and so on....

I've had a quick play around, but can't quite get it to work, do you know if there is a way??

cheers

 

[xlbo]

Try this:
=indirect($L$8 & "!B" & row())

HTH Rgds
~Geoff~

 

[shep6]

Thanks Geoff, just the job!!!