Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 11th 07, 02:14 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 447
Default If, vlookup, data validation & dependent list

I have a data validation selection in A2 and a dependent list in A3. There
are instances where there's no dependent list after making a selection in A2;
so there's no entry made in A3.

I want to a formula in A4. The formula will say if A3 is not blank, vlookup
A3 in a lookup table. However, if A3 is blank, vlookup A2 in the same lookup
table. My formula currently reads like this: =IF(A3<"
",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE),VLOOKUP(A 2,Sheet1!$A$2:$B$141,2,FALSE)).

The trouble is when A3 is blank I get a #N/A result even though I know that
A2 is in the table. Please advise as to how to modify this formula to get
the correct result. All help is prematurely appreciated.
--
Thanks, Karen

  #2   Report Post  
Old July 11th 07, 02:26 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,594
Default If, vlookup, data validation & dependent list

Karen,

Try this

=IF(A3<"",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE), "")&IF(A3="",VLOOKUP(A2,Sheet1!$A$2:$B$141,2,FALSE ),"")

--
HTH

Bob

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

"Karen" wrote in message
...
I have a data validation selection in A2 and a dependent list in A3.
There
are instances where there's no dependent list after making a selection in
A2;
so there's no entry made in A3.

I want to a formula in A4. The formula will say if A3 is not blank,
vlookup
A3 in a lookup table. However, if A3 is blank, vlookup A2 in the same
lookup
table. My formula currently reads like this: =IF(A3<"
",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE),VLOOKUP(A 2,Sheet1!$A$2:$B$141,2,FALSE)).

The trouble is when A3 is blank I get a #N/A result even though I know
that
A2 is in the table. Please advise as to how to modify this formula to get
the correct result. All help is prematurely appreciated.
--
Thanks, Karen



  #3   Report Post  
Old July 11th 07, 02:28 PM posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,397
Default If, vlookup, data validation & dependent list

I am not sure but I think you have " " and not "" in your formula
if so change to "" and it should work

"Karen" wrote:

I have a data validation selection in A2 and a dependent list in A3. There
are instances where there's no dependent list after making a selection in A2;
so there's no entry made in A3.

I want to a formula in A4. The formula will say if A3 is not blank, vlookup
A3 in a lookup table. However, if A3 is blank, vlookup A2 in the same lookup
table. My formula currently reads like this: =IF(A3<"
",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE),VLOOKUP(A 2,Sheet1!$A$2:$B$141,2,FALSE)).

The trouble is when A3 is blank I get a #N/A result even though I know that
A2 is in the table. Please advise as to how to modify this formula to get
the correct result. All help is prematurely appreciated.
--
Thanks, Karen

  #4   Report Post  
Old July 11th 07, 02:28 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,339
Default If, vlookup, data validation & dependent list

Formula is OK

=IF(A3<"",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE), VLOOKUP(A2,Sheet1!$A$2:$B$141,2,FALSE))

Is A3 test this: A3<"" OR this A3<" "; the latter will give #N/A as it
will search on A3 not A2.

Check A2 data and Sheet1 Column A have no extra blanks in them.

"Karen" wrote:

I have a data validation selection in A2 and a dependent list in A3. There
are instances where there's no dependent list after making a selection in A2;
so there's no entry made in A3.

I want to a formula in A4. The formula will say if A3 is not blank, vlookup
A3 in a lookup table. However, if A3 is blank, vlookup A2 in the same lookup
table. My formula currently reads like this: =IF(A3<"
",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE),VLOOKUP(A 2,Sheet1!$A$2:$B$141,2,FALSE)).

The trouble is when A3 is blank I get a #N/A result even though I know that
A2 is in the table. Please advise as to how to modify this formula to get
the correct result. All help is prematurely appreciated.
--
Thanks, Karen

  #5   Report Post  
Old July 11th 07, 02:46 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 447
Default If, vlookup, data validation & dependent list

Thanks all for the assistance. It works well now.
--
Thanks, Karen


"Bob Phillips" wrote:

Karen,

Try this

=IF(A3<"",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE), "")&IF(A3="",VLOOKUP(A2,Sheet1!$A$2:$B$141,2,FALSE ),"")

--
HTH

Bob

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

"Karen" wrote in message
...
I have a data validation selection in A2 and a dependent list in A3.
There
are instances where there's no dependent list after making a selection in
A2;
so there's no entry made in A3.

I want to a formula in A4. The formula will say if A3 is not blank,
vlookup
A3 in a lookup table. However, if A3 is blank, vlookup A2 in the same
lookup
table. My formula currently reads like this: =IF(A3<"
",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE),VLOOKUP(A 2,Sheet1!$A$2:$B$141,2,FALSE)).

The trouble is when A3 is blank I get a #N/A result even though I know
that
A2 is in the table. Please advise as to how to modify this formula to get
the correct result. All help is prematurely appreciated.
--
Thanks, Karen






  #6   Report Post  
Old July 11th 07, 02:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 3,718
Default If, vlookup, data validation & dependent list

Simplify version:

=VLOOKUP(IF(OR(A3={""," "}),A2,A3),Sheet1!$A$2:$B$141,2,0)


"Karen" wrote:

I have a data validation selection in A2 and a dependent list in A3. There
are instances where there's no dependent list after making a selection in A2;
so there's no entry made in A3.

I want to a formula in A4. The formula will say if A3 is not blank, vlookup
A3 in a lookup table. However, if A3 is blank, vlookup A2 in the same lookup
table. My formula currently reads like this: =IF(A3<"
",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE),VLOOKUP(A 2,Sheet1!$A$2:$B$141,2,FALSE)).

The trouble is when A3 is blank I get a #N/A result even though I know that
A2 is in the table. Please advise as to how to modify this formula to get
the correct result. All help is prematurely appreciated.
--
Thanks, Karen

  #7   Report Post  
Old July 11th 07, 04:18 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,594
Default If, vlookup, data validation & dependent list

Surely TRIM is better in a 'simplified' version?

--
HTH

Bob

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

"Teethless mama" wrote in message
...
Simplify version:

=VLOOKUP(IF(OR(A3={""," "}),A2,A3),Sheet1!$A$2:$B$141,2,0)


"Karen" wrote:

I have a data validation selection in A2 and a dependent list in A3.
There
are instances where there's no dependent list after making a selection in
A2;
so there's no entry made in A3.

I want to a formula in A4. The formula will say if A3 is not blank,
vlookup
A3 in a lookup table. However, if A3 is blank, vlookup A2 in the same
lookup
table. My formula currently reads like this: =IF(A3<"
",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE),VLOOKUP(A 2,Sheet1!$A$2:$B$141,2,FALSE)).

The trouble is when A3 is blank I get a #N/A result even though I know
that
A2 is in the table. Please advise as to how to modify this formula to
get
the correct result. All help is prematurely appreciated.
--
Thanks, Karen





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
data validation--multiple dependent list Michael Excel Discussion (Misc queries) 9 May 2nd 06 01:14 AM
Dependent List (via Data Validation) Error Dezdan Excel Worksheet Functions 2 December 2nd 05 12:33 AM
custom dependent list validation ben h Excel Worksheet Functions 1 October 24th 05 05:31 AM
Dependent List- Data Validation Annie Excel Worksheet Functions 2 September 23rd 05 03:40 PM
Using Validation List from Another Workbook with Dependent Data Mike R. Excel Worksheet Functions 5 January 8th 05 07:06 PM


All times are GMT +1. The time now is 05:48 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017