Log In
Register
Home
New Member
April 11, 2013
Offline
Hello,
I have a template with a lot of named formulas that use their relative position and I was looking to use a Data Validation(?), Indirect function (?) looking for mechanism to trigger all the cells to change from using one named formula to another based on a cell selection.
The tab I'm looking to accomplish this on is Current; I'm looking to swap between choices of 1)Amounts_Invoiced, 2)Current_Accrual, 3)Gross_for_Items_with_Activity_Range, 4)Invoice_basedon_InvoiceDate_Activity_Date and 5)Hold_Back_Basedon_Activity_Date.
As I understand, because of how the named formulas require the lead "=" I'm struggling to figure this one out. One additional caveat I'm trying to accomplish this without using VBA/Macros to make it user friendly for the novice Office user.
I'm using Excel 2010 and/or Office 365.
Thank you much,
David
Trusted Members
February 13, 2021
Offline
Hi David, welcome!
It took me a minute but I have an idea for you!
In my idea you will need 2 helper cells, but if you use the choose function you are golden, I think! So in one helper cell you will have a drop down list with your 5 options of which named formula you want to use, the second cell will assign that option a number (I used the "If" formula because it was easiest for my purposes). Finally, your final displayed formula will be "=choose("Cell in which your index number is located", Amounts_Invoiced,Current_Accrual,Gross_for_items_with_Activity_Range,Invoice_basedon_InvoiceDate_ActivityDate,Hold_Back_Basedon_ActivtyDate). My helper cells are right at the top, Drop down in cell E3 and my example cell is D5. 
1 Guest(s)
