DV selection to trigger a reset or redefine of a named range ??

[sschwant]

say you have 4 cols A:D and a worksheet full of calcs currently tied to col C
End user selects either Yes/No from a DV drop down.
If yes, then the sheet calcs need to refer to col C
If no, then the sheet calcs need to refer to col B
Rather than implement a bunch of IF Then logic embedded in each formula, is there
a way to have an event triggered based on the DV selection?
The event would reset a named range to point to either col, e.g., if Yes, then
name col C as the range, else if No, then name Col B as the range.
Then all the formulas would just refer to the range and would not need the boolean logic
embedded in each cell.
Something like:
If cell "B3" = "Yes", call sub(rename range to point col C), if no, then call sub(rename range to point to col B .........
Thoughts?
Thanks,
Steve

 

[Hui]

Are you using Excel or VBA ?

Can you please post the file or a sample file so we can be more specific

You have the facilities to use either Choose(), Indirect(), Index() or Offset() to choose different sets of data in Excel

In VBA you can pretty much do whatever you want but without specifics it is hard to assist

 

[NARAYANK991]

Hi Steve ,

Suppose your data is in the range A5:D1000 ; I am assuming that your data starts from row 5 , since you say that you have a DV dropdown in cell B3.

Create a named range called SelectedRange , and in its RefersTo box , enter the formula :

=IF($B$3="YES" , $C$5:$C$1000 , $B$5:$B$1000)

Since your DV dropdown has only 2 choices , the IF statement is a simple way of doing it ; functions such as CHOOSE , INDIRECT ,... need to be used only if there are several choices available , since with many choices , an IF formula becomes unwieldy.

If you are using this in VBA , you can use [SelectedRange] to refer to the named range.

Narayan