• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Cells Change by using Drop down

bexcelusr

New Member
I can't figure out the correct If statement ( i think that would the best solution) for the sample spreadsheet attached. I need cells B6 and B7 to change based on the selection of the dropdowns in B1 through B3. I've hardcoded the correct answer in B6 and B7 to illustrate what my answer needs to be.

Thank you for your help - I am stuck.
 

Attachments

bosco_yip

Excel Ninja
Try,

In B6, copied down to B7 :

=SUMPRODUCT(VLOOKUP($A6,$D$2:$H$3,CHOOSE({1,2,3},RIGHT($B$1)+1,($B$2="Include")*3+1,($B$3="Include")*4+1),0))

Edit : Your Lookup Table cell D2 "Property 1" has 1 extra space in between and should be removed

Regards
Bosco
 
Last edited:

bexcelusr

New Member
Thanks for this. I'm trying to expand the lookup by year and am having a hard time adjusting the formula to expand. I've attached another sample file.

I've listed the correct answers in the sample file for the drop down selections I have chosen. When the drop downs are changed the answers will need to change as well.
 

Attachments

Peter Bartholomew

Well-Known Member
I found all the chasing around the Data table with formulas too complicated so I just went for a single nice simple array formula!

I assume you will find the approach somewhat alien but, while debugging it, I discovered that your validation list entry "Include " has a following space which caused the tests for "adj 1" and "adj 2" to fail. I hope that helps.

= IF( NetInc_1or2="Net Inc 1", NetInc1, NetInc2 ) +
IF( Adj_1="Include", Adjust1 ) +
IF( Adj_2="Include", Adjust2 )
 

Attachments

Last edited:

bexcelusr

New Member
This is helpful. But I'm a bit stuck again. My question is, I am having a hard time copy and pasting the array formula on the summary tab. I need it to apply to a much larger range and I've expanded the ranges in the name manager but when i copy and past on the summary tab, it simply refers to original cell i copied. for example if i copy cell c7 into cell c9, the result is still the number in c7. The goal of this spread sheet is to have cells C9:H56 interact and operate in the same dynamic manner that cells C7:H8 operate. Obviously Property 3 needs correspond with the data listed for property 3 on the data tab and so on down the line. Thanks for any help on this.
 

Attachments

Peter Bartholomew

Well-Known Member
To extend an array formula, start by selecting the top-left (anchor) cell of the range and then follow with Shift+Left-Mousebutton to select the entire range that you wish to extend the formula to (Ctrl+Shift+ArrowKey can also be useful for such selections). The anchor-cell formula is then committed (again) using Ctrl+Shift+Enter. To change an array formula it is only necessary to select a single cell (usually the top-left) and use CSE once the formula has been edited.

One way of reducing an array formula to a non-array form is to select the entire region and commit the formula using Ctrl+Enter. This can be a key step if you ever wish to reduce the size of an array formula. If Excel moans at you about changing part of an array formula hit Esc and start the step again.

When I worked on your problem examply, I moved the tables to stack them vertically since that allowed me to see them all on-screen. With the bigger datasets you may prefer to have them side by side, especially if they extend down over time. For that, move the named ranges as single blocks, either using drag-and-drop of Ctrl+C / Ctrl+V.

Something else you might find useful is to convert the input data blocks (currently sized at 50x6) into Tables. That way, if your dataset grows, the named ranges follow dynamically, though the output array will need to be changed manually. You can select table options that get rid of the stripes and drop-down buttons that make tables such a visual 'treat'. These changes are shown as Sample v4 (PB).xlsx

Finally, do you use Office 365 or one of the fixed versions? The new versions of Excel 365 include Dynamic Arrays which offer a far better user experience than either traditional single-cell working or CSE arrays.
 

Attachments

Top