cell reference

G

Guest

Here is the formula =+'19'!$F$17 where '19' is referencing a tab name
representing the day of the month. I have multiple formulas in the
spreadsheet referencing the current day of the month sheet ie 19 in this
case. How do I embed a cell reference into the formula so that I can just
change one cell from 19 to 20 to 21,etc rather than going to each formula and
changing it to a 20, 21, etc.
 
N

Niek Otten

Hi Libby,

You don't need the + sign. Make sure you have Tools>Options>Transition>all items unchecked, unless you explicitly need them

Look in HELP for the INDIRECT() function

Post again in this thread if you can't get it done

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Here is the formula =+'19'!$F$17 where '19' is referencing a tab name
| representing the day of the month. I have multiple formulas in the
| spreadsheet referencing the current day of the month sheet ie 19 in this
| case. How do I embed a cell reference into the formula so that I can just
| change one cell from 19 to 20 to 21,etc rather than going to each formula and
| changing it to a 20, 21, etc.
| --
| Libby
 
G

Guest

The current formula works as is. I'm trying to modify the current formula so
that I don't have to munually change all of the 19's to 20's tomorrow, then
to 21's the next day, then to 22's, etc.
 
D

David Biddulph

=INDIRECT("'"&A1&"'!$F$17") if you want to put your 19 in A1, or
=INDIRECT("'"&DAY(TODAY())&"'!$F$17") if you want it to change
automatically.

As there are no spaces in the sheet name, you could in fact get away without
the single quotes around the '19', so the formula could be simplified
further.
 
D

Dave Peterson

Another if you need 2 digit days:
=indirect("'" & text(a1,"dd") & "'!f17")

or if you don't need 2 digit days:
=indirect("'" & text(a1,"d") & "'!f17")

In both cases, you don't need the $f$17. Since it's a string, it won't change
if you copy that formula to another cell.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top