data validation

[Guest]

I have created a data validation list that pulls values from cells E98:E112.
E98 has the following formula: =IF('2'!B8="","",'2'!B8) and this formula is
copied down through E112. Only cells E98:E105 have values in them. When the
dropdown for the data validation list is clicked, the scroll bar is at the
very bottom of the list displaying empty values and I always need to scroll
to the top of the list. Why is the top of the list not automatically
displayed in the dropdown rather than the bottom of the list?

 

[Debra Dalgleish]

Your current data validation list contains blanks, and when the Data
Validation dropdown is opened, it finds a match to the current content
of the cell. It selects the first blank in the dropdown list, because it
is a match for the blank in the cell. To prevent this, you could fill
the cell with the first item from the list, as a default value.

Or, you can use a named dynamic range as the source. There are
instructions here:

http://www.contextures.com/xlNames01.html

Then, in the Data Validation Source box, type an equal sign and the name
of the range, e.g.: =MyRange

 

[Guest]

Thanks Debra. I tried to create a dynamic range using the offset and the
counta, except the counta doesn't work since Excel does not interpret the
cells as being blank since they contain a formula. I'd love to hear any
other suggestions you have.

 

[Debra Dalgleish]

Instead of using COUNTA in the formula, you could use COUNTIF, e.g.:

=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,">0"),1)

 

[Dave Peterson]

I use this technique to limit the print area when I have formulas that evaluate
to "".

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$2000<>""),ROW(Sheet1!$A$1:$A$2000))

(Make that 2000 big enough to extend past the last possible row--but don't use
the whole column.)

Then once more:
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,5)

That last 5 represents the last column to print (A:E).

And change the worksheet (sheet1) if necessary (in all the places).

===
It works nicely when there are "" intermingled in the column. (Maybe not too
nice a fit for you???)

 

[Guest]

Thanks Dave and Debra for your great suggestions. You guys are the best!!
What we ended up doing was rather than using the cells with the formulas
linking to the other sheet, we created a dynamic range on sheet where the
actual values were entered. This eliminated the issue of needing to use "".

 

[Guest]

Dave, How would you do this with a fixed number of rows but dynamic COLUMNS?

 

[Peo Sjoblom]

Go across and the use COLUMN instead of ROW

=LOOKUP(2,1/(Sheet1!$1:$1<>""),COLUMN(Sheet1!$1:$1))

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon