| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how !!
hello help me plz :
given : in sheet 1 : A B 3 a 1 4 b 2 5 c 3 in sheet 2 : A B 1 c 4 2 d 5 3 a 3 in sheet 3 : A B 2 e 6 3 f 7 4 g 8 NOW i would like to make in sheet 4 the following : column a:a : the search string and i would like to receive in column b:b their coresponding results.. thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how !!
So, if you put "a" in A1 of sheet4, you want B1 to show 4 (the sum of
all the a's from the other 3 sheets?) If so, put this in B1 of Sheet4: =IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,0)),0 ,VLOOKUP(A1,Sheet1! A:B,2,0)) + IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),0,VLOOKUP(A1,S heet2! A:B,2,0)) + IF(ISNA(VLOOKUP(A1,Sheet3!A:B,2,0)),0,VLOOKUP(A1,S heet3! A:B,2,0))) Copy this down as far as you think you need it. Hope this helps. Pete On May 28, 6:39*pm, pierre wrote: hello help me plz : given : in sheet 1 : * * * A * *B 3 * *a * *1 4 * *b * *2 5 * *c * *3 in sheet 2 : * * * A * *B 1 * *c * * 4 2 * *d * * 5 3 * *a * * 3 in sheet 3 : * * * A * *B 2 * *e * *6 3 * *f * * 7 4 * *g * *8 NOW i would like to make in sheet 4 the following : column a:a *: the search string and i would like to receive in column b:b *their *coresponding results... thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how !!
no ...i want if i entered "a" to give me all its answers . i.e : 1 and 3
, if i entered "b" to give me =2 ect...ect "Pete_UK" wrote: So, if you put "a" in A1 of sheet4, you want B1 to show 4 (the sum of all the a's from the other 3 sheets?) If so, put this in B1 of Sheet4: =IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,0)),0 ,VLOOKUP(A1,Sheet1! A:B,2,0)) + IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),0,VLOOKUP(A1,S heet2! A:B,2,0)) + IF(ISNA(VLOOKUP(A1,Sheet3!A:B,2,0)),0,VLOOKUP(A1,S heet3! A:B,2,0))) Copy this down as far as you think you need it. Hope this helps. Pete On May 28, 6:39 pm, pierre wrote: hello help me plz : given : in sheet 1 : A B 3 a 1 4 b 2 5 c 3 in sheet 2 : A B 1 c 4 2 d 5 3 a 3 in sheet 3 : A B 2 e 6 3 f 7 4 g 8 NOW i would like to make in sheet 4 the following : column a:a : the search string and i would like to receive in column b:b their coresponding results... thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how !!
OK, you could change it to this:
=IF(A1="","",TRIM(IF(ISNA(VLOOKUP(A1,Sheet1!A:B, 2,0)),"",VLOOKUP(A1,Sheet1!A:B,2,0)) &" "& IF(ISNA(VLOOKUP(A1,Sheet2! A:B,2,0)),"",VLOOKUP(A1,Sheet2!A:B,2,0)) &" "& IF(ISNA(VLOOKUP(A1,Sheet3!A:B,2,0)),"",VLOOKUP(A1, Sheet3!A:B,2,0)))) this would give you a space between the numbers, like 1 3 for a, and 3 4 for c. Hope this helps. Pete On May 28, 7:08*pm, pierre wrote: no ...i want if i entered *"a" *to give me all its answers . *i.e : 1 *and 3 , if i entered "b" to give me =2 ect...ect "Pete_UK" wrote: So, if you put "a" in A1 of sheet4, you want B1 to show 4 (the sum of all the a's from the other 3 sheets?) If so, put this in B1 of Sheet4: =IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,0)),0 ,VLOOKUP(A1,Sheet1! A:B,2,0)) + IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),0,VLOOKUP(A1,S heet2! A:B,2,0)) + IF(ISNA(VLOOKUP(A1,Sheet3!A:B,2,0)),0,VLOOKUP(A1,S heet3! A:B,2,0))) Copy this down as far as you think you need it. Hope this helps. Pete On May 28, 6:39 pm, pierre wrote: hello help me plz : given : in sheet 1 : * * * A * *B 3 * *a * *1 4 * *b * *2 5 * *c * *3 in sheet 2 : * * * A * *B 1 * *c * * 4 2 * *d * * 5 3 * *a * * 3 in sheet 3 : * * * A * *B 2 * *e * *6 3 * *f * * 7 4 * *g * *8 NOW i would like to make in sheet 4 the following : column a:a *: the search string and i would like to receive in column b:b *their *coresponding results... thanks- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how !!
please i would like the results of "a" for example to be showed each one i
a cell. i.e : in A1= "a" ---- the results are : in cell B1 we should get =1 and in cell B2 we should get =3 ect...ect "Pete_UK" wrote: OK, you could change it to this: =IF(A1="","",TRIM(IF(ISNA(VLOOKUP(A1,Sheet1!A:B, 2,0)),"",VLOOKUP(A1,Sheet1!A:B,2,0)) &" "& IF(ISNA(VLOOKUP(A1,Sheet2! A:B,2,0)),"",VLOOKUP(A1,Sheet2!A:B,2,0)) &" "& IF(ISNA(VLOOKUP(A1,Sheet3!A:B,2,0)),"",VLOOKUP(A1, Sheet3!A:B,2,0)))) this would give you a space between the numbers, like 1 3 for a, and 3 4 for c. Hope this helps. Pete On May 28, 7:08 pm, pierre wrote: no ...i want if i entered "a" to give me all its answers . i.e : 1 and 3 , if i entered "b" to give me =2 ect...ect "Pete_UK" wrote: So, if you put "a" in A1 of sheet4, you want B1 to show 4 (the sum of all the a's from the other 3 sheets?) If so, put this in B1 of Sheet4: =IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,0)),0 ,VLOOKUP(A1,Sheet1! A:B,2,0)) + IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),0,VLOOKUP(A1,S heet2! A:B,2,0)) + IF(ISNA(VLOOKUP(A1,Sheet3!A:B,2,0)),0,VLOOKUP(A1,S heet3! A:B,2,0))) Copy this down as far as you think you need it. Hope this helps. Pete On May 28, 6:39 pm, pierre wrote: hello help me plz : given : in sheet 1 : A B 3 a 1 4 b 2 5 c 3 in sheet 2 : A B 1 c 4 2 d 5 3 a 3 in sheet 3 : A B 2 e 6 3 f 7 4 g 8 NOW i would like to make in sheet 4 the following : column a:a : the search string and i would like to receive in column b:b their coresponding results... thanks- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how !!
So, do you always want sheet1's results to be in column B, sheet2's
result in column C and sheet3's results in column D? If so, then put these formulae in the cells stated: B1: =IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet1!A:B, 2,0)),"",VLOOKUP(A1,Sheet1!A:B,2,0)) C1: =IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet2!A:B, 2,0)),"",VLOOKUP(A1,Sheet2!A:B,2,0)) D1: =IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet3!A:B, 2,0)),"",VLOOKUP(A1,Sheet3!A:B,2,0)) Then copy these down the columns as required. Hope this helps. Pete On May 28, 8:15*pm, pierre wrote: please i would like the results of *"a" *for example to be showed each one i a cell. i.e : in A1= "a" *---- *the results are : in cell B1 we should get =1 and in cell B2 we should get =3 ect...ect "Pete_UK" wrote: OK, you could change it to this: =IF(A1="","",TRIM(IF(ISNA(VLOOKUP(A1,Sheet1!A:B, 2,0)),"",VLOOKUP(A1,Sheet1!A:B,2,0)) &" "& IF(ISNA(VLOOKUP(A1,Sheet2! A:B,2,0)),"",VLOOKUP(A1,Sheet2!A:B,2,0)) &" "& IF(ISNA(VLOOKUP(A1,Sheet3!A:B,2,0)),"",VLOOKUP(A1, Sheet3!A:B,2,0)))) this would give you a space between the numbers, like 1 3 for a, and 3 4 for c. Hope this helps. Pete On May 28, 7:08 pm, pierre wrote: no ...i want if i entered *"a" *to give me all its answers . *i.e : 1 *and 3 , if i entered "b" to give me =2 ect...ect "Pete_UK" wrote: So, if you put "a" in A1 of sheet4, you want B1 to show 4 (the sum of all the a's from the other 3 sheets?) If so, put this in B1 of Sheet4: =IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,0)),0 ,VLOOKUP(A1,Sheet1! A:B,2,0)) + IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),0,VLOOKUP(A1,S heet2! A:B,2,0)) + IF(ISNA(VLOOKUP(A1,Sheet3!A:B,2,0)),0,VLOOKUP(A1,S heet3! A:B,2,0))) Copy this down as far as you think you need it. Hope this helps. Pete On May 28, 6:39 pm, pierre wrote: hello help me plz : given : in sheet 1 : * * * A * *B 3 * *a * *1 4 * *b * *2 5 * *c * *3 in sheet 2 : * * * A * *B 1 * *c * * 4 2 * *d * * 5 3 * *a * * 3 in sheet 3 : * * * A * *B 2 * *e * *6 3 * *f * * 7 4 * *g * *8 NOW i would like to make in sheet 4 the following : column a:a *: the search string and i would like to receive in column b:b *their *coresponding results... thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how !!
Sorry, I mis-read what you asked for. Put the formulae in these cells:
B1: =IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet1!A:B, 2,0)),"",VLOOKUP(A1,Sheet1!A:B,2,0)) B2: =IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet2!A:B, 2,0)),"",VLOOKUP(A1,Sheet2!A:B,2,0)) B3: =IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet3!A:B, 2,0)),"",VLOOKUP(A1,Sheet3!A:B,2,0)) Hope this helps. Pete On May 29, 1:08*am, Pete_UK wrote: So, do you always want sheet1's results to be in column B, sheet2's result in column C and sheet3's results in column D? If so, then put these formulae in the cells stated: B1: * *=IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet1!A:B, 2,0)),"",VLOOKUP(A1,Sheet1!A:B,2,0)) C1: * *=IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet2!A:B, 2,0)),"",VLOOKUP(A1,Sheet2!A:B,2,0)) D1: * *=IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet3!A:B, 2,0)),"",VLOOKUP(A1,Sheet3!A:B,2,0)) Then copy these down the columns as required. Hope this helps. Pete On May 28, 8:15*pm, pierre wrote: please i would like the results of *"a" *for example to be showed each one i a cell. i.e : in A1= "a" *---- *the results are : in cell B1 we should get =1 and in cell B2 we should get =3 ect...ect "Pete_UK" wrote: OK, you could change it to this: =IF(A1="","",TRIM(IF(ISNA(VLOOKUP(A1,Sheet1!A:B, 2,0)),"",VLOOKUP(A1,Sheet1!A:B,2,0)) &" "& IF(ISNA(VLOOKUP(A1,Sheet2! A:B,2,0)),"",VLOOKUP(A1,Sheet2!A:B,2,0)) &" "& IF(ISNA(VLOOKUP(A1,Sheet3!A:B,2,0)),"",VLOOKUP(A1, Sheet3!A:B,2,0)))) this would give you a space between the numbers, like 1 3 for a, and 3 4 for c. Hope this helps. Pete On May 28, 7:08 pm, pierre wrote: no ...i want if i entered *"a" *to give me all its answers . *i.e : 1 *and 3 , if i entered "b" to give me =2 ect...ect "Pete_UK" wrote: So, if you put "a" in A1 of sheet4, you want B1 to show 4 (the sum of all the a's from the other 3 sheets?) If so, put this in B1 of Sheet4: =IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,0)),0 ,VLOOKUP(A1,Sheet1! A:B,2,0)) + IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),0,VLOOKUP(A1,S heet2! A:B,2,0)) + IF(ISNA(VLOOKUP(A1,Sheet3!A:B,2,0)),0,VLOOKUP(A1,S heet3! A:B,2,0))) Copy this down as far as you think you need it. Hope this helps. Pete On May 28, 6:39 pm, pierre wrote: hello help me plz : given : in sheet 1 : * * * A * *B 3 * *a * *1 4 * *b * *2 5 * *c * *3 in sheet 2 : * * * A * *B 1 * *c * * 4 2 * *d * * 5 3 * *a * * 3 in sheet 3 : * * * A * *B 2 * *e * *6 3 * *f * * 7 4 * *g * *8 NOW i would like to make in sheet 4 the following : column a:a *: the search string and i would like to receive in column b:b *their *coresponding results... thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
