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.

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.

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.