how !!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pierre - 28 May 2008 18:39 GMT

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

Reply to this Message

Pete_UK - 28 May 2008 18:55 GMT

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,Sheet2!
A:B,2,0)) + IF(ISNA(VLOOKUP(A1,Sheet3!A:B,2,0)),0,VLOOKUP(A1,Sheet3!
A:B,2,0)))

Copy this down as far as you think you need it.

Hope this helps.

Pete

> hello help me plz :
>
[quoted text clipped - 26 lines]
>
> thanks

Reply to this Message

pierre - 28 May 2008 19:08 GMT

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

> 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:
[quoted text clipped - 40 lines]
> >
> > thanks

Reply to this Message

Pete_UK - 28 May 2008 19:37 GMT

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

> 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
[quoted text clipped - 46 lines]
>
> - Show quoted text -

Reply to this Message

pierre - 28 May 2008 20:15 GMT

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

> OK, you could change it to this:
>
[quoted text clipped - 60 lines]
> >
> > - Show quoted text -

Reply to this Message

Pete_UK - 29 May 2008 01:08 GMT

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

> please i would like the results of "a" for example to be showed each one i
> a cell. i.e :
[quoted text clipped - 69 lines]
>
> - Show quoted text -

Reply to this Message

Pete_UK - 29 May 2008 01:11 GMT

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

> 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
[quoted text clipped - 90 lines]
>
> - Show quoted text -

Reply to this Message