| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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))) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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))) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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))) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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))) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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))) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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))) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Printing on Legal Size Paper! | Excel Discussion (Misc queries) | |||
| Not all data prints on legal paper but does on other computer. | Excel Discussion (Misc queries) | |||
| Auto Adjust Spread Sheet so it will print on 1 page/legal/landscap | Excel Worksheet Functions | |||
| Sorting 'legal' numbers | Excel Discussion (Misc queries) | |||
Linear Mode
