Reference using worksheet name

[Guest]

Hi,

Thanks in advance

Column B = Ref No of Client (which = worksheet Name(Which is a No)

I want column C to Look up Cell B(Which equals to Worksheet Name) Followed
By Cell C5 of That Woksheet to give be contents of that cell. How do I link
it

Col B C
Row 2 80 =Worksheet(B2) & CellC5

(Should be looking up Worksheet 80 & Cell C5

Your urgent help is required

 

[Ardus Petus]

Try:
=INDIRECT("Worksheet"&b2&"!C5")

HTH

 

[Guest]

Put in C2: =INDIRECT("'"&B2&"'!C5")
Copy down

 

[Hollingshead]

That was really helpful and solved a big problem for me. But- where did you
find the syntax for a worksheet reference in the Indirect function? My help
file only explains cell references.

Thanks,
Kevin

 

[Gord Dibben]

Help explains that INDIRECT uses the cell contents of the referenced cell.

In this case B2 contains the sheetname.

You could also use a construct like this if your sheetnames are the standard
sheet1, sheet2 etc.

=INDIRECT("Sheet" & (ROW()) & "!C5")

entered in row 1 and copied down.


Gord Dibben MS Excel MVP