Excel Named Range

busy_bee

New Member
Joined
Mar 9, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I have an Excel file with and image of Sheet1 attached below.

Image1.JPG

I am trying to create a named range with a formula. I would like to set the range from cell A2 till the last row in Column A. I attempted to set the following formula in the Formulas-Define Name-Define Name section.

=OFFSET(Sheet1!$A$2,0,0,COUNTBLANK(Sheet1!$A:$A)+COUNTA(Sheet1!$A:$A)-1,1)

But this does not seem to set the range correctly. Any suggestions on how to resolve this?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
It is working correctly, just not as expected. When you use countblank on an entire column, you're including over a million empty cells below the last entry, all of which are blank.

This should work based on the example, although I would advise getting out of the habit of merging cells, they are one of the best ways of breaking an otherwise perfectly good spreadsheet.
Excel Formula:
=OFFSET(Sheet1!$A$2,,,MATCH("zzz",Sheet1!$A:$A),1)
 
Upvote 0
It is working correctly, just not as expected. When you use countblank on an entire column, you're including over a million empty cells below the last entry, all of which are blank.

This should work based on the example, although I would advise getting out of the habit of merging cells, they are one of the best ways of breaking an otherwise perfectly good spreadsheet.
Excel Formula:
=OFFSET(Sheet1!$A$2,,,MATCH("zzz",Sheet1!$A:$A),1)
The named range is being created correctly. I apologize for the mistake.

I am now trying to create a drop down list on Sheet2 with Unique values in Column A on Sheet1 by utilizing following Formula for the List.

=SORT(UNIQUE(FILTER(List1,List1<>""))).

This Formula correctly outputs a list of unique values in Cell C4 on Sheet2 as in the attached image but throws an error when I try to create a drop down list.

Image2.jpg
 
Upvote 0
If you're trying to use the sort formula to generate the dropdown then that is something that will definitely not work.

As you have the dynamic array functions, the easiest way would be to pass the function into a worksheet, as you have already done. Then use =$C$4# as the validation list source.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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
Back
Top