Substitute the name of a sheet with a cell number

[danhendo888]

Book1
	B	C
1		Start
2	Vanguard	01-Feb-19
Index

Cell Formulas
Range	Formula
C2	=Vanguard!C2

The formula is pointing to a sheet whose name is in the first column.
How can I incorporate >> B2 into the formula in C2? so that I can click and drag the formula down

 

[Peter_SSs]

Re: How do I subtitute the name of a sheet with a cell number

Try
=INDIRECT(B2&"!C2")

Note though that INDIRECT is a volatile function so can slow you sheet if over-used.

 

[danhendo888]

Re: How do I subtitute the name of a sheet with a cell number

=INDIRECT(B2&"!C2")

Hi Peter,
Thank you for your solution.

I am getting an REF error for some of them, I am not sure why.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://1drv.ms/x/s!AvjBsEPEq12ngSu79egQrQk66XId?e=QNkLNO

Column C shows a list of company names. Each name has a sheet in the workbook.
Column D retrieves the value of cell C2 for each sheet
[/FONT]
Do you know why the formula is showing a REF error in D4?

 

[DanteAmor]

Re: How do I subtitute the name of a sheet with a cell number

The Ref error may be because the sheet does not exist or is misspelled in the cell or because the name has spaces, in this case, try this:

=INDIRECT("'" & C4 &"'!B4")

 

[Peter_SSs]

Re: How do I subtitute the name of a sheet with a cell number

Do you know why the formula is showing a REF error in D4?

It is the spaces in the sheet name, as Dante has suggested as a possibility, & his formula should solve your problem.

 

[danhendo888]

Re: How do I subtitute the name of a sheet with a cell number

Thank you Dante and Peter, very happy with the solution.
I appreciate it

 

[Peter_SSs]

Re: How do I subtitute the name of a sheet with a cell number

Thank you Dante and Peter, very happy with the solution.
I appreciate it

Glad you got a satisfactory outcome.

 

[DanteAmor]

Re: How do I subtitute the name of a sheet with a cell number

Thank you Dante and Peter, very happy with the solution.
I appreciate it

I'm glad to help you. Thanks for the feedback.