reference a cell on multiple worksheets that has a drop down list.

**TrendWendy** · 01-03-2013, 04:22 PM

I have a worksheet that is primarily to fill in information that is then distributed to multiple places on other worksheets.

One cell contains a drop down list.

If I try to reference that cell from another worksheet in the normal way it just wont let you choose that cell at all.

If I try to use the INDIRECT function - it allows you to reference the cell but returns a #Ref! error. I just want whatever text is chosen from the dropdown list to appear in the other cells on other worksheets. It seems like it should be very simple (and probably is) but I'm struggling with it.

**Palmetto** · 01-03-2013, 08:49 PM

Not sure that I completely understand your situation, but referencing cells on other sheets is as simple as:

=Sheet1!A1

Replace "Sheet1!" with whatever the actual sheet name is.

That may not be what you are after, so explain how you are attempting to reference the "drop down cell".

**TrendWendy** · 01-04-2013, 12:22 PM

Yes, that would be the regular way and is wonderful. I have 25 other cells on that work sheet that plug into two other worksheets in various locations. But it will not work with the one cell that has a drop down list.

On one worksheet I have all my information for an order. I put all my data in that worksheet and then that data is used in all the other worksheets. The one pesky cell uses a dropdown list for the user to choose which text he/she needs for the order they are working on. That one cell will not work in the normal way and I was told you could get around it with the INDIRECT formula but it just returns the reference error.

**ChemistB** · 01-04-2013, 12:32 PM

It sounds like what you have is a "List box" and not a data validation dropdown list. Is this correct? A list box is an imbedded object which is positioned over a cell but it's not a cell. Is it a "Forms" list box or an Controls (ActiveX) list box? If it's a form list box, if you right click on it and "format control" you'll see a text box with "cell link" (among other things).

**TrendWendy** · 01-04-2013, 04:12 PM

Thank you! I didn't need the cell link box but you got me looking in the right place to fix it!

I made the cell in question by using the Data tab - Data Validation - Settings - List - In Cell drop down and then typing in the source box the information seperated by commas. So that is why I thought it was a Data Validation list.

I right clicked on the data list cell went to format cells and under the protection tab it had the box for locked checked. I undid the lock and it works perfectly now.