Reference using worksheet name
 

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

Reference using worksheet name
 

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

HTH
--
AP

"Trever B" a écrit dans le message de
news: ...
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

Reference using worksheet name
 

Put in C2: =INDIRECT("'"&B2&"'!C5")
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Trever B" wrote:
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

Reference using worksheet name
 

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


"Max" wrote:

Put in C2: =INDIRECT("'"&B2&"'!C5")
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Trever B" wrote:
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

Reference using worksheet name
 

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


On Tue, 23 Sep 2008 08:30:13 -0700, Hollingshead
wrote:

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


"Max" wrote:

Put in C2: =INDIRECT("'"&B2&"'!C5")
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Trever B" wrote:
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