Creating Dynamic Range - Missing Last entry

[Guest]

I have created a dynamic range for an excel 2000 spreadsheet that I reference by using a Data Validation List...

The dynamic range almost works, except that is leaves whichever is my last entry to the list off, of my "drop-down" box... So anything I add to the list whether is is several or a couple, will show up in my drop-down box except the very last entry....

My list that I am creating the dynamic range from runs from cell

A3 thru A20

The formula for the dynamic range I have is:
=OFFSET(Sheet2!$A$3,0,0,COUNTA(Sheet2!$A:$A))

Can anyone recognize something that might be wrong with my formula, or do you know of a website that could possibly help??

 

[Bob Phillips]

There is nothing wrong with that formula that I can see. You don't by any
chance have a blank row anywhere in that range by any chance?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have created a dynamic range for an excel 2000 spreadsheet that I

reference by using a Data Validation List...

The dynamic range almost works, except that is leaves whichever is my last

entry to the list off, of my "drop-down" box... So anything I add to the
list whether is is several or a couple, will show up in my drop-down box
except the very last entry....

My list that I am creating the dynamic range from runs from cell

A3 thru A20

The formula for the dynamic range I have is:
=OFFSET(Sheet2!$A$3,0,0,COUNTA(Sheet2!$A:$A))

Can anyone recognize something that might be wrong with my formula, or do

you know of a website that could possibly help??

 

[Guest]

PC
Are any of the cells within your range blank. If you have the first cell blank, or skip a cell in your list then the COUNTA function will be off. If this is the case, and you want it that way, then just add 1 to the COUNTIF

Good Luck
Mark Graese
(e-mail address removed)

----- PC wrote: ----

I have created a dynamic range for an excel 2000 spreadsheet that I reference by using a Data Validation List...

The dynamic range almost works, except that is leaves whichever is my last entry to the list off, of my "drop-down" box... So anything I add to the list whether is is several or a couple, will show up in my drop-down box except the very last entry...

My list that I am creating the dynamic range from runs from cell

A3 thru A2

