Blank cells in named ranges

**JaSonic75** · 04-05-2018, 08:15 AM

Hi,

I have a list of data lets say between J3:J52 and would like to create a drop down box accordingly

I've created a named range between those cells which works the drop down lovely...except

The data is constantly changing and could often result in empty cells which I am after excluding from the drop down.

I've tried formulas in the named range box such as:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

but all of these result in no drop down data at all
Name manager with any of these formulas just shows {...} under value

Please may I kindly ask what I am doing wrong?

Many thanks

**AliGW** · 04-05-2018, 08:17 AM

Attach the workbook.

**JaSonic75** · 04-05-2018, 08:38 AM

File attached

My named ranges are on the dropdowns tab
My dropdowns themselves are on the Print tab

many thanks

**AliGW** · 04-05-2018, 08:40 AM

There are two ways to get at a result here: either change the way that you generate the dynamic lists (my preferred option) or fiddle with the validation formula - which do you want?

**JaSonic75** · 04-05-2018, 08:47 AM

I'll go with your preferred if that's ok please

Many thanks

**AliGW** · 04-05-2018, 09:04 AM

Really sorry - I've been called away. Hopefully someone else will step in.

**AliGW** · 04-05-2018, 09:39 AM

I'm back, but I am afraid I am unable to work with your attachment - it keeps crashing Excel.

**JaSonic75** · 04-05-2018, 10:11 AM

Hmm, strange

I've re-saved the document as macro-free
The only macro that was in there was the 'print rota' button on the Print sheet

Hopefully this version will work.

Many thanks

**JaSonic75** · 04-06-2018, 07:49 AM

Did you get any luck opening the file please ?

Many thanks

**AliGW** · 04-06-2018, 08:04 AM

No - I didn't see it. It wasn't opening the original file that was the issue, it was trying to work with it.

I will have a look at your new file later today.

**AliGW** · 04-06-2018, 08:39 AM

OK, so for example in E3 of your DropDowns tab:

=IFERROR(INDEX(Setup!$C$4:$C$53,MATCH(0,IF(ISBLANK(Setup!$C$4:$C$53),"",COUNTIF(E$2:$E2,Setup!$C$4:$C$53)),0)),"")

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Once confirmed, drag copy down.

**AliGW** · 04-06-2018, 09:13 AM

Let us know how you get on.

**JaSonic75** · 04-06-2018, 12:59 PM

Many thanks for this,

This command helps putting all names at the top so there are no blanks in between,
However, unfortunately the drop down list is full of blanks at the bottom

I've created a Name for the list in name manager and allocated the range: =DropDowns!$E$3:$E$52

Is there a command that can replace that in which will ignore the blanks

I've just tried as recommended from this post:

Please Login or Register  to view this content.

in the name manager but once again that went to {...} again and the drop down does not work

Many thanks

**JaSonic75** · 04-06-2018, 01:22 PM

Just changed it to:

Please Login or Register  to view this content.

unfortunately still the same

**AliGW** · 04-06-2018, 01:32 PM

Can you attach your latest attempt?

**JaSonic75** · 04-06-2018, 01:59 PM

File attached,

Had to edit it down and remove some tabs as is beginning to get rather big lol

Many thanks

**AliGW** · 04-07-2018, 04:42 AM

I will have a gander.

**xlnitwit** · 04-07-2018, 04:53 AM

I haven’t viewed your file yet, but perhaps just amend the formula to
=OFFSET(DropDowns!$E$3,0,0,COUNTIF(DropDowns!$E:$E,”?*”),1)

**Glenn Kennedy** · 04-07-2018, 04:53 AM

Where is the faulty DD being used?

**Glenn Kennedy** · 04-07-2018, 04:55 AM

For dynamic dropdowns which contain formula blanks, I always use this type of formula to make the dynamic DD list:

=OFFSET(Sheet1!$a$2,,,SUMPRODUCT(--(LEN(Sheet1!$a$2:$a$100)>0)))

**AliGW** · 04-07-2018, 05:04 AM

I'm stumped, I'm afraid - sorry.

**JaSonic75** · 04-07-2018, 05:14 AM

The DD is being used on sheet 'Print'

There are 2 drop downs:
D2 = category list
L2 = Dynamic name list

