3

I am really stuck with a problem using excel macro and named range. I have dynamic formula for Named Manager using formula 

=OFFSET('Sheet1'!$A$2,0,0,COUNTA('Sheet1'!$A$2:$A$1000),1)

The Data validation for Name is applied to Col A on Sheet2. Now the Col B value should be populated based on the value selected in Col A. So I am using the Indirect function using data validation:

=IF(A1="","",INDIRECT(A1))

This function is not working when I use dynamic named range 

=OFFSET('Sheet1'!$B$2,0,0,COUNTA('Sheet1'!$B$2:$B$1000),1)

But works if the range is direct. I really want this to work dynamically. Can anyone please provide me a work around for this? Thanks a lot for your time.

guitarthrower
  • 5,624
  • 3
  • 29
  • 37
RakeshP
  • 141
  • 5
  • 15

2 Answers2

0

This line you mentioned:

=IF(A1="","",INDIRECT(A1))

is not working because "" is not a valid list for the data validation. So it is throwing an error. To make this work you'll need to create 2 new names. I've listed below what I used, but you can change names to suit your purpose.

  • Name 1: rng_ChangeSelection
  • Refers to: ="Change Selection"
  • Name 2: list_datavalidation
  • Refers to: =IF(Sheet1!$A$1="",rng_ChangeSelection,INDIRECT(Sheet1!$A$1))

Then in the source of the data validation you will enter

=list_datavalidation

This will refer to the formula that was a problem when entered into the data validation source.

Data validation has some limits on what you can do, but the named ranges get around those limits. Now, when A1 is blank the validation list will say they need to change the selection (you can edit the message as needed). And if it is not blank it will use the list (if it exisits), and if it doesn't exits, there will be no list to choose from.

I would also suggest adding some conditional formatting to visually flag the user when they have not entered something correctly.

guitarthrower
  • 5,624
  • 3
  • 29
  • 37
0

I've also found that the combination of INDIRECT and OFFSET does not work. This is annoying, as if we have a dynamically sized named range (using OFFSET or INDEX) then we can't refer to this via INDIRECT - instead we get a #REF error.

The reason for this I would speculate is that the call to INDIRECT resolves to the formula for the named range, which it can't then evaluate.

So your call will boil down to:

=IF(A1="","",INDIRECT("RangeName"))

and I speculate that RangeName will then substitute in the named range formula as so:

=IF(A1="","",INDIRECT("OFFSET('Sheet1'!$B$2,0,0,COUNTA('Sheet1'!$B$2:$B$1000),1)")

which yields a #REF error because INDIRECT is incapable of resolving a formula. I can't think of an option after that that doesn't resort to using a macro (or at least the EVALUATE trick, though that still requires your workbook to have macros enabled).

theStrawMan
  • 235
  • 2
  • 9
  • I think this is the same problem as in [this](https://stackoverflow.com/questions/14856889/referencing-dynamic-named-range-in-excel-formula?rq=1) question (which I only found later) – theStrawMan Feb 08 '18 at 02:04