Altering a Formula based on Month

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
598
Office Version
  1. 2019
Platform
  1. Windows
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:

Code:
='[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?





 
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.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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. :mad:
 
Upvote 0
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.
 
Upvote 0
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)
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,276
Messages
6,171,138
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top