Data Validation Formula and Conditional Formatting

[StuartWhi]

Hi All,

Using 2010 on Vista.

I have been playing around with this for far too long so thought I would get your advice.

Column “D” contains a Data Validation list which has two options “Quick” or “Key”,
- No issues with this.
- Row 7 is the first possible entry point (due to headings etc).
Column “G” contains a Data Validation list which uses =indirect(D7) to allow this to be populated by the Cell Reference Names of “Quick” and “Key”, thus G is populated by D’s reference.
- This all works very well.

So my issues,
Column “D” isn’t always populated with data so Column “G” should reference the next above entry.
- After reading many posts and combining a few ideas I have come up with the following,
Note: Column “B” will have the same number of empty cells to the “Quick” or “Key” reference.

=INDIRECT(ADDRESS(LOOKUP(9.99E+307,SEARCH("?*",$B$7:B7),ROW($B$7:B7)),4))

- This formula appears to work without any issues in a standard cell but when place in as a Data Validation it doesn't work???
Any ideas would be very welcome.

The second question is how to copy the corrected formula within Data Validation down the column (minimum 200 rows).
- Please note I have a lot of conditional formatting within this document so not having to redo this would be appreciated (if possible).

Thanks as always and look forward to your thoughts.
- Doesn't have to use this formula or could even uses VBA (I have minimum experience in VBA) so would love to hear any ideas.

Excel 2010

	B	C	D	E	F	G
6	Customer Name	Customer Region	Customer Type	Customer Priority	Marketing Requirements	Timing before release, Key Points
7	Test Cust 1	ASR	Quick	1 Low	Product Introduction	3 weeks
8					Product Detailed discusion	3 weeks
9					Pre Final PPT	4 weeks
10	test 2	ASR	Quick	3 High	Product Detailed discusion	3 weeks
11					Product Detailed discusion	1 month
12

<tbody>
</tbody>

Sheet1​

Stuart.

 

[StuartWhi]

Hi All,

If you happen to come across this post and wonder what the answer is thanks to PGC01 it was resolved in this post basically I needed a double Indirect in the formula.

Thanks PGC01