Dynamic named range of non contigious cells

Herakles

New Member
Joined
Jul 5, 2020
Messages
44
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 5, 2020
Messages
44
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 5, 2020
Messages
44
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,366
Messages
5,601,207
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top