Data Validation cell not working other sheet

Brass Contributor

Working in Sheet 1 and in cell  B2 I am using IF(B2=10,Sheet 2 cell C2,"") 

In sheet 2  cell C2 is having data validation list in it but it is not showing data validation list in Sheet 1 after using formula. 

 

Can anyone help please?

3 Replies

@ExcelGeek90 

 

Hi,

 

Do not know if I understood correctly but; the fact of referencing a cell that contains a data validation list does not mean that the cell in Sheet1 with the formula will also contain the list.

 

That is, the data validation list only works for those cells that you defined the data validation, not any cell making reference to these.

 

Having said this, in addition, your formulae seem to not be accurate. Plus, you seem to be trying to reference cell B2 and writing the formula in B2, which seems circular. Try this instead:

 

=IF(B2=10,Sheet2!C2,"")

 

This will return the value on C2 of Sheet2 whenever B2 of Sheet1 is exactly equal to 10. Place this formula in a cell you desire. Again, take into account that only the value entered in cell C2 of Sheet2 will be returned, but not the entire validation list of it.

 

Hope this has clarified your question. If you wanted another functionality please let us know.

 

 

Thank yor your response. Can you please tell me way of seeing data validation list in cell B2 in sheet 1?

@ExcelGeek90 

If you want a cell on Sheet1 to have the same value as cell C2 on Sheet2, you don't need a drop-down list in that cell - the value of the cell is determined by the formula. The moment you'd select an item from the drop-down list, you'd overwrite the formula.