Substitute the name of a sheet with a cell number

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
142
Office Version
  1. 365
Platform
  1. Windows

Book1
BC
1Start
2Vanguard01-Feb-19
Index
Cell Formulas
RangeFormula
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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")
 
Upvote 0
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. :)
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top