Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Received the following reply to earlier question (below).
Doesn't work. Can the above two statements be combined? Ie. If one cell (in another worksheet) = "Active" then lookup the values from the same worksheet. I've been trying: VLOOKUP(IF('Entry 1 - New Clients'!N2:N31="ACTIVE",'Entry 1 - New Clients'!A2,'Entry 1 - New Clients'! A2:O20,2,FALSE)) Returns ugly errors. Maybe Excel doesn't like the combination of both statements? Yes, just re-use the formula in another cell with a different column index =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,3,FALSE). note the ,3 not ,2 -- HTH RP (remove nothere from the email address if mailing direct) "Fiona" wrote in message ... Received advise previously on this (thanks). However, I need to look up entries in another separate worksheet and it won't work. I'm using: =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,2,FALSE). It returns the persons name (great) but I need to transfer across other details too. Basically I'm summarising one worksheet onto a specified format in another. In addition, I somehow want it to only show me those clients in 'Entry 1 - New Clients' if the corresponding column (N) has the entry of "Active". Is this possible?? Fiona .. |
#2
![]() |
|||
|
|||
![]()
Hi!
You need an entirely different formula because you're doing a multiple criteria lookup. Man, I hate long sheet names! <g It looks to me as though you want to lookup a clients name in column A then lookup "active" in column N and return the corresponding value in column B? Something like this entered with the key combo of CTRL,SHIFT,ENTER: =IF(ISNA(MATCH(1,(A2:A11=A2)*(N2:N11="active"),0)) ,"",INDEX (B2:B11,MATCH(1,(A2:A11=A2)*(N2:N11="active"),0))) Just think how much longer that formula will get once you put those long sheet names in! <g Biff -----Original Message----- Received the following reply to earlier question (below). Doesn't work. Can the above two statements be combined? Ie. If one cell (in another worksheet) = "Active" then lookup the values from the same worksheet. I've been trying: VLOOKUP(IF('Entry 1 - New Clients'!N2:N31="ACTIVE",'Entry 1 - New Clients'!A2,'Entry 1 - New Clients'! A2:O20,2,FALSE)) Returns ugly errors. Maybe Excel doesn't like the combination of both statements? Yes, just re-use the formula in another cell with a different column index =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,3,FALSE). note the ,3 not ,2 -- HTH RP (remove nothere from the email address if mailing direct) "Fiona" wrote in message ... Received advise previously on this (thanks). However, I need to look up entries in another separate worksheet and it won't work. I'm using: =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,2,FALSE). It returns the persons name (great) but I need to transfer across other details too. Basically I'm summarising one worksheet onto a specified format in another. In addition, I somehow want it to only show me those clients in 'Entry 1 - New Clients' if the corresponding column (N) has the entry of "Active". Is this possible?? Fiona .. . |
#3
![]() |
|||
|
|||
![]()
I agree with you about how the long worksheet names clutter up the
formula--especially when you're creating it. I like to rename the long worksheet name to A. Then build the formula. Then after it's perfect, I change the name of the worksheet. This works fine until I get to a formula that will be longer than 1024 characters (measured in R1C1 reference style). But I'd have that problem anyway with the long worksheet name. (More for the OP, than you, Biff. <bg) Biff wrote: Hi! You need an entirely different formula because you're doing a multiple criteria lookup. Man, I hate long sheet names! <g It looks to me as though you want to lookup a clients name in column A then lookup "active" in column N and return the corresponding value in column B? Something like this entered with the key combo of CTRL,SHIFT,ENTER: =IF(ISNA(MATCH(1,(A2:A11=A2)*(N2:N11="active"),0)) ,"",INDEX (B2:B11,MATCH(1,(A2:A11=A2)*(N2:N11="active"),0))) Just think how much longer that formula will get once you put those long sheet names in! <g Biff -----Original Message----- Received the following reply to earlier question (below). Doesn't work. Can the above two statements be combined? Ie. If one cell (in another worksheet) = "Active" then lookup the values from the same worksheet. I've been trying: VLOOKUP(IF('Entry 1 - New Clients'!N2:N31="ACTIVE",'Entry 1 - New Clients'!A2,'Entry 1 - New Clients'! A2:O20,2,FALSE)) Returns ugly errors. Maybe Excel doesn't like the combination of both statements? Yes, just re-use the formula in another cell with a different column index =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,3,FALSE). note the ,3 not ,2 -- HTH RP (remove nothere from the email address if mailing direct) "Fiona" wrote in message ... Received advise previously on this (thanks). However, I need to look up entries in another separate worksheet and it won't work. I'm using: =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,2,FALSE). It returns the persons name (great) but I need to transfer across other details too. Basically I'm summarising one worksheet onto a specified format in another. In addition, I somehow want it to only show me those clients in 'Entry 1 - New Clients' if the corresponding column (N) has the entry of "Active". Is this possible?? Fiona .. . -- Dave Peterson |
#4
![]() |
|||
|
|||
![]()
Hi Dave!
I like to rename the long worksheet name to A. Then build the formula. Then after it's perfect, I change the name of the worksheet. That's a good idea! Biff -----Original Message----- I agree with you about how the long worksheet names clutter up the formula--especially when you're creating it. I like to rename the long worksheet name to A. Then build the formula. Then after it's perfect, I change the name of the worksheet. This works fine until I get to a formula that will be longer than 1024 characters (measured in R1C1 reference style). But I'd have that problem anyway with the long worksheet name. (More for the OP, than you, Biff. <bg) Biff wrote: Hi! You need an entirely different formula because you're doing a multiple criteria lookup. Man, I hate long sheet names! <g It looks to me as though you want to lookup a clients name in column A then lookup "active" in column N and return the corresponding value in column B? Something like this entered with the key combo of CTRL,SHIFT,ENTER: =IF(ISNA(MATCH(1,(A2:A11=A2)* (N2:N11="active"),0)),"",INDEX (B2:B11,MATCH(1,(A2:A11=A2)*(N2:N11="active"),0))) Just think how much longer that formula will get once you put those long sheet names in! <g Biff -----Original Message----- Received the following reply to earlier question (below). Doesn't work. Can the above two statements be combined? Ie. If one cell (in another worksheet) = "Active" then lookup the values from the same worksheet. I've been trying: VLOOKUP(IF('Entry 1 - New Clients'! N2:N31="ACTIVE",'Entry 1 - New Clients'!A2,'Entry 1 - New Clients'! A2:O20,2,FALSE)) Returns ugly errors. Maybe Excel doesn't like the combination of both statements? Yes, just re-use the formula in another cell with a different column index =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,3,FALSE). note the ,3 not ,2 -- HTH RP (remove nothere from the email address if mailing direct) "Fiona" wrote in message ... Received advise previously on this (thanks). However, I need to look up entries in another separate worksheet and it won't work. I'm using: =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,2,FALSE). It returns the persons name (great) but I need to transfer across other details too. Basically I'm summarising one worksheet onto a specified format in another. In addition, I somehow want it to only show me those clients in 'Entry 1 - New Clients' if the corresponding column (N) has the entry of "Active". Is this possible?? Fiona .. . -- Dave Peterson . |
#5
![]() |
|||
|
|||
![]()
Legendary - works perfectly (apart from the enormous
statement this ends up being) F -----Original Message----- Hi! You need an entirely different formula because you're doing a multiple criteria lookup. Man, I hate long sheet names! <g It looks to me as though you want to lookup a clients name in column A then lookup "active" in column N and return the corresponding value in column B? Something like this entered with the key combo of CTRL,SHIFT,ENTER: =IF(ISNA(MATCH(1,(A2:A11=A2)* (N2:N11="active"),0)),"",INDEX (B2:B11,MATCH(1,(A2:A11=A2)*(N2:N11="active"),0)) ) Just think how much longer that formula will get once you put those long sheet names in! <g Biff -----Original Message----- Received the following reply to earlier question (below). Doesn't work. Can the above two statements be combined? Ie. If one cell (in another worksheet) = "Active" then lookup the values from the same worksheet. I've been trying: VLOOKUP(IF('Entry 1 - New Clients'! N2:N31="ACTIVE",'Entry 1 - New Clients'!A2,'Entry 1 - New Clients'! A2:O20,2,FALSE)) Returns ugly errors. Maybe Excel doesn't like the combination of both statements? Yes, just re-use the formula in another cell with a different column index =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,3,FALSE). note the ,3 not ,2 -- HTH RP (remove nothere from the email address if mailing direct) "Fiona" wrote in message ... Received advise previously on this (thanks). However, I need to look up entries in another separate worksheet and it won't work. I'm using: =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,2,FALSE). It returns the persons name (great) but I need to transfer across other details too. Basically I'm summarising one worksheet onto a specified format in another. In addition, I somehow want it to only show me those clients in 'Entry 1 - New Clients' if the corresponding column (N) has the entry of "Active". Is this possible?? Fiona .. . . |
#6
![]() |
|||
|
|||
![]()
Legendary - works perfectly (apart from the enormous
statement this ends up being) F -----Original Message----- Hi! You need an entirely different formula because you're doing a multiple criteria lookup. Man, I hate long sheet names! <g It looks to me as though you want to lookup a clients name in column A then lookup "active" in column N and return the corresponding value in column B? Something like this entered with the key combo of CTRL,SHIFT,ENTER: =IF(ISNA(MATCH(1,(A2:A11=A2)* (N2:N11="active"),0)),"",INDEX (B2:B11,MATCH(1,(A2:A11=A2)*(N2:N11="active"),0)) ) Just think how much longer that formula will get once you put those long sheet names in! <g Biff -----Original Message----- Received the following reply to earlier question (below). Doesn't work. Can the above two statements be combined? Ie. If one cell (in another worksheet) = "Active" then lookup the values from the same worksheet. I've been trying: VLOOKUP(IF('Entry 1 - New Clients'! N2:N31="ACTIVE",'Entry 1 - New Clients'!A2,'Entry 1 - New Clients'! A2:O20,2,FALSE)) Returns ugly errors. Maybe Excel doesn't like the combination of both statements? Yes, just re-use the formula in another cell with a different column index =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,3,FALSE). note the ,3 not ,2 -- HTH RP (remove nothere from the email address if mailing direct) "Fiona" wrote in message ... Received advise previously on this (thanks). However, I need to look up entries in another separate worksheet and it won't work. I'm using: =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,2,FALSE). It returns the persons name (great) but I need to transfer across other details too. Basically I'm summarising one worksheet onto a specified format in another. In addition, I somehow want it to only show me those clients in 'Entry 1 - New Clients' if the corresponding column (N) has the entry of "Active". Is this possible?? Fiona .. . . |
#7
![]() |
|||
|
|||
![]()
did you try putting the IF statement first :
=IF('Entry 1 - New Clients'!N2="ACTIVE",VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,3,FALSE)),"") Is this what you were trying to achieve ? Fadi On Thu, 10 Mar 2005 20:55:02 -0800, wrote: Received the following reply to earlier question (below). Doesn't work. Can the above two statements be combined? Ie. If one cell (in another worksheet) = "Active" then lookup the values from the same worksheet. I've been trying: VLOOKUP(IF('Entry 1 - New Clients'!N2:N31="ACTIVE",'Entry 1 - New Clients'!A2,'Entry 1 - New Clients'! A2:O20,2,FALSE)) Returns ugly errors. Maybe Excel doesn't like the combination of both statements? Yes, just re-use the formula in another cell with a different column index =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,3,FALSE). note the ,3 not ,2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP statements | Excel Discussion (Misc queries) | |||
VLOOKUP not working | Excel Worksheet Functions |