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
Test Cust 1

<tbody>
[TD="align: center"]6[/TD]
[TD="bgcolor: #F2F2F2"]Customer Name[/TD]
[TD="bgcolor: #F2F2F2"]Customer Region[/TD]
[TD="bgcolor: #F2F2F2"]Customer Type[/TD]
[TD="bgcolor: #F2F2F2"]Customer Priority[/TD]
[TD="bgcolor: #00FF00"]Marketing Requirements[/TD]
[TD="bgcolor: #F2F2F2"]Timing before release, Key Points[/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: #F2F2F2"]ASR[/TD]
[TD="bgcolor: #F2F2F2"]Quick[/TD]
[TD="bgcolor: #F2F2F2"]1 Low[/TD]
[TD="bgcolor: #F2F2F2"]Product Introduction[/TD]
[TD="bgcolor: #F2F2F2"]3 weeks[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2"]Product Detailed discusion[/TD]
[TD="bgcolor: #F2F2F2"]3 weeks[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2"]Pre Final PPT[/TD]
[TD="bgcolor: #F2F2F2"]4 weeks[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #F2F2F2"]test 2[/TD]
[TD="bgcolor: #F2F2F2"]ASR[/TD]
[TD="bgcolor: #F2F2F2"]Quick[/TD]
[TD="bgcolor: #F2F2F2"]3 High[/TD]
[TD="bgcolor: #F2F2F2"]Product Detailed discusion[/TD]
[TD="bgcolor: #F2F2F2"]3 weeks[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2"]Product Detailed discusion[/TD]
[TD="bgcolor: #F2F2F2"]1 month[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]

</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