I have a spreadsheet that utilizes the indirect function for data
validation. This works great except I have to manually change the
range size if I add more data to a specific range.

In comes =Offset() that will allow me to dynamically set the range
depending on the last cell. However, once I set this up (which works),
the indirect function does not work correctly.

Any ideas, here are the formulas that I'm working with:

For the Dynamic Ranges

=OFFSET('IACL Order Form'!$AO$2,0,0,COUNTA('IACL Order Form'!$AO:
$AO),1)

For the Indirect List

=INDIRECT(SUBSTITUTE($A18," ",""))

So A18 has the category which then shows the items for that category
in B18.

Any help would be appreciated.

The way I do it is to name the first cell in the dynamic list, let's say
Names, and also name the column, say NamesCol, and in the DV use a formula
of

=OFFSET(INDIRECT($A18),0,0,COUNTA(INDIRECT(A18&"Co l")),1)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
I have a spreadsheet that utilizes the indirect function for data
validation. This works great except I have to manually change the
range size if I add more data to a specific range.

In comes =Offset() that will allow me to dynamically set the range
depending on the last cell. However, once I set this up (which works),
the indirect function does not work correctly.

Any ideas, here are the formulas that I'm working with:

For the Dynamic Ranges

=OFFSET('IACL Order Form'!$AO$2,0,0,COUNTA('IACL Order Form'!$AO:
$AO),1)

For the Indirect List

=INDIRECT(SUBSTITUTE($A18," ",""))

So A18 has the category which then shows the items for that category
in B18.

Any help would be appreciated.