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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,138
Office Version
  1. 365
Platform
  1. Windows
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)
 

busy_bee

New Member
Joined
Mar 9, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,138
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Forum statistics

Threads
1,136,275
Messages
5,674,783
Members
419,524
Latest member
helensesc

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