Replacing text in a formula with a variable

you can use a cell that will not get printed and use that as the source for the month value

Is JanSun1 a defined name which holds a valid date value?

Can you upload a sample file?

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,"")

Thank you all, my apologies.

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.

How about uploading a sample file with minimal/faked data so that direct testing could be done?

Hello Saqib,

Thank you for your offer of assistance. I explained what I am looking for on the sheet. :)

https://drive.google.com/open?id=1EiInt1u51TPSpFpZ9yY7ZL8CG0dWSJyI

You could use the INDIRECT function, for example

=INDIRECT(A1)

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

Thank you all. I appreciate your help and patience. :)