Use a drop down to change a variable in a function

[KC E]

Is there a way to use a drop down list to change a variable (or a reference, I don't know the right word) within a function?

I have attached a file where I have raw data and a pivot table of the raw data. The raw data is mainly showing how many Days Past Due a project is, so it involves dates.

I would like to create a drop down where I can have users select whether they want to see how many days out a project is as of a date I enter into $Q$1 on the 'Data' sheet or whether they want to see how many days out a project is as of today using the TODAY() function. The TODAY() function is already being used in the formulas located in columns U to Y on the Data Sheet.

Example of what my formulas in columns U to Y look like: =IF($Q3>TODAY(),"",IF(AND($R3="", TODAY()-$Q3>0, TODAY()-$Q3<=30),1,""))

1) what formula do I put in the Source field of the Data Validation box to replace the TODAY() part of the formula with the date in $Q$1 for all of the formulas located in columns U through Y on the Data sheet when a user selects 'Date from $Q$1' from the drop down field? Then it replaces the $Q$1 reference in the formula back to the TODAY() function when the user selects the TODAY() function from the drop down field? Does it involve INDIRECT? If so, how does that work, please?

Thank you. Please let me know if you have any questions.

P.S. I use Excel 2010.

 

[NARAYANK991]

Hi ,

Would it not be better if you replaced references to TODAY() in your formula by the cell reference , so that what ever date users enter in the cell , the formula will use that date ?

=IF($Q3>$Q$1,"",IF(AND($R3="", $Q$1-$Q3>0, $Q$1-$Q3<=30),1,""))

However , looking at your formula , can it not be simplified ? The cases being considered are :

1. Q3 is greater than today's date

2. Q3 is strictly earlier than today's date

3. Q3 is equal to today's date

Since the output in case 1 and case 3 is a blank , why not combine them together , and use :

=IF(AND($R3="", $Q$1-$Q3>0, $Q$1-$Q3<=30),1,"")

Narayan

 

[Somendra Misra]

Hi,

Just See another alternative using VLOOKUP.

Regards,

 

[KC E]

Hi ,

Would it not be better if you replaced references to TODAY() in your formula by the cell reference , so that what ever date users enter in the cell , the formula will use that date ?

=IF($Q3>$Q$1,"",IF(AND($R3="", $Q$1-$Q3>0, $Q$1-$Q3<=30),1,""))

However , looking at your formula , can it not be simplified ? The cases being considered are :

1. Q3 is greater than today's date

2. Q3 is strictly earlier than today's date

3. Q3 is equal to today's date

Since the output in case 1 and case 3 is a blank , why not combine them together , and use :

=IF(AND($R3="", $Q$1-$Q3>0, $Q$1-$Q3<=30),1,"")

Narayan

I thought of that last night but by using a drop down menu. The users can just type in the date they want, though. I made this too complicated.

Thank you both for replying.