Validation List only partially 'sees' named range

[jerryliang2k]

I've got a named range which defined using a custom function. Lets call this named range [range1] which is defined by:

Please Login or Register  to view this content.

So the whole point of the custom function is becaues all the tutorials I've found on creating dynamic ranges didn't measure up to what I needed my dynamic list to do (unsorted, contains numbers and characters, contains spaces etc.)

Now when I go into the name editor, it outlines the range correctly but when I try to use the named range in a validation drop down list, only the first item is shown. Similarly if I try to use conditional formatting with the named range, only the first item is resolved to be within the range.

Any help would be greatly appreciated,

thanks

[NBVC]

Did you see these variations

dynamic named ranges:

namely this one: =OFFSET($A$1,0,0,COUNTA($A:$A),1)

that accomodates, text and numbers in the range.

[jerryliang2k]

yes but it doesn't account for blank cells. If there are blank cells in your range, that method does not work.

[NBVC]

How about:

=OFFSET($A$1,0,0,COUNTA($A:$A)+COUNTBLANK($A:$A),1)

[jerryliang2k]

Never tried that one, but just looking at it, if your adding all the cells in a range that arn't blank and all the cells in the range that are blank, you just get the whole range...

Anyways, more fundamentally, do you know if its not possible to use a custom function in a named range?

[NBVC]

Yeah... you are right that was stupid of me... didn't think before replying...

I don't think you can use UDF's in those features.. they are usually pretty sensitive to basic excel functions.... they don't even allow for you to use the Excel's own addin, Analysis Toolpak", they don't allow for direct references to cells not on the same page (you need to name the range or indirectly refer to them) and they don't allow for you to create arrays like {1,2,3,4,5} within formulas, you have to reference ranges...

That said, I doubt it allows custom UDF's if it doesn't allow those.

[NBVC]

How about this one...does it work for you?

=OFFSET(Sheet2!$A$1,0,0,MATCH(9.999999E+307,Sheet2!$A:$A),1)

This one offsets to the last cell that has anything in it.

[jerryliang2k]

that works for columns with only numbers. What would make it work for text?

[NBVC]

=OFFSET(Sheet2!$A$1,0,0,MATCH(REPT("Z",255),Sheet2!$A:$A),1)

[NBVC]

Deleted response...

not my day...

I will try to figure out best way to get both..text/numerics.

[NBVC]

Ok,\

I think this should do it to get to the last cell with either a number of text...

=OFFSET(Sheet1!$A$1,0,0,MATCH(2,1/(1-ISBLANK(Sheet1!$A$1:$A$65535))),1)

Note: that is an array formula and so you can't use A:A..

[Richard Schollar]

Not sure whether it really makes much impact on performnce but that isn't a particularly efficient formula Vittorio (each value gets evaluated). I gave this formula for both text/numeric in another thread which uses a slightly different approach:

=LOOKUP(9.99999999E+307,CHOOSE({1,2,3},MATCH(9.99999E+307,A:A),MATCH("zzzzzzz",A:A),MAX(MATCH({"zzzz zzz",9.999999E+307},A:A))))


But I doubt you can use this in a defined name as it contains array constants - so isn't much use here!


Richard

[NBVC]

In that case, Richard... mind you it's been a long day... so not thinking so straight...

why not just this part?... it seems to capture the last cell that has either number or text on its own...

=MAX(MATCH({"zzzzzzz",9.999999E+307},A:A))

and it seems to give the same results as the longer formula you provided...

which, as you stated would work either in Data Validation because of the array constants...

[jerryliang2k]

NBVC,

I realized that your previous solution (that you deleted) and your latest solution both TECHNICALLY work. It counts the whole column up till the last row with ANY kind of data... including formulas!

You see, my column is 2000 cells long (1-2001). Each cell contains a formula that uses INDIRECT to pull data from a correspoding cell on another sheet. So if the other sheet doesn't have data, the cell won't have a value, but it WILL have a formula and I think your formula takes that into account.

Is there something you can do with the ISBLANK function to make it look for valueless cells?

thanks

[Richard Schollar]

The problem comes if the column doesn't contain any numerics or any text - so you need to do it within the LOOKUP so the error that would be returned in this case gets ignored.

I shouldn 't take credit for this formula - it's from Krishnakumar.

Richard

[NBVC]

So I am not sure why the original =OFFSET($A$1,0,0,COUNTA($A:$A),1) didn't work? Did you try it?.. that counts formula blanks as well as text, numbers, etc.. except for actual null cells...

[NBVC]

The problem comes if the column doesn't contain any numerics or any text - so you need to do it within the LOOKUP so the error that would be returned in this case gets ignored.

I shouldn 't take credit for this formula - it's from Krishnakumar.

