simple drop down error

[sassriverrat]

Good Morning,

I seem to be having what appears to be a simple data validation error that I'm confused with.
I have a cell that has a drop down list (list is on another sheet). User can scroll through the drop down and choose any of the listings from the other sheet perfect.

So here's the trouble. On sheet 2, A1 is set equal to the data validation cell on Sheet 1, A1. however, sheet 2 A1 just shows a "0" instead of whatever is in A1 from Sheet 1. If I go on sheet 1 and set A2 = to A1 Then it populates correctly on just Sheet 1. What is wrong with sheet 2. cell formatting is in "general"


Thanks.

 

[Perpa]

sassriverrat,
Good morning.

Drop Down List where the list is on another sheet:


On sheet 3 Create the list of names including the header
On sheet 3, If the list will change or be added to, then select the header and the list of names and create a Table (Insert>Table)
On sheet 3, Create a Named Range I called it 'MyNames' selecting just the names and not the header
On Sheet 1 Create the dropdown using Data Validation>List>Source: '=MyNames'


Then on Sheet 2 in A1 insert this formula: '=Sheet1!A1'


Credit for above method to Contextures:
http://www.contextures.com/xldataval02.html


Hope this helps
Perpa

 

[sassriverrat]

sorry I wasn't very clear I think. My issue doesn't lie with the data validation side...that part is fairly simple. The issue I get is the last part of your instructions. Basically doing the =Sheet1!A1, I get a "0" value regardless of whatever is actually in the list....

So if the list (Sheet1!A1) is equal to "Hello" in the drop down, the Sheet2!A1 just is equal to "0", not "Hello" as it should be.

 

[Fluff]

Do you have calculation set to manual?

 

[sassriverrat]

Do you have calculation set to manual?

Nope. Automatic....

 

[Perpa]

sassriverrat,
It worked for me. Not sure why it is not working for you.
Perpa

 

[Fluff]

As calculation is on auto, then I can think of no reason why it isn't working