Altering a Formula based on Month

[nniedzielski]

I have two workbooks that are communicating with each other,
One workbook is used by a clerk and has data entered intoit, the other work book is displayed on a monitor outside of the office.
I am trying to get the two workbooks to talk to each otherso the clerk doesn’t have to pull the display sheet in each time there is data.
I created a formula to get the two sheets to talk, but thisneeds to happen for each day of the month, as each workbook has a tab for days1-31.


This is the formula being used:

='[1st Shift Creation Macro.xlsm]DEC1'!$E4

What I need is to be able to create a template for the sheetwhere the “DEC” in the formula will change from month to month,
How can I get the formula to change from JAN to FEB and soforth as the months change?

 

[Special-K99]

Try

=INDIRECT("'[1st Shift Creation Macro.xlsm]"&UPPER(TEXT(TODAY(),"mmm"))&"'!$E4")

 

[nniedzielski]

That did not connect, keep in mind its got to find the Sheet called AUG15 first, I don't think that it found it this way.

 

[Special-K99]

Didnt see the day number in your description
try

=INDIRECT("'[1st Shift Creation Macro.xlsm]"&UPPER(TEXT(TODAY(),"mmmd"))&"'!$E4")

 

[nniedzielski]

I am just getting a #REF ! warning in the cell, it doesn't seem to connecting to the other worksheet

 

[Special-K99]

Think it should be this

=INDIRECT("'[1st Shift Creation Macro.xlsm]"&UPPER(TEXT(TODAY(),"mmmd"))&"'!$E4")

If it doesn't work just take the INDIRECT() off the formula then you can see what value it's creating

The above formula is producing this

'[1st Shift Creation Macro.xlsm]AUG16'!$E4

 

[nniedzielski]

This works for the cell, but it will not copy down,

 

[Special-K99]

"...but will not copy down"

What does that mean?

You're getting an eror?
You're just getting blank cells?
You're getting the wrong values?
Something else?

 

[nniedzielski]

sorry for not elaborating, when i copy down, the formula stays the same in each row, $E4 is in row 5, 6, 7, 8 and so on,

 

[Special-K99]

No that cant happen.

$E4 the E is anchored by the dollar, that means if you copy it to other columns the E will be in each cell, in fact it will always say $E4 since the row doesn't change
If you copy E4 to other columns then it will change to F G H since the $ is not anchored.

Similarly, the 4 is not anchored so if you copy the formula down the column it will change to row 5 6 7 etc

You must have copied it wrong.

Copy the cell
and paste down the column, the row should change.