data validation-pulls values f

[maryj]

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?
--
maryj

[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


maryj wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[maryj]

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.
--
maryj


"Debra Dalgleish" wrote:

> 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
>
>
> maryj wrote:
> > 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
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[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)

maryj wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[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???)

maryj wrote:
>
> 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.
> --
> maryj
>
> "Debra Dalgleish" wrote:
>
> > 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
> >
> >
> > maryj wrote:
> > > 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
> > Excel FAQ, Tips & Book List
> > http://www.contextures.com/tiptech.html
> >
> >

--

Dave Peterson

[maryj]

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 "".
--
maryj


"Debra Dalgleish" wrote:

> 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)
>
> maryj wrote:
> > 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
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[GeorgeW]

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

"Dave Peterson" wrote:

> 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???)
>
> maryj wrote:
> >
> > 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.
> > --
> > maryj
> >
> > "Debra Dalgleish" wrote:
> >
> > > 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
> > >
> > >
> > > maryj wrote:
> > > > 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
> > > Excel FAQ, Tips & Book List
> > > http://www.contextures.com/tiptech.html
> > >
> > >
>
> --
>
> Dave Peterson
>

[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




"GeorgeW" <GeorgeW@discussions.microsoft.com> wrote in message
news:F7DF4F1B-721A-41AF-A3E4-C113ADA23FAE@microsoft.com...
> Dave, How would you do this with a fixed number of rows but dynamic
> COLUMNS?
>
> "Dave Peterson" wrote:
>
>> 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???)
>>
>> maryj wrote:
>> >
>> > 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.
>> > --
>> > maryj
>> >
>> > "Debra Dalgleish" wrote:
>> >
>> > > 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
>> > >
>> > >
>> > > maryj wrote:
>> > > > 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
>> > > Excel FAQ, Tips & Book List
>> > > http://www.contextures.com/tiptech.html
>> > >
>> > >
>>
>> --
>>
>> Dave Peterson
>>