Link to home
Create AccountLog in
Avatar of Doug Van
Doug VanFlag for Canada

asked on

Replacing text in a formula with a variable

Hello,

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.
Avatar of Arana (G.P.)
Arana (G.P.)

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,"")
Avatar of Doug Van

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you all. I appreciate your help and patience. :)