Re: IF cell contains specific text enter this number questio
Set up a data table like this:
| IF statement with OR.xls |
|---|
|
|---|
| A | B | C | D | E | F | G | H |
|---|
| 1 | | | | | | | MyGenerationTourAdultOnly | 128 |
|---|
| 2 | | | | | | | DeluxeTour | 128 |
|---|
| 3 | | | | | | | JungleRiverSafari | 88 |
|---|
| 4 | | | | | | | PowerBoatJeepRental | 295 |
|---|
| 5 | | | | | | | | |
|---|
|
|---|
Name the cells G:H as “Tours” (no quotes) with a dynamic range:
=OFFSET(Sheet1!$G$1,0,0,COUNTA(Sheet1!$G:$G),2)
In your Validation drop down, which I assume you are using, use as the Source:
=INDEX(Tours,0,1)
In say C14:
=VLOOKUP(B14,Tours,2,FALSE)
The dynamic range feeds both the Validation drop down and the Vlookup function. If you add more tours in column G, with the corresponding price in column H, the drop down and the Vlookup will automatically pick up the addition (or deletions).
The main advantages of this routine:
Dynamic;
Avoids hard coding variables in the IF statement or a VLOOKUP statement, which is always prone to typos;
One data table feeds two functions.
If you prefer, use a macro to dynamically name the range:
Code:
Sub myRng()
With Sheet1
.Range("G1", .Range("G65536").End(xlUp)(1, 2)).Name = "Tours"
End With
End Sub
Regards,
Mike
