MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Reference to another sheet name in formulas

Posted by David on January 25, 2001 6:20 PM


I must to do a spreadsheet with one page for each
day of the month. I'll put in each page many formulas
with references to cells in the previous page.
Is there a way to automatize this job?
In an example: on sheet 3 there will be lots of cells
with formulas like '2'!k22+'2'!n43. I'd like to put
the name "2" on a cell and reference his content, so
I could duplicate the sheet many times and change just
one or two cells to correct the values.
Does anybody knows how can I do it? I found the
INDIRECT function, but I was unable to use it.
Thanks in advance.

Posted by Dave Hawley on January 25, 2001 6:31 PM

Hi David

It is the INDIRECT formula you need. You would use it like this:

=Sheet1!A1+INDIRECT(Sheet3!$A$1&"!" & "A1")

Where A1 on sheet 3 contains the text: Sheet2

Hope this helps


OzGrid Business Applications

Posted by David de Carvalho on January 31, 2001 6:55 PM

Thanks for the help, Dave, but when I use the
formula, I get an #NAME? error on the cell.