Richard

I deleted everything in the column and still got #N/A for both the Lookup() formula and the Match(Max()) only formula....

[jerryliang2k]

I guess is worked, but not in the way I wanted to. Like I said in my previous post, My range of rows for all columns is from 1 to 2001. All cells contain formulas. So while the COUNTA picks up all the cells with ANYTHING In them, I want it to NOT pick up the cells with formulas, but no values/text. In other words I only want to count the cells with ONLY values/text.

Sorry if that was confusing,

Jerry

[NBVC]

I am at a loss then...

Each of the formulas I gave do slightly different counts...

I counted all entries including formula blanks with Counta()
I counted all non-formula blanks with Countblank()
I counted all numbers with Match(9.9999,e+307,A:A)
I counted all text with Match(Rept("z",255),A:A)
I counted again all text/numbers/blanks with MATCH(2,1/(1-ISBLANK(Sheet1!$A$1:$A$65535)))

One of which must do what you want?


Maybe you should post a workbook showing exactly your needs.

[jerryliang2k]

I'll play around with formulas some more. Maybe I copied and pasted something wrong. If all else fails, I'll upload the workbook. The reason I'm triyng to not upload it is because the function of the workbook is tied closely to a custom menu bar which has a handfull of other files.

Thanks for your help so far NBVC, I'll definately be back tomorrow.

*edit*
Before I sign off for the day, maybe this example will help, Imagine the column below, keeping in mind that all rows including '-blank' rows have formulas:

(1) ITEM 1
(2) -blank
(3) -blank
(4) -blank
(5) -blank
(6) ITEM 2
(7) -blank
(8) -blank
(9) -blank
rest of the column is -blank

Assuming that they are all text, I need a count that will return 6 (the last row with any value in it).

COUNTA will return 9
COUNTBLANK will return 7
Match(Rept("z",255),A:A) should in theory work but for some reason it does nothing for me

[Richard Schollar]

I deleted everything in the column and still got #N/A for both the Lookup() formula and the Match(Max()) only formula....

Try it with only numerics in the column or only text in the column.

[DonkeyOte]

jerryliang2k, based on your last post it is my belief that NBVC has previously provided the path to a solution, namely:

=MATCH(2,1/(1-ISBLANK(Sheet1!$A$1:$A$65535)))

However ISBLANK will count formula Null as a Non-Blank cell so you would need to adapt... eg (array):

=MATCH(2,1/(1-($A$1:$A$65535="")))

On a similar theme you can use the below (non-array) though utilises ROW:

=LOOKUP(2,1/($A$1:$A$65535<>""),ROW($A$1:$A$65535))

However it won't be quick to calculate if you must reference the entire column ... if you can restrict the reference range do so... ie if you KNOW it will never exceed say row 10000 limit the range accordingly:

=LOOKUP(2,1/($A$1:$A$10000<>""),ROW($A$1:$A$10000))

Given this is being used in a named range the formula will I guess be treated as an array by default so it may be that the MATCH would be quicker in this instance... perhaps NBVC/Richard can shed some light on that for us.

[DonkeyOte]

Try it with only numerics in the column or only text in the column.

Richard I appreciate you got this from Kris but what is the significance of the space within the string ? Why not REPT("z",255) ?

Please Login or Register  to view this content.

I can see this approach is useful if being used in a native cell but I would probably still err on the side of having a few named ranges as outlined here recently (ignoring the issues on this thread of formula nulls) though my approach would need to be "enhanced" to make it more dynamic ... I've not tested shg's approach myself.

[Richard Schollar]

Richard I appreciate you got this from Kris but what is the significance of the space within the string ? Why not REPT("z",255) ?

That would be called a 'typo'!!!

[DonkeyOte]

Ok that makes sense...

I guess this method is a little flawed insofar as given inline array constant you'd need to physically type in "z" say 255 times to make it as watertight as possible... using "zzzzzzz" would not cope with strings exceeding 7 chars in length (255 not 100% watertight either obviously)... and doing so would make the formula horrendous aesthetically.

PS Enjoy Seattle... does this now mean you've told work you have been awarded Excel MVP status by Microsoft ?
(ie they now know officially that you don't do any actual work...)

[Richard Schollar]

Well, "zzzzzzz" would work with any strings where the first character was less than a "z", so not completely watertight, but how many values do you normally have in your worksheets that begin "zzzzzSomething else"?

Yep, work knows but they haven't quite cottoned on to exactly what 'helping out on forums' means - and I ain't tellin' 'em!

[DonkeyOte]

re: z ... true... perhaps guilty of being a little over zealous !

I guess if you were monitoring sleep patterns with each "z" representing a minutes sleep ....