Drop Down Data Validation List

[Guest]

Ready to jump out the window. I have a data validation
list which I'm using for 10 columns (C5, L5, U5, etc.)
within a worksheet. The validation list contains 8 #'s,
however I'm treating them like text; otherwise if 00 is in
front of a number, it wants to remove the two zeros (i.e.
00609 would read 609). This drop down list is working
fine (I can choose 1 number of the 8 each time); however,
on another worksheet within the same workbook I'm trying
to display what single # is in each column (C5, L5, U5,
etc.) Every formula I use gives me #VALUE or some other
error message, can you help?

 

[Otto Moehrbach]

Anon...
A little hard to follow but I'll give it a try. If you have numbers
with leading zeros that you want to treat as text (so that the leading zeros
don't go away), precede each "number" with an apostrophe in your list of
numbers. Then name the list something appropriate, like "TheList". Then
when you setup the Data Validation cells(s), select List and, in the
"Source:" block, enter "=TheList" without the quotes. The Data Validation
will then retain the leading zeros.
Now you say that you are putting formulas in the same workbook to
display the values selected in the Data Validation cells. What do those
formulas look like? Let Excel write the formula for you. Select the cell
in which you want to place the formula. Type "=" without the quotes. Now
go to the cell that has the drop down whose value you want to display and
click on that cell, even if it's on another sheet. Hit Enter. Done. The
formula is written for you. HTH Otto

 

[Dave Peterson]

Didn't
=sheet2!c5
work?

or even
=value(sheet2!c5)

if you wanted to treat them as numbers.

 

[Guest]

Otto
Thank you so very much, most of it work, however, the
second part of your answer doesn't. I'm trying to get the
result of all 8 cells (different number each time) into
one cell, the formula gives me the error message #VALUE!

 

[Guest]

Dave,

Thank you for your response. I do get the first cell's
number when I use the formula below, I'm looking to get
all results from the 8 cells in one cell on a differnt
worksheet within the workbook, putting a "," in between.
I can't get it to work, do you have any other suggestions?

 

[Otto Moehrbach]

Anon.......
All 8 "numbers" in one cell? Do you mean something like "001, 002, 003,
etc" in one cell? The formula for this would look something like this:
=A1 & ", " & A2 & ", " & A3................
You would need to substitute the sheet name and cell address for each cell
address in the above formula if the formula is on a different sheet. Note
that there is a space after the comas in the above formula. HTH Otto

 

[Guest]

Otto,

I tried your suggestion and I get the formula in the cell,
rather than the result.

 

[Guest]

Otto,

Just a quick note, I got rid of the formula in the cell
and the result is what I've been getting #VALUE, if you
have any other suggestions, I would appreciate it. Thank
you for your help.

 

[Otto Moehrbach]

Anon.......
Post the formula you are using and the values in the cells that are
referenced in the formula. This is too simple a task for you to be having
this much trouble with it. HTH Otto

 

[MrMike6by9]

I have a couple of sheets where I know I will have values of a certai
length and that some will have leading zeros. In those cases, I use
"special" format for the cell like "00000000" so that an entry of 123
will display as 00001234.

YMM

 

[Guest]

Otto,

Thanks for all of your help, the reason it wasn't working
is that I needed to put a ' before each number and the
fields were locked. It's working now, thanks to your
help. Until next time.

 

[Otto Moehrbach]

I'm glad it's working for you. Otto

Otto,

Thanks for all of your help, the reason it wasn't working
is that I needed to put a ' before each number and the
fields were locked. It's working now, thanks to your
help. Until next time.