I think this is very rudimentary but I'm haven't a moment. :(
I am building an Excel calendar with the formula: =DAY(IF(DAY(JanSun1)=1,JanSun1-5,JanSun1+2)).
But I want to be able to set the calendar month dynamically but replacing the hand-coded, "JanSun1" with a variable. That will allow the end-user to type in the month and recalculate the calendar.
How can this be accomplished?
Thank you.
Microsoft OfficeSpreadsheetsMicrosoft Excel
Last Comment
Doug Van
8/22/2022 - Mon
Arana (G.P.)
you can use a cell that will not get printed and use that as the source for the month value
Rob Henson
Is JanSun1 a defined name which holds a valid date value?
Can you upload a sample file?
Saqib Husain
If you can rename the sheet tab name to JanSun1 you can use
=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"") to get the sheet tab name
You might even name the sheet something more meaningful like Data_for_JanSun1 and modify the formula to =REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1))+9,"")
In my haste, I neglected to provide complete details.
I am modifying a calendar template that I pulled from the free Excel templates, under business calendars > expense calendar.
The calendar uses predefined variables, such as JanSun1. They are defined in the Name Manager.
For instance, JanSun1 = "=DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1))"
But my specific question is it possible to make the variable, "JanSun1" a variable?
Allow me to try to describe what I want to do...
Currently, the calendar is designed with all the months listed, beginning with January.
I would like to only display a single month and then allow the end-user to determine which month is displayed by simply selecting the month from a single pull-down menu.
This is only possible if I can substitute the variable "JanSun1" with a new value.
Example (it doesn't actually work)...
This:
=DAY(IF(DAY(JanSun1)=1,JanSun1-5,JanSun1+2))
To this:
=DAY(IF(DAY($selected_month$)=1,$selected_month$-5,$selected_month$+2)).
Where $selected_month$ is selected from a pull-down menu and equals = JanSun1 or MarSun1, etc.
Perhaps this isn't possible?
Thank you.
Saqib Husain
How about uploading a sample file with minimal/faked data so that direct testing could be done?
Doug Van
ASKER
Hello Saqib,
Thank you for your offer of assistance. I explained what I am looking for on the sheet. :)
Will look at the contents of A1 and will interpret as a range address.
So, if you have a dropdown list in A1 with a list of your calendar options, the INDIRECT function will look at the range selected from the list rather than just the cell