Is it possible to produce a dynamic named range that chooses between multiple pre-existing named ranges using a formula?
Specifically I have two ranges as follows:
Based on the selection by a user in a third cell (VIC or QLD) I want the named range PriceList to point to appropriate range.
I realise I can do this in VBA. However some of the places these ranges are used are in Validation Lists - and I keep having issues changing the validation list in a cell (something about formula1 being read only). A dynamic named range seems a reasonable alternative - but I can't seem to get my head around how to build one that works. I need something like
Range Name : PriceList
Refers To : =if(A1="VIC",PriceListVIC,if(A2="QLD",PriceListQLD,""))
But .. something like this that works, 'cause this doesn't ... is this possible?
Thanks Ken
You might have a named range called 'PriceList' and specify it as
=INDIRECT(Sheet1!$A$1)
Now, depending upon whether you write "PriceListVIC" or "PriceListQLD" in Sheet1!A1 the third range 'PriceList' will refer to one of the other named ranges.
This works in validation lists as well.
Este artigo é coletado da Internet.
Se houver alguma infração, entre em [email protected] Delete.
deixe-me dizer algumas palavras