name ranges in nonadjacent cells

rj183600

New Member
Joined
Sep 22, 2002
Messages
4
I cannot seem to get excel to recognise a named range of non-adjacent columns. Say I want to name a range existing out of column A and column E only.

I can create names for A and E separately, and create a name for A to E. But what about A and E together. If you highlight both columns and go to insert, name, define it shows =sheet1!$A:$A, sheet1!$E:$E, but it won't recognise the name afterwards. In particular I want to link the spreadsheet to Access, but only these named ranges.

Is this possible without reformat the spreadsheet so the columns are adjacent?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

rj183600

New Member
Joined
Sep 22, 2002
Messages
4
If you define a named range, when you highlight that range then the name shows in the top left hand "names" box. But if I create the range for non-adjacent cells, when I highlight them then it doesn't show me a name in the names box.

Also, if you then try to link the named range into access, access doesn't see that the spreadsheet has a named range.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The range must be a rectangle of cells for its name to appear in the Names box. Your range refers to a union of cells.

I expect the same is true for Access.
 

Forum statistics

Threads
1,144,209
Messages
5,723,039
Members
422,476
Latest member
beck85

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