Dynamic named range of non contigious cells

[Herakles]

Hi All

I would like to be able to create a number of dynamic named ranges (DNR) using non contigious cells.

I can identify the rows to use in the cells in each DNR in an array formula on the worksheet e.g True,False,False but how can I use that in the 'Applies to' in Name Manager?

I also need to be able to offset the range X number of columns to the left. The offset can be hard coded in the DNR 'Applies to'.

Thanks for any assistance and pointers that you can give me.

Herakles

 

[Joe4]

I would recommend building the range first as a range variable in Excel (you can combine different cells into a range using UNION).
Then once complete, assign the name to that range.

See: Use VBA Union to Combine Ranges - wellsr.com

 

[Herakles]

I would recommend building the range first as a range variable in Excel (you can combine different cells into a range using UNION).
Then once complete, assign the name to that range.

See: Use VBA Union to Combine Ranges - wellsr.com

Thanks for your response.

I did not want use VBA as I woule have to recreate one of the named ranges each time a row is added to a table.

If I have to then I will use VBA but if I can avoid it I will.

 

[Joe4]

Unless you want to manually create/edit those named ranges any time there is a change, I think you may need VBA.
I am not sure how to create a "dynamic named ranges", or if if is even possible, without VBA.

 

[Herakles]

It is unfortunate that the INDIRECT function cannot be used with non-contigious cells else I would have cracked it.

Not every project has an easy fix in Excel, no software could facilitate that.