Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula
Hi, I have the following 2 formulae and i have been desperately trying to
make them work but the dont! I am looking up the value of column D in 1 worksheet to look across 10 different sheets (which are all named) and then return the results as "1,0" when done into another sheet. So i am just looking up names from column D of sheet"names" and then looking across 10 sheets, again in the same column - D. These are the formulae i have tried. the 1st works on the sheets being called sheets 1,2 etc and not being renamed and the 2nd formula allows you to name the sheets then refer to them seperatly. i would like to use the 2nd as i all my sheets are named. =VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0) =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0) I either get the response N/A or VALUE. i dont know if i have got all the referenes correct, maybe thats why its not working. maybe the "d2:d100" is incorrect as i repeat it in the formula, but this is the range where the lookup should look in. I hope im making sense. Thanks for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula
For a VLOOKUP to function with an offset of more than 1, the LookupRange must
include the columns that contain the data to be returned as well as the column that contains the value to be looked up......... =VLOOKUP(A1,A:A,1,FALSE) will work... =VLOOKUPiA1,A:A,2,FALSE) will not....it should be -VLOOKUP(A1,A:B,FALSE) Vaya con Dios, Chuck, CABGx3 "Gemz" wrote: Hi, I have the following 2 formulae and i have been desperately trying to make them work but the dont! I am looking up the value of column D in 1 worksheet to look across 10 different sheets (which are all named) and then return the results as "1,0" when done into another sheet. So i am just looking up names from column D of sheet"names" and then looking across 10 sheets, again in the same column - D. These are the formulae i have tried. the 1st works on the sheets being called sheets 1,2 etc and not being renamed and the 2nd formula allows you to name the sheets then refer to them seperatly. i would like to use the 2nd as i all my sheets are named. =VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0) =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0) I either get the response N/A or VALUE. i dont know if i have got all the referenes correct, maybe thats why its not working. maybe the "d2:d100" is incorrect as i repeat it in the formula, but this is the range where the lookup should look in. I hope im making sense. Thanks for your help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula
But i am looking in a number of sheets, how can i tell this formula to lookup
c:c in each sheet. the thing is because i am only looking up names i just need to see if the name on one sheet is present anywhere within the 20 sheets and it doesnt need to return anything other than "1,false" which means just to return the name IF it appears anywhere within the 20 sheets. thanks. "CLR" wrote: For a VLOOKUP to function with an offset of more than 1, the LookupRange must include the columns that contain the data to be returned as well as the column that contains the value to be looked up......... =VLOOKUP(A1,A:A,1,FALSE) will work... =VLOOKUPiA1,A:A,2,FALSE) will not....it should be -VLOOKUP(A1,A:B,FALSE) Vaya con Dios, Chuck, CABGx3 "Gemz" wrote: Hi, I have the following 2 formulae and i have been desperately trying to make them work but the dont! I am looking up the value of column D in 1 worksheet to look across 10 different sheets (which are all named) and then return the results as "1,0" when done into another sheet. So i am just looking up names from column D of sheet"names" and then looking across 10 sheets, again in the same column - D. These are the formulae i have tried. the 1st works on the sheets being called sheets 1,2 etc and not being renamed and the 2nd formula allows you to name the sheets then refer to them seperatly. i would like to use the 2nd as i all my sheets are named. =VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0) =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0) I either get the response N/A or VALUE. i dont know if i have got all the referenes correct, maybe thats why its not working. maybe the "d2:d100" is incorrect as i repeat it in the formula, but this is the range where the lookup should look in. I hope im making sense. Thanks for your help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula
Sorry, I guess I missed your point. I've never done exactly what you are
attempting, but I think my first choice would be VBA.....and my second would be to use a helper column with the 20 individual VLOOKUPS there and then use one master VLOOKUP to check that column for results...........just my thoughts... Vaya con Dios, Chuck, CABGx3 "Gemz" wrote: But i am looking in a number of sheets, how can i tell this formula to lookup c:c in each sheet. the thing is because i am only looking up names i just need to see if the name on one sheet is present anywhere within the 20 sheets and it doesnt need to return anything other than "1,false" which means just to return the name IF it appears anywhere within the 20 sheets. thanks. "CLR" wrote: For a VLOOKUP to function with an offset of more than 1, the LookupRange must include the columns that contain the data to be returned as well as the column that contains the value to be looked up......... =VLOOKUP(A1,A:A,1,FALSE) will work... =VLOOKUPiA1,A:A,2,FALSE) will not....it should be -VLOOKUP(A1,A:B,FALSE) Vaya con Dios, Chuck, CABGx3 "Gemz" wrote: Hi, I have the following 2 formulae and i have been desperately trying to make them work but the dont! I am looking up the value of column D in 1 worksheet to look across 10 different sheets (which are all named) and then return the results as "1,0" when done into another sheet. So i am just looking up names from column D of sheet"names" and then looking across 10 sheets, again in the same column - D. These are the formulae i have tried. the 1st works on the sheets being called sheets 1,2 etc and not being renamed and the 2nd formula allows you to name the sheets then refer to them seperatly. i would like to use the 2nd as i all my sheets are named. =VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0) =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0) I either get the response N/A or VALUE. i dont know if i have got all the referenes correct, maybe thats why its not working. maybe the "d2:d100" is incorrect as i repeat it in the formula, but this is the range where the lookup should look in. I hope im making sense. Thanks for your help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula
Maybe you overlooked the formulas that i have been playing around with..
=VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0) =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0) i got these off previous posts on this website, where people have been provided formule to work through, i tried to apply it to my situation but it didnt work. if not via formula, how do i do it via vba? please help, i really dont know what to do. "CLR" wrote: Sorry, I guess I missed your point. I've never done exactly what you are attempting, but I think my first choice would be VBA.....and my second would be to use a helper column with the 20 individual VLOOKUPS there and then use one master VLOOKUP to check that column for results...........just my thoughts... Vaya con Dios, Chuck, CABGx3 "Gemz" wrote: But i am looking in a number of sheets, how can i tell this formula to lookup c:c in each sheet. the thing is because i am only looking up names i just need to see if the name on one sheet is present anywhere within the 20 sheets and it doesnt need to return anything other than "1,false" which means just to return the name IF it appears anywhere within the 20 sheets. thanks. "CLR" wrote: For a VLOOKUP to function with an offset of more than 1, the LookupRange must include the columns that contain the data to be returned as well as the column that contains the value to be looked up......... =VLOOKUP(A1,A:A,1,FALSE) will work... =VLOOKUPiA1,A:A,2,FALSE) will not....it should be -VLOOKUP(A1,A:B,FALSE) Vaya con Dios, Chuck, CABGx3 "Gemz" wrote: Hi, I have the following 2 formulae and i have been desperately trying to make them work but the dont! I am looking up the value of column D in 1 worksheet to look across 10 different sheets (which are all named) and then return the results as "1,0" when done into another sheet. So i am just looking up names from column D of sheet"names" and then looking across 10 sheets, again in the same column - D. These are the formulae i have tried. the 1st works on the sheets being called sheets 1,2 etc and not being renamed and the 2nd formula allows you to name the sheets then refer to them seperatly. i would like to use the 2nd as i all my sheets are named. =VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0) =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0) I either get the response N/A or VALUE. i dont know if i have got all the referenes correct, maybe thats why its not working. maybe the "d2:d100" is incorrect as i repeat it in the formula, but this is the range where the lookup should look in. I hope im making sense. Thanks for your help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula
This may get you started.........
Sub FindTheName() Dim sh As Worksheet Dim searchname Dim k Dim iCount As Integer iCount = 0 k = InputBox("Enter NAME to search for:") 'case sensitive searchname = k For Each sh In Worksheets sh.Select Dim lastrow As Long, r As Long lastrow = Cells(Rows.Count, "D").End(xlUp).Row For r = lastrow To 1 Step -1 If Cells(r, "D") = k Then MsgBox "Name found on " & ActiveSheet.Name & " in cell " & Cells(r, "d").Address '"tom" iCount = iCount + 1 Else End If Next r Next sh If iCount = 0 Then MsgBox "NAME NOT FOUND" Else MsgBox "NAME FOUND " & iCount & " TIMES" End If End Sub Vaya con Dios, Chuck, CABGx3 "Gemz" wrote: Maybe you overlooked the formulas that i have been playing around with.. =VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0) =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0) i got these off previous posts on this website, where people have been provided formule to work through, i tried to apply it to my situation but it didnt work. if not via formula, how do i do it via vba? please help, i really dont know what to do. "CLR" wrote: Sorry, I guess I missed your point. I've never done exactly what you are attempting, but I think my first choice would be VBA.....and my second would be to use a helper column with the 20 individual VLOOKUPS there and then use one master VLOOKUP to check that column for results...........just my thoughts... Vaya con Dios, Chuck, CABGx3 "Gemz" wrote: But i am looking in a number of sheets, how can i tell this formula to lookup c:c in each sheet. the thing is because i am only looking up names i just need to see if the name on one sheet is present anywhere within the 20 sheets and it doesnt need to return anything other than "1,false" which means just to return the name IF it appears anywhere within the 20 sheets. thanks. "CLR" wrote: For a VLOOKUP to function with an offset of more than 1, the LookupRange must include the columns that contain the data to be returned as well as the column that contains the value to be looked up......... =VLOOKUP(A1,A:A,1,FALSE) will work... =VLOOKUPiA1,A:A,2,FALSE) will not....it should be -VLOOKUP(A1,A:B,FALSE) Vaya con Dios, Chuck, CABGx3 "Gemz" wrote: Hi, I have the following 2 formulae and i have been desperately trying to make them work but the dont! I am looking up the value of column D in 1 worksheet to look across 10 different sheets (which are all named) and then return the results as "1,0" when done into another sheet. So i am just looking up names from column D of sheet"names" and then looking across 10 sheets, again in the same column - D. These are the formulae i have tried. the 1st works on the sheets being called sheets 1,2 etc and not being renamed and the 2nd formula allows you to name the sheets then refer to them seperatly. i would like to use the 2nd as i all my sheets are named. =VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0) =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0) I either get the response N/A or VALUE. i dont know if i have got all the referenes correct, maybe thats why its not working. maybe the "d2:d100" is incorrect as i repeat it in the formula, but this is the range where the lookup should look in. I hope im making sense. Thanks for your help. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula
Your 2nd formula works but you have mismatched references:
=VLOOKUP(B2,..........,B1)............) Both of those references need to the same. Also, don't forget that this is an array formula. Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Gemz" wrote in message ... Hi, I have the following 2 formulae and i have been desperately trying to make them work but the dont! I am looking up the value of column D in 1 worksheet to look across 10 different sheets (which are all named) and then return the results as "1,0" when done into another sheet. So i am just looking up names from column D of sheet"names" and then looking across 10 sheets, again in the same column - D. These are the formulae i have tried. the 1st works on the sheets being called sheets 1,2 etc and not being renamed and the 2nd formula allows you to name the sheets then refer to them seperatly. i would like to use the 2nd as i all my sheets are named. =VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0) =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0) I either get the response N/A or VALUE. i dont know if i have got all the referenes correct, maybe thats why its not working. maybe the "d2:d100" is incorrect as i repeat it in the formula, but this is the range where the lookup should look in. I hope im making sense. Thanks for your help. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula
I have now used the formula from your example sheet:
=VLOOKUP(A2,INDIRECT("'"&INDEX(WSList,MATCH(TRUE,C OUNTIF(INDIRECT("'"&WSList&"'!A:A"),A2)0,0))&"'!A :B"),2,0) This works ok, expect the formula returns back information that i dont really need, i understand this is because of the "2,0" but when i change it to "1,0" it doesnt work! i do no want it to return anything additional back, for example, as i am just searching for peoples names in the 20 sheets to see if they are present i just want it to return the persons name if they are present, i do not require any additional information being returned. how can i change the formula to do this? thanks alot. "T. Valko" wrote: Your 2nd formula works but you have mismatched references: =VLOOKUP(B2,..........,B1)............) Both of those references need to the same. Also, don't forget that this is an array formula. Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Gemz" wrote in message ... Hi, I have the following 2 formulae and i have been desperately trying to make them work but the dont! I am looking up the value of column D in 1 worksheet to look across 10 different sheets (which are all named) and then return the results as "1,0" when done into another sheet. So i am just looking up names from column D of sheet"names" and then looking across 10 sheets, again in the same column - D. These are the formulae i have tried. the 1st works on the sheets being called sheets 1,2 etc and not being renamed and the 2nd formula allows you to name the sheets then refer to them seperatly. i would like to use the 2nd as i all my sheets are named. =VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0) =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0) I either get the response N/A or VALUE. i dont know if i have got all the referenes correct, maybe thats why its not working. maybe the "d2:d100" is incorrect as i repeat it in the formula, but this is the range where the lookup should look in. I hope im making sense. Thanks for your help. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula
when i change it to "1,0" it doesnt work!
Hmmm... It works for me. However, if all you want to do is verify that the name appears on some other sheet try this instead (normally entered, not array entered although it'll work either way): =IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&wslist&"'!A:A" ),A2)),A2,"") -- Biff Microsoft Excel MVP "Gemz" wrote in message ... I have now used the formula from your example sheet: =VLOOKUP(A2,INDIRECT("'"&INDEX(WSList,MATCH(TRUE,C OUNTIF(INDIRECT("'"&WSList&"'!A:A"),A2)0,0))&"'!A :B"),2,0) This works ok, expect the formula returns back information that i dont really need, i understand this is because of the "2,0" but when i change it to "1,0" it doesnt work! i do no want it to return anything additional back, for example, as i am just searching for peoples names in the 20 sheets to see if they are present i just want it to return the persons name if they are present, i do not require any additional information being returned. how can i change the formula to do this? thanks alot. "T. Valko" wrote: Your 2nd formula works but you have mismatched references: =VLOOKUP(B2,..........,B1)............) Both of those references need to the same. Also, don't forget that this is an array formula. Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Gemz" wrote in message ... Hi, I have the following 2 formulae and i have been desperately trying to make them work but the dont! I am looking up the value of column D in 1 worksheet to look across 10 different sheets (which are all named) and then return the results as "1,0" when done into another sheet. So i am just looking up names from column D of sheet"names" and then looking across 10 sheets, again in the same column - D. These are the formulae i have tried. the 1st works on the sheets being called sheets 1,2 etc and not being renamed and the 2nd formula allows you to name the sheets then refer to them seperatly. i would like to use the 2nd as i all my sheets are named. =VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0) =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0) I either get the response N/A or VALUE. i dont know if i have got all the referenes correct, maybe thats why its not working. maybe the "d2:d100" is incorrect as i repeat it in the formula, but this is the range where the lookup should look in. I hope im making sense. Thanks for your help. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula
Thanks for the new formula, it worked when i tested it on 3 sheets but when i
tried to make it work for what i need it for (which are 31 sheets) it doesnt work! This is the formula i changed to new cell references: =IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&WSlist&"'!D:D" ),A2)),A2,"") I just keep getting "REF!". I have the sheet names re-named in the exact way they appear on the sheet tabs and they are in the named range "WSList", the data that i am looking up is in column A (which is in the sheet where i am doing the formula which is an unnamed sheet1) and in all the other sheets i need to look in column D. what am i doing wrong? please help. thanks. "T. Valko" wrote: when i change it to "1,0" it doesnt work! Hmmm... It works for me. However, if all you want to do is verify that the name appears on some other sheet try this instead (normally entered, not array entered although it'll work either way): =IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&wslist&"'!A:A" ),A2)),A2,"") -- Biff Microsoft Excel MVP "Gemz" wrote in message ... I have now used the formula from your example sheet: =VLOOKUP(A2,INDIRECT("'"&INDEX(WSList,MATCH(TRUE,C OUNTIF(INDIRECT("'"&WSList&"'!A:A"),A2)0,0))&"'!A :B"),2,0) This works ok, expect the formula returns back information that i dont really need, i understand this is because of the "2,0" but when i change it to "1,0" it doesnt work! i do no want it to return anything additional back, for example, as i am just searching for peoples names in the 20 sheets to see if they are present i just want it to return the persons name if they are present, i do not require any additional information being returned. how can i change the formula to do this? thanks alot. "T. Valko" wrote: Your 2nd formula works but you have mismatched references: =VLOOKUP(B2,..........,B1)............) Both of those references need to the same. Also, don't forget that this is an array formula. Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Gemz" wrote in message ... Hi, I have the following 2 formulae and i have been desperately trying to make them work but the dont! I am looking up the value of column D in 1 worksheet to look across 10 different sheets (which are all named) and then return the results as "1,0" when done into another sheet. So i am just looking up names from column D of sheet"names" and then looking across 10 sheets, again in the same column - D. These are the formulae i have tried. the 1st works on the sheets being called sheets 1,2 etc and not being renamed and the 2nd formula allows you to name the sheets then refer to them seperatly. i would like to use the 2nd as i all my sheets are named. =VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0) =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0) I either get the response N/A or VALUE. i dont know if i have got all the referenes correct, maybe thats why its not working. maybe the "d2:d100" is incorrect as i repeat it in the formula, but this is the range where the lookup should look in. I hope im making sense. Thanks for your help. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula
I tested the formula on a file with 32 sheets and it worked ok.
Is the defined name WSList a static range or is it dynamic? If it's dynamic you'll get a #REF! error because INDIRECT won't interpret this correctly. Are there any empty cells in WSList? If a sheet does not exist (yet) but the sheet name is in WSList then you'll get a #REF! error. what i need it for (which are 31 sheets) 31 sheets for the days of the month? If so, what are the sheet names? 1, 2, 3 .... 31? If the sheet names follow some sort of sequential pattern you can eliminate having to create the WSList. -- Biff Microsoft Excel MVP "Gemz" wrote in message ... Thanks for the new formula, it worked when i tested it on 3 sheets but when i tried to make it work for what i need it for (which are 31 sheets) it doesnt work! This is the formula i changed to new cell references: =IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&WSlist&"'!D:D" ),A2)),A2,"") I just keep getting "REF!". I have the sheet names re-named in the exact way they appear on the sheet tabs and they are in the named range "WSList", the data that i am looking up is in column A (which is in the sheet where i am doing the formula which is an unnamed sheet1) and in all the other sheets i need to look in column D. what am i doing wrong? please help. thanks. "T. Valko" wrote: when i change it to "1,0" it doesnt work! Hmmm... It works for me. However, if all you want to do is verify that the name appears on some other sheet try this instead (normally entered, not array entered although it'll work either way): =IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&wslist&"'!A:A" ),A2)),A2,"") -- Biff Microsoft Excel MVP "Gemz" wrote in message ... I have now used the formula from your example sheet: =VLOOKUP(A2,INDIRECT("'"&INDEX(WSList,MATCH(TRUE,C OUNTIF(INDIRECT("'"&WSList&"'!A:A"),A2)0,0))&"'!A :B"),2,0) This works ok, expect the formula returns back information that i dont really need, i understand this is because of the "2,0" but when i change it to "1,0" it doesnt work! i do no want it to return anything additional back, for example, as i am just searching for peoples names in the 20 sheets to see if they are present i just want it to return the persons name if they are present, i do not require any additional information being returned. how can i change the formula to do this? thanks alot. "T. Valko" wrote: Your 2nd formula works but you have mismatched references: =VLOOKUP(B2,..........,B1)............) Both of those references need to the same. Also, don't forget that this is an array formula. Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Gemz" wrote in message ... Hi, I have the following 2 formulae and i have been desperately trying to make them work but the dont! I am looking up the value of column D in 1 worksheet to look across 10 different sheets (which are all named) and then return the results as "1,0" when done into another sheet. So i am just looking up names from column D of sheet"names" and then looking across 10 sheets, again in the same column - D. These are the formulae i have tried. the 1st works on the sheets being called sheets 1,2 etc and not being renamed and the 2nd formula allows you to name the sheets then refer to them seperatly. i would like to use the 2nd as i all my sheets are named. =VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0) =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0) I either get the response N/A or VALUE. i dont know if i have got all the referenes correct, maybe thats why its not working. maybe the "d2:d100" is incorrect as i repeat it in the formula, but this is the range where the lookup should look in. I hope im making sense. Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 VLOOKUP formula or other formula | Excel Discussion (Misc queries) | |||
Vlookup using a formula possible? | Excel Worksheet Functions | |||
VLookup Formula | Excel Discussion (Misc queries) | |||
vlookup formula | Excel Worksheet Functions | |||
how to use vlookup formula | Excel Worksheet Functions |