Hi,



I have a drop down list on one sheet which looks at a list of peoples names on another worksheet, when I add a line in this sheet to add another name the list then searches on the worksheet it is on.

For example

Worksheet 1 Worksheet 2

Dropdown list List of names (A1:A50)

Once I add a name to the list it searches A1:A50 on worksheet 1

Any ideas what is happening on this ?

Thanks

That's never happened to me, but try one of these:

* Specify the sheet in the range e.g. =[Worksheet 2]!A1:A50
* Use a named range

Hi,

Have tried but no luck. I have 20 dropdown lists all connected to this other workbook and when I insert a new line the dropdown list I am doing it for is OK but all of the other drop down lists start to pick up the location of the worksheet it is on.

Any other suggestion I can try?

Try attaching your sheet and I'll take a gander.

Blank Budget Plan 2016 Final V7.xlsx

Hi,

Here you go, the issue is on the forecasting sheet

Where is the data validation? I don't see any. That may be due to the sheets being password protected.

FYI, you can easily attach a sheet using the Go Advanced button below. It might be easier than whatever you have used.

Hi,

The password is Wobwitwu2, really appreciate your help

OK...I see the validation now. A few questions/points of clarification regarding your original post...

Forecast IV, Column A. Correct?

(Note: I don't use spaces in tab names to avoid those unsightly quote marks in formulas. I would call it RateCard and thus that range would be RateCard!$D$2:$D$74. It's an OCD thing.)

So let me get this straight. When you "add a line" to RATE CARD, the data validation drop down no longer uses the range on RATE CARD, rather it then uses a range on Forecast IV. Is that right?

What do you mean by "add a line"? Are you just adding a name to the end of the list? If you do that, the data validation range should not change at all though it would not include the added name since it is outside the range specified.

However, if you insert a line within the range, the data validation range will update.

I have done both of these with your sheet and that is what happened. I notice that the RATE CARD data has a filter applied. Perhaps that may be part of the issue though it's hard to say since I can't replicate it.

Personally, I would use the Format as Table functionality for the RATE CARD data and name the table something logical (e.g. RateData) and specify the name of the field headers, again done logically and again avoiding spaces. I have done these things on the attached version. I called the field headers Name, Position, Rate, and Card just to call them something

See if it helps. To add a name to the RateData table (which is, of course, on the RateCard tab), either right click on a cell to insert a line above it or pull down the little doohickey on the bottom right corner of the table.

Now the data validation range is a whole lot more aesthetic and logical: You can tell just by looking at it what it's referring to. The INDIRECT function is necessary when doing it this way. I have only done this for the first line in your table. No sense going further if this is a dead end.

Also, look at the formula next to it in Column B on Forecast IV:
and compare it to the old formula in the record two lines below:
Much prettier, eh? And we can make it even prettier if we apply the same Format as Table Functionality to Forecast IV. It might look something like:
Anyhoo, check out the attached and see if it helps with your issue. Again, I haven't been able to replicate it as I understand it.

Attached Files

Blank Budget Plan 2016 Final V7.xlsx (139.7 KB, 8 views)

You are so right, much more clearer formula. Ive opened the file and if you now look on the drop down box in cell A13, its gone from looking at the rate card to now looking at the forecast tab.

From now on I'm naming all code areas so thanks for the lesson

I never changed that validation to the new one so whatever it is, and it is a mystery to me, didn't seem to change the one I did.

So I copied the validation to each alternate cell going down column A. See if it still changes.

Attached Files

Blank Budget Plan 2016 Final V7.xlsx (139.6 KB, 8 views)