The formula for the dynamic range I have is:
=OFFSET(Sheet2!$A$3,0,0,COUNTA(Sheet2!$A:$A)

Can anyone recognize something that might be wrong with my formula, or do you know of a website that could possibly help??

 

[Guest]

Yes Bob, I did....I took that out and it ten worked fine..... However, one thing is I actually would like the first entry to be blank (as a default)....Is that possible to do, or can I trick excel to believe the first cell has something in it whenit doesn't....Say like if I select a blank cell atop my list and just insert several spacebar hits, so that something will be in the cell, but to the eye nothing shows....
Thanks for your help.

 

[Guest]

Yeah I had a blank cell in there, taking that out corrected my problem....However, now my problem is.... Can I create a dynamic range for a few lists....

I have 3 lists on a seperate sheet (all lists are on the same sheet tho ) that I want to create a dynamic range for .... The dynamic range I created for my 1st list works, but the other two give me an error message: "The Source Currently evaluates to an error. Do you wish to continue?

The formula I used for the other 2 lists is the same as the one that works for my 1st list, the only thing different is that the other two lists reference their appropriate Columns cells.. But I get this message..

Do I need the lists on seperate sheets, or do I need to change my formula around to get the other two lists to work as well

The formula I used for the "working" dynamic range is: =OFFSET(Sheet2!$A$4,0,0,COUNTA(Sheet2!$A:$A)
-for the othe r2 lists I just changed to column reference from "A" to "C" and "E" which hold my other lists.

Any help is greatly appreciated.

 

[Bob Phillips]

You could and do 1 of 2 things,

add an extra 1 to the count

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

start at a4

=OFFSET($A$3,0,0,COUNTA($A:$A))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Yes Bob, I did....I took that out and it ten worked fine..... However, one

thing is I actually would like the first entry to be blank (as a
default)....Is that possible to do, or can I trick excel to believe the
first cell has something in it whenit doesn't....Say like if I select a
blank cell atop my list and just insert several spacebar hits, so that
something will be in the cell, but to the eye nothing shows....

 

[Guest]

PC
The other ranges should work fine. They do not need to be on a seperate sheet from the first range

Are you entering the formula directly into the Data Validation List Source. When using DV you cannot reference another sheet as the source. You need to define the range with a name and then reference the name as the source. Is this what you did for the first range

Post the formulas and let us take a look at them

Regards
Mark Graesse
(e-mail address removed)

----- PC wrote: ----

Yeah I had a blank cell in there, taking that out corrected my problem....However, now my problem is.... Can I create a dynamic range for a few lists....

I have 3 lists on a seperate sheet (all lists are on the same sheet tho ) that I want to create a dynamic range for .... The dynamic range I created for my 1st list works, but the other two give me an error message: "The Source Currently evaluates to an error. Do you wish to continue?

The formula I used for the other 2 lists is the same as the one that works for my 1st list, the only thing different is that the other two lists reference their appropriate Columns cells.. But I get this message..

Do I need the lists on seperate sheets, or do I need to change my formula around to get the other two lists to work as well

The formula I used for the "working" dynamic range is: =OFFSET(Sheet2!$A$4,0,0,COUNTA(Sheet2!$A:$A)
-for the othe r2 lists I just changed to column reference from "A" to "C" and "E" which hold my other lists.

Any help is greatly appreciated.

 

[Guest]

sorry rojj is also me PC who posted the original message

 

[Guest]

PC (aka rojj),
Change COUNTC and COUNTE back to COUNTA. The "A" doesn't refer to the column, it is part of the function name.

Regards,
Mark Graesser
(e-mail address removed)

----- rojj wrote: -----

What I have done is:
Sheet 1 is my main document that will have the Data Validation Drop Down Boxes

Sheet 2 contains my 3 lists that I will reference for those Data Validation Boxes
-For each list I highlight the range of terms and go to Insert> Name> Define
----in the name define box I simply give each list a unique name (i.e for my 1st list, the only one which works now, I label it "auth")
----in the "refers to" field I enter my formula: List 1 -(the working one) is: =OFFSET(Sheet2!$A$5,0,0,COUNTA(Sheet2!$A:$A))
this is the only one which currently works

I did the same thing for the other 2 lists here are the formulas:
-List 2 : Named "req" // Formula is: =OFFSET(Sheet2!$C$5,0,0,COUNTC(Sheet2!$C:$C))
-List 3 : Named "terms" // Formula is: =OFFSET(Sheet2!$E$5,0,0,COUNTE(Sheet2!$E:$E))

Once done with this I go to my main sheet (#1) and create the data validation
- I Select the appropriate cell then select "Data > Validation
- In the DV box under "Criteria" I select List and in the "Source" field I enter =auth (or whichever list I want it to reference)

I have done this I think pretty consistently for all 3 DV I want to create, but like I said only one works (the "auth" list) the other 2 when entering the other list names in the "source" field kicks out that error message: "The Source currently evaluates to an error. Do you wish to continue?"

I can't figure out what is wrong with my set up, or formulas......

And sorry for the lenghty and wordy explanation, I am trying to make it clear as possible what I am doing, so hopefully someone can recognize what I am doing wrong..

Thanks for your input....

 

[Guest]

WOW....simple enough... I knew if would be some little stupid detail I would overlook... I was close though... But now everything works like a charm, exactly what I was looking to do..... Thank you very much for your input and suggestions, I greatly appreciate it....
Paul

 

[Aladin Akyurek]

Try:

=Sheet2!$A$3:INDEX(Sheet2!$A:$A,MATCH(REPT("z",255),Sheet2!$A:$A))

I have created a dynamic range for an excel 2000 spreadsheet that I

reference by using a Data Validation List...

The dynamic range almost works, except that is leaves whichever is my last

entry to the list off, of my "drop-down" box... So anything I add to the
list whether is is several or a couple, will show up in my drop-down box
except the very last entry....

My list that I am creating the dynamic range from runs from cell

A3 thru A20

The formula for the dynamic range I have is:
=OFFSET(Sheet2!$A$3,0,0,COUNTA(Sheet2!$A:$A))

Can anyone recognize something that might be wrong with my formula, or do

you know of a website that could possibly help??

 

[Guest]

Hi Paul,
Your welcome. Also, it is nice to see a name attached to the posts.

Regards,
Mark Graesser
(e-mail address removed)

----- PC wrote: -----

WOW....simple enough... I knew if would be some little stupid detail I would overlook... I was close though... But now everything works like a charm, exactly what I was looking to do..... Thank you very much for your input and suggestions, I greatly appreciate it.....
Paul