Is this legal?
 

I have 2 columns and I am trying to create a validation in which my 2nd
column relies on the input of the 1st. That's fine. But, I need to do a
lookup in two different tables on the same sheet. Is what I'm trying to do
allowed in Excel. I attempted a formula in my source but I get errors. It's
not a parenthesis error or anything like that. Can someone please help?
Thanks.

=IF(INDIRECT(VLOOKUP(B2,G1:I18,3,0)),(VLOOKUP(B2,J 1:K15,2,0)))

Is this legal?
 

=IF(NOT(ISNA(VLOOKUP(B2,G1:I18,3,0))),VLOOKUP(B2,G 1:I18,3,0),VLOOKUP(B2,J1:K15,2,0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karen" wrote in message
...
I have 2 columns and I am trying to create a validation in which my 2nd
column relies on the input of the 1st. That's fine. But, I need to do a
lookup in two different tables on the same sheet. Is what I'm trying to do
allowed in Excel. I attempted a formula in my source but I get errors.
It's
not a parenthesis error or anything like that. Can someone please help?
Thanks.

=IF(INDIRECT(VLOOKUP(B2,G1:I18,3,0)),(VLOOKUP(B2,J 1:K15,2,0)))

Is this legal?
 

When I copied this into my source, I get a message that states "the source
currently evaluates to an error. do you wish to continue?". When I click yes,
my data is not provided in the drop down. I'm not familiar with the ISNA. Why
is there a duplicate vlookup for the same parameters? thanks!

"Bob Phillips" wrote:

=IF(NOT(ISNA(VLOOKUP(B2,G1:I18,3,0))),VLOOKUP(B2,G 1:I18,3,0),VLOOKUP(B2,J1:K15,2,0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karen" wrote in message
...
I have 2 columns and I am trying to create a validation in which my 2nd
column relies on the input of the 1st. That's fine. But, I need to do a
lookup in two different tables on the same sheet. Is what I'm trying to do
allowed in Excel. I attempted a formula in my source but I get errors.
It's
not a parenthesis error or anything like that. Can someone please help?
Thanks.

=IF(INDIRECT(VLOOKUP(B2,G1:I18,3,0)),(VLOOKUP(B2,J 1:K15,2,0)))

Is this legal?
 

If VLOOKUP can't resolve a value, Excel returns the #N/A error (value not
available). Therefore, Bob's formula has the following logic:

IF #NA is NOT returned from the VLOOKUP VLOOKUP(B2,G1:I18,3,0) THEN do
VLOOKUP(B2,G1:I18,3,0) ELSE do VLOOKUP(B2,J1:K15,2,0)

But it sounds like you're trying to do data validation with this formula. I
don't think If-Then constructions can be used in data validation. How about
=OR(VLOOKUP(B2,G1:I18,3,0),(B2,J1:K15,2,0)) ?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Karen" wrote:

When I copied this into my source, I get a message that states "the source
currently evaluates to an error. do you wish to continue?". When I click yes,
my data is not provided in the drop down. I'm not familiar with the ISNA. Why
is there a duplicate vlookup for the same parameters? thanks!

"Bob Phillips" wrote:

=IF(NOT(ISNA(VLOOKUP(B2,G1:I18,3,0))),VLOOKUP(B2,G 1:I18,3,0),VLOOKUP(B2,J1:K15,2,0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karen" wrote in message
...
I have 2 columns and I am trying to create a validation in which my 2nd
column relies on the input of the 1st. That's fine. But, I need to do a
lookup in two different tables on the same sheet. Is what I'm trying to do
allowed in Excel. I attempted a formula in my source but I get errors.
It's
not a parenthesis error or anything like that. Can someone please help?
Thanks.

=IF(INDIRECT(VLOOKUP(B2,G1:I18,3,0)),(VLOOKUP(B2,J 1:K15,2,0)))

Is this legal?
 

Should be: =OR(VLOOKUP(B2,G1:I18,3,0),VLOOKUP(B2,J1:K15,2,0))

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Dave F" wrote:

If VLOOKUP can't resolve a value, Excel returns the #N/A error (value not
available). Therefore, Bob's formula has the following logic:

IF #NA is NOT returned from the VLOOKUP VLOOKUP(B2,G1:I18,3,0) THEN do
VLOOKUP(B2,G1:I18,3,0) ELSE do VLOOKUP(B2,J1:K15,2,0)

But it sounds like you're trying to do data validation with this formula. I
don't think If-Then constructions can be used in data validation. How about
=OR(VLOOKUP(B2,G1:I18,3,0),(B2,J1:K15,2,0)) ?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Karen" wrote:

When I copied this into my source, I get a message that states "the source
currently evaluates to an error. do you wish to continue?". When I click yes,
my data is not provided in the drop down. I'm not familiar with the ISNA. Why
is there a duplicate vlookup for the same parameters? thanks!

"Bob Phillips" wrote:

=IF(NOT(ISNA(VLOOKUP(B2,G1:I18,3,0))),VLOOKUP(B2,G 1:I18,3,0),VLOOKUP(B2,J1:K15,2,0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karen" wrote in message
...
I have 2 columns and I am trying to create a validation in which my 2nd
column relies on the input of the 1st. That's fine. But, I need to do a
lookup in two different tables on the same sheet. Is what I'm trying to do
allowed in Excel. I attempted a formula in my source but I get errors.
It's
not a parenthesis error or anything like that. Can someone please help?
Thanks.

=IF(INDIRECT(VLOOKUP(B2,G1:I18,3,0)),(VLOOKUP(B2,J 1:K15,2,0)))

Is this legal?
 

i think that would probably work too. i've playing around with it while
waiting and i actually got something to work. i used this:

=INDIRECT(VLOOKUP(B2,$J$1:$K$33,2,0)) in which i merged two areas and only
have 2 columns. this works fine. However, one of my values in the 1st column
of my list is N/A. If this is selected, the lookup is supposed to return a
value that is in a named range. However, the named range is based on another
named range. For example:

1st column 2nd column

N/A should return a pay grade

This pay grade is based on a named range called NOTBANDGRADE. This range is
made up of several named ranges which lists different pay grades. One of them
being FIFTEEN, which lists a range of numbers from 01 to 15. I don't know if
you can create a range within a range and use it in a data validation. I'm at
wits end and have been working on this all day. If there is another
suggestion, I'm all ears. Thanks. I hope this all makes sense

"Dave F" wrote:

If VLOOKUP can't resolve a value, Excel returns the #N/A error (value not
available). Therefore, Bob's formula has the following logic:

IF #NA is NOT returned from the VLOOKUP VLOOKUP(B2,G1:I18,3,0) THEN do
VLOOKUP(B2,G1:I18,3,0) ELSE do VLOOKUP(B2,J1:K15,2,0)

But it sounds like you're trying to do data validation with this formula. I
don't think If-Then constructions can be used in data validation. How about
=OR(VLOOKUP(B2,G1:I18,3,0),(B2,J1:K15,2,0)) ?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Karen" wrote:

When I copied this into my source, I get a message that states "the source
currently evaluates to an error. do you wish to continue?". When I click yes,
my data is not provided in the drop down. I'm not familiar with the ISNA. Why
is there a duplicate vlookup for the same parameters? thanks!

"Bob Phillips" wrote:

=IF(NOT(ISNA(VLOOKUP(B2,G1:I18,3,0))),VLOOKUP(B2,G 1:I18,3,0),VLOOKUP(B2,J1:K15,2,0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karen" wrote in message
...
I have 2 columns and I am trying to create a validation in which my 2nd
column relies on the input of the 1st. That's fine. But, I need to do a
lookup in two different tables on the same sheet. Is what I'm trying to do
allowed in Excel. I attempted a formula in my source but I get errors.
It's
not a parenthesis error or anything like that. Can someone please help?
Thanks.

=IF(INDIRECT(VLOOKUP(B2,G1:I18,3,0)),(VLOOKUP(B2,J 1:K15,2,0)))

Is this legal?
 

It's a little hard to tell what an alternative solution would be because I'm
not clear on what you're trying to accomplish. One possibility would be to
have dependent drop downs, in which a user selects a pay range from a
drop-down, and then a second drop-down shows values dependent on the range
chosen. I have no idea if this kind of functionality addresses your needs,
however, you can read about such functionality he
http://www.contextures.com/xlDataVal02.html

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Karen" wrote:

i think that would probably work too. i've playing around with it while
waiting and i actually got something to work. i used this:

=INDIRECT(VLOOKUP(B2,$J$1:$K$33,2,0)) in which i merged two areas and only
have 2 columns. this works fine. However, one of my values in the 1st column
of my list is N/A. If this is selected, the lookup is supposed to return a
value that is in a named range. However, the named range is based on another
named range. For example:

1st column 2nd column

N/A should return a pay grade

This pay grade is based on a named range called NOTBANDGRADE. This range is
made up of several named ranges which lists different pay grades. One of them
being FIFTEEN, which lists a range of numbers from 01 to 15. I don't know if
you can create a range within a range and use it in a data validation. I'm at
wits end and have been working on this all day. If there is another
suggestion, I'm all ears. Thanks. I hope this all makes sense

"Dave F" wrote:

If VLOOKUP can't resolve a value, Excel returns the #N/A error (value not
available). Therefore, Bob's formula has the following logic:

IF #NA is NOT returned from the VLOOKUP VLOOKUP(B2,G1:I18,3,0) THEN do
VLOOKUP(B2,G1:I18,3,0) ELSE do VLOOKUP(B2,J1:K15,2,0)

But it sounds like you're trying to do data validation with this formula. I
don't think If-Then constructions can be used in data validation. How about
=OR(VLOOKUP(B2,G1:I18,3,0),(B2,J1:K15,2,0)) ?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Karen" wrote:

When I copied this into my source, I get a message that states "the source
currently evaluates to an error. do you wish to continue?". When I click yes,
my data is not provided in the drop down. I'm not familiar with the ISNA. Why
is there a duplicate vlookup for the same parameters? thanks!

"Bob Phillips" wrote:

=IF(NOT(ISNA(VLOOKUP(B2,G1:I18,3,0))),VLOOKUP(B2,G 1:I18,3,0),VLOOKUP(B2,J1:K15,2,0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karen" wrote in message
...
I have 2 columns and I am trying to create a validation in which my 2nd
column relies on the input of the 1st. That's fine. But, I need to do a
lookup in two different tables on the same sheet. Is what I'm trying to do
allowed in Excel. I attempted a formula in my source but I get errors.
It's
not a parenthesis error or anything like that. Can someone please help?
Thanks.

=IF(INDIRECT(VLOOKUP(B2,G1:I18,3,0)),(VLOOKUP(B2,J 1:K15,2,0)))

Is this legal?
 

That is exactly what I am trying to get to work. My drop downs are dependent.
When the user selects the choice of N/A in the first column, the second
column should show pay grades. But the problem lies in the data validation
source in the second column. I'm trying to use named ranges, which are made
up of other named ranges. I don't know if this can be done.....

Karen

"Dave F" wrote:

It's a little hard to tell what an alternative solution would be because I'm
not clear on what you're trying to accomplish. One possibility would be to
have dependent drop downs, in which a user selects a pay range from a
drop-down, and then a second drop-down shows values dependent on the range
chosen. I have no idea if this kind of functionality addresses your needs,
however, you can read about such functionality he
http://www.contextures.com/xlDataVal02.html

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Karen" wrote:

i think that would probably work too. i've playing around with it while
waiting and i actually got something to work. i used this:

=INDIRECT(VLOOKUP(B2,$J$1:$K$33,2,0)) in which i merged two areas and only
have 2 columns. this works fine. However, one of my values in the 1st column
of my list is N/A. If this is selected, the lookup is supposed to return a
value that is in a named range. However, the named range is based on another
named range. For example:

1st column 2nd column

N/A should return a pay grade

This pay grade is based on a named range called NOTBANDGRADE. This range is
made up of several named ranges which lists different pay grades. One of them
being FIFTEEN, which lists a range of numbers from 01 to 15. I don't know if
you can create a range within a range and use it in a data validation. I'm at
wits end and have been working on this all day. If there is another
suggestion, I'm all ears. Thanks. I hope this all makes sense

"Dave F" wrote:

If VLOOKUP can't resolve a value, Excel returns the #N/A error (value not
available). Therefore, Bob's formula has the following logic:

IF #NA is NOT returned from the VLOOKUP VLOOKUP(B2,G1:I18,3,0) THEN do
VLOOKUP(B2,G1:I18,3,0) ELSE do VLOOKUP(B2,J1:K15,2,0)

But it sounds like you're trying to do data validation with this formula. I
don't think If-Then constructions can be used in data validation. How about
=OR(VLOOKUP(B2,G1:I18,3,0),(B2,J1:K15,2,0)) ?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Karen" wrote:

When I copied this into my source, I get a message that states "the source
currently evaluates to an error. do you wish to continue?". When I click yes,
my data is not provided in the drop down. I'm not familiar with the ISNA. Why
is there a duplicate vlookup for the same parameters? thanks!

"Bob Phillips" wrote:

=IF(NOT(ISNA(VLOOKUP(B2,G1:I18,3,0))),VLOOKUP(B2,G 1:I18,3,0),VLOOKUP(B2,J1:K15,2,0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karen" wrote in message
...
I have 2 columns and I am trying to create a validation in which my 2nd
column relies on the input of the 1st. That's fine. But, I need to do a
lookup in two different tables on the same sheet. Is what I'm trying to do
allowed in Excel. I attempted a formula in my source but I get errors.
It's
not a parenthesis error or anything like that. Can someone please help?
Thanks.

=IF(INDIRECT(VLOOKUP(B2,G1:I18,3,0)),(VLOOKUP(B2,J 1:K15,2,0)))