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?

 

[nniedzielski]

I have it copied right, and it works inside the cell Icopied into,
But, the E does not turn color as if its connected to acell, and when I place my curser next to it and hit F4, nothing happens, sosomething is weird, I have tried copying it and recopying it a few times andjust typing it out, nothing will allow it to paste down and change the rownumber.

 

[Special-K99]

Can you post a link to the file?

 

[craigexcel]

Isn't the issue that -- &"'!$E4" -- is part of the INDIRECT function and is enclosed in double quotes, and therefore, when the formula is copied, that will always remain the same?
Can't find my workbook where I did this before, so can't offer a solution right now.

 

[craigexcel]

Perhaps this link might help:
https://www.ozgrid.com/forum/forum/...ndirect-function-with-changing-cell-reference

 

[mgd_Excel]

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.

Remember you have the references wrapped in " " so the copy down will not work as you are describing. I would suggest how to get around this but I am sure you know how to. So you may make the correction needed.

 

[Special-K99]

Oh yes youre right!

Try this (untested)

=INDIRECT("'[1st Shift Creation Macro.xlsm]"&UPPER(TEXT(TODAY(),"mmm"))&"'!$E"&(ROW()-1)+ROWS($1:1))

This should allow you to place the formula on any row (if it changes in the future)

 

[nniedzielski]

Thank you for your help on this, this is returning a result, however when I copy down, im getting every other line from the original worksheet, so it isn't giving me row 1,2,3,4,5 its giving me 1,3,5.

do you know why?

 

[mgd_Excel]

Thank you for your help on this, this is returning a result, however when I copy down, im getting every other line from the original worksheet, so it isn't giving me row 1,2,3,4,5 its giving me 1,3,5.

do you know why?

This is because the dynamic part (ROW()-1)+ROWS($1:1)) is evaluating to the sequence you describe.

Do not be afraid to play around with this part. I would just use Row() and then add a constant if necessary.
Row() just returns the row number the formula is in.