OK...I see the validation now. A few questions/points of clarification regarding your original post...
Quote:
Originally Posted by Webbn111
I have a drop down list on one sheet
|
Forecast IV, Column A. Correct?
Quote:
|
which looks at a list of peoples names on another worksheet
|
Code:
='RATE CARD'!$D$2:$D$74
(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.)
Quote:
|
when I add a line in this sheet to add another name the list then searches on the worksheet it is on.
|
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:
Code:
=INDIRECT("RateData[Name]")
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:
Code:
=VLOOKUP(A11,RateData,4,FALSE)
and compare it to the old formula in the record two lines below:
Code:
=VLOOKUP(A13,RateCard!$D$2:$G$74,4,FALSE)
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:
Code:
=VLOOKUP([@Name],RateData,4,FALSE)
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.