#1   Report Post  
Old May 28th 08, 06:39 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 149
Default 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   Report Post  
Old May 28th 08, 06:55 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default 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   Report Post  
Old May 28th 08, 07:08 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 149
Default 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   Report Post  
Old May 28th 08, 07:37 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default 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   Report Post  
Old May 28th 08, 08:15 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 149
Default 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   Report Post  
Old May 29th 08, 01:08 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default 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   Report Post  
Old May 29th 08, 01:11 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default 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
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



All times are GMT +1. The time now is 06:41 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017