Forum Discussion
Drop down formula to update multiple sets of columns based on what is selected
Twifoo Sorry for the delay as my internet was out at my building for almost a week. I have attached an example excel file to show what I am trying to accomplish by having a drop down (list) on one tab that updates a column on the viewing page. My want is to be able to adjust all the numbers based on 2 columns and select what the adjustment is based on a drop down option being selected.
Please clarify in which cells you need a formula for and explain the logic of each.
- MichaelBFromBCNov 29, 2019Copper Contributor
Yes, I am sure that will make it easier to digest.
[Tab](Adding Gains) - Cell A15 is a drop down - The drop down is a list [Tab](Gains B1:P1)
[Tab](Adding Gains) - If Drop down Cell A15 has Gain 2 selected I would like [Tab](Gains) C2:C11 numbers to copy to [Tab](Adding Gains) B2:B11
Whenever the drop down is changed to a different "Gain 1, Gain 2, Gain 3, ect." I would like for that data to be present in that column.
- TwifooNov 30, 2019Silver Contributor
In the attached file, the formula in B2 is shown in the snapshot below:
Similar to B2 and C2, the formula in D2, copied down and across the range is:
=SUM(INDEX(StatData,ROW()-1,
MATCH(D$1,StatLabels,0)),
$B2:$C2)Upon pressing Ctrl+F3, you will see the 4 defined named ranges, as shown in the snapshot below:
- MichaelBFromBCDec 02, 2019Copper Contributor
Twifoo - That is incredibly helpful. I thank you very much as I learned something that I wasnt aware is possible. Really great of you to help me better understand this 🙂