Use sheet name in formula
 

I have a file with 40 sheets, named 1 - 40. There is 1 form in the file that
will be used to print data from any one of the 40 sheets. I've started a
macro that includes an InputBox asking for the sheet name the user wants to
print. I then need to insert the sheet name into several formulas that
reference different cells on the same sheet. What I need is to be able to
get the formulas on the form to change according to the sheet named in the
InputBox. I've tried lots of different things, but I think I'm getting
stymied on syntax errors. Any help would be much appreciated as always.

Use sheet name in formula
 

I assume the "form" is a worksheet. In a cell, put the sheet name. in the
formulas use indirect

=Indirect("'" & B9 & "'!A5")

where B9 is the cell with the sheet name.

--
Regards,
Tom Ogilvy


"cottage6" wrote in message
...
I have a file with 40 sheets, named 1 - 40. There is 1 form in the file
that
will be used to print data from any one of the 40 sheets. I've started a
macro that includes an InputBox asking for the sheet name the user wants
to
print. I then need to insert the sheet name into several formulas that
reference different cells on the same sheet. What I need is to be able to
get the formulas on the form to change according to the sheet named in the
InputBox. I've tried lots of different things, but I think I'm getting
stymied on syntax errors. Any help would be much appreciated as always.

Use sheet name in formula
 

Tom, thanks so much. This isn't the first time you've answered a question
for me, and I can't thank you enough. I really try to learn on my own, but
sometimes I'm a bit thick I think. I'm not familiar enough with Indirect,
but I guess I'd better start to be.

"cottage6" wrote:

I have a file with 40 sheets, named 1 - 40. There is 1 form in the file that
will be used to print data from any one of the 40 sheets. I've started a
macro that includes an InputBox asking for the sheet name the user wants to
print. I then need to insert the sheet name into several formulas that
reference different cells on the same sheet. What I need is to be able to
get the formulas on the form to change according to the sheet named in the
InputBox. I've tried lots of different things, but I think I'm getting
stymied on syntax errors. Any help would be much appreciated as always.