How to reference a data validation answer in a cell in another sheet

[mr_samd]

Hi,

I am recieving a #VALUE ! error when trying to copy the result of a data validation drop down in another sheet and dont know why.

e.g.
In sheet 1 I have a a drop down box in a cell (A1) that has recent dates in.
On sheet 2 I want the chosen date to show in a cell. I have put the formula "=Sheet1!A1" and I recieve the #VALUE ! error.
This does not happen when referencing it in sheet 1.

Any help on this issue would be much appreciated.

Sam

 

[Gerald Higgins]

Hmm . . . I can't replicate this.

I thought it could be due to an error in the sheet name, for example "SHEET1" and "SHEET 1", but that would generate a #REF error, not a #VALUE error.

What exactly is in A1 ?
And can you give us an example of how you reference it in sheet 1 when it works ?

 

[Marcílio_Lobão]

mr_samd, Good morning.

It is a very simple operation to display error.

Have you tried the same process using other cells?

At first there's nothing wrong with the procedure you've gone through.

Recheck the cells involved in this operation to see if there is nothing configured differently in them.

If you are all right with the cells the only way out is to save your spreadsheet to a free website, www.sendspace.com and send the download link here.

Only by having access to the worksheet can we give you a conclusive answer.

I hope it helps.

 

[mr_samd]

While re checking and trying all of these suggestions i realised it was due to the drop down box being in some merged cells. I have fixed the problem by referencing the first of the merged cells. Thanks for your help!

 

[Marcílio_Lobão]

mr_samd,

Thanks for the feedback.

Merge cells are always a huge headache in operations at EXCEL.

Have a nice day!

 

[mr_samd]

Merge cells are always a huge headache in operations at EXCEL.

Yeah i am very quickly realising this!

Thanks again,

Sam