If D2 is set to Tonne_7.5, L2 drop down works but has a magnitude of blanks at the bottom (I have only have the formula =DropDowns!$D$3:$D$52 for this named range at the moment.
If D2 is set to Tonne_3.5, L2 drop down does not work, (I currently have the formula =OFFSET(DropDowns!$E$3,0,0,COUNTA(DropDowns!$E:$E),1) for this named range.

I have just tried:

Please Login or Register  to view this content.

But this had the same result as previous.

Many thanks

**JaSonic75** · 04-07-2018, 05:17 AM

Originally Posted by xlnitwit

I haven’t viewed your file yet, but perhaps just amend the formula to
=OFFSET(DropDowns!$E$3,0,0,COUNTIF(DropDowns!$E:$E,”?*”),1)

Unfortunately this did not work either

Many thanks for all your suggestions/assistance
Very much appreciated

**AliGW** · 04-07-2018, 05:24 AM

If it were me, I'd start again. Something is wrong, but I can't put my finger on what it is. In any case, you probably don't need all the steps you are taking to derive those drop-down lists. There must be an easier way.

I'm afraid I'm not offering to find that easier way for you - don't have time today for a project like that!

**JaSonic75** · 04-07-2018, 05:27 AM

Thankyou very much for taking a look AliGW
Very much appreciated.

Please don't take this the wrong way, But in a way I'm happy it stumped you, being someone who knows there stuff.
I don't feel as dumb as I thought I was being lol

Many thanks

**AliGW** · 04-07-2018, 05:30 AM

I know what you mean - but there are wizards lurking, so we'll probably both end up looking stupid.

**Roel Jongman** · 04-07-2018, 06:20 AM

I think i found a way.
In the formula of AliGW for the dropdowns I changed the result of the IFERROR formule from "" to "-" in column D of Dropdowns-sheet

Please Login or Register  to view this content.

And I also noticed that for some reason when using arrayformula the standard COUNTA formula comes back with 50 all the time so I changed the validation formula for Tonne_7.5 to include a countif formula that counts the number of "-" values and substract it from the countA

Please Login or Register  to view this content.

hope this solves your problem.

**AliGW** · 04-07-2018, 06:31 AM

Well, how's that for a forum debutant's opening post?

Good solution, Roel, although it would be great to get to the bottom of why things were not working as expected. At least Jason has a way to move forward now.

Welcome to the forum!

**Roel Jongman** · 04-07-2018, 06:42 AM

Haha, thanks for the welcome, new here, but been doing "training laps" on other forums (mostly Dutch)

You are right.. it is a bit of "works by accident" solution, I think the COUNTA formula might even be obsolete in the named range formula by a fixed number, 50 in this example (as that seems to be the outcome always.)

I'm not sure why the COUNTA does not work, my theory is that it has to do with that COUNTA cannot see there are array formulas in the selected range and therefor sees the cell content as "not blank" and counts it as filled cell

**AliGW** · 04-07-2018, 06:44 AM

Probably, which is why I suggested starting from scratch.

**JaSonic75** · 04-07-2018, 06:48 AM

Many thanks

I have tried inputting that exactly as shown, but still when on the Print tab when you select Tonne7.5 in the left pull down, the right pull down remains empty

Dropdowns.jpg

Many thanks

**JaSonic75** · 04-07-2018, 07:06 AM

Tried changing the code to:

Please Login or Register  to view this content.

still no luck

If I start again, will have to do it later due to my kids being 'dad, dad, dad' lol

Appreciating all your help here, happy to try all suggestions that come my way.

Many thanks

**AliGW** · 04-07-2018, 07:33 AM

It's Saturday - family time.

I've already suggested that starting again might be the best option - clean slate. We'll be here when you are ready to proceed with this.

**Glenn Kennedy** · 04-07-2018, 08:30 AM

Are you OK with a bit if VBA to sort this out?

**Glenn Kennedy** · 04-07-2018, 08:35 AM

One more Q. Why are you hugely complicating this by having the q and w in A and B53 of Setup??? What function do they serve in your real data?

**JohnTopley** · 04-07-2018, 08:47 AM

You cannot use INDIRECT to reference dynamic ranges Which use OFFSET without a "work around"

See here

http://www.contextures.com/xlDataVal02.html#Dynamic

See Section "Using Dynamic Lists"

See attached where I have created drop downs for "Tonne_7.5" , "Tonne_3.5" , "Drivers Mate" and "CPW"

Curious as to why you have duplicated data from "Setup" in "Dropdowns"

NOTE: removed "extraneous" letters (q,w etc) from "Setup" columns

**xlnitwit** · 04-09-2018, 07:24 AM

You can also use EVALUATE in place of INDIRECT though it requires an additional defined name and the workbook must be macro enabled (though it appears the latter was the case already). Updated version attached with dynamic names using COUNTIF as I suggested previously.

**FlameRetired** · 04-09-2018, 02:18 PM

In the attached find:

the list Department copied and transposed as a table header in row 1 of 'DropDowns'.
the same formulas used in post #31 attachment in 'DropDowns'.
for the Name DD validation formula in L2
Formula:
```
Please Login or Register  to view this content.
```

I retained the q, w, a ... in 'Setup'. I'm guessing they are there to confirm the formulas are removing blanks.

There are no INDIRECT references to D2. They are all MATCH references.

Formulas in 'Print' P1 and T1 have no function. Those were just convenient places to build the DD formula. I left them in case they aid in following this. They can be deleted.

Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Re: Blank cells in named ranges

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Delete blank rows at the bottom without affecting named ranges

[SOLVED] List all the named ranges & the cells they refer to on a blank she

Bookmarks

Bookmarks

Posting Permissions