Avatar of Doug Van
Doug Van
Flag 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.
Microsoft OfficeSpreadsheetsMicrosoft Excel

Avatar of undefined
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,"")
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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.
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. :)

https://drive.google.com/open?id=1EiInt1u51TPSpFpZ9yY7ZL8CG0dWSJyI
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rob Henson

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
Saqib Husain

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Doug Van

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