To get value in another sheet dynamically

[jagadeeshds]

Dear members,

I need to get amount and name stored in sheet2 by comparing id numbers stored in sheet1

Sheet2 values


A B C H

______________________________

1 1000000 DM

2 SS100148 DM2 350

3 SS100278 Dm3 350

4 SS100364 DM4 1050

5 SS100473 DM5 350

6 SS100584 DM6 1400

7 SS100616 DM7 1050

-----------------------------------------------------------------------

Getting values in Sheet1 (Contains columns A to F)


A B C D E F

S. No. ID -- Name -- Amount
---------------------------------------------------
1 SS100584 1400
2 SS100616 1050

Here, i got amount ( H column in sheet2 by compaing id coumn in sheet1 compoaing with b column in sheet2 ) using the formula

=SUM(SUMIF(Sheet2!B5:Sheet2!B140,B2,Sheet2!H5:Sheet2!H140))

But, i am not able to get name (C Column in sheet2 by compaing id coumn in sheet1 compoaing with b column in sheet2) . Immediate help is required.

Thanks in advance.

 

[Special-K99]

Each ID number in column B occurs only once. If this is not the case solution will not work.
Hence the need to supply accurate data.

Since the ID occurs only once this is a simple case of VLOOKUP or INDEX(...MATCH())

(I cant make out where your columns are in Sheet1)

in Sheet1!B1
=VLOOKUP(B1,Sheet1:B$1:H$7,2,0)
will return the name

in Sheet1!D1
=VLOOKUP(B1,Sheet1:B$1:H$7,7,0)
will return the amount

 

[jagadeeshds]

Thanks for your reply.
Yes you are right. ID value occurs only once in both sheets.

ID columns in sheet1 (Sheet1!C2) has to be compared with ID Column in Sheet2
(Sheet2!B5). Here in this vlookup function i don't know where sheet2 data is compared. kindly explain. i am not getting the result.

Each ID number in column B occurs only once. If this is not the case solution will not work.
Hence the need to supply accurate data.

Since the ID occurs only once this is a simple case of VLOOKUP or INDEX(...MATCH())

(I cant make out where your columns are in Sheet1)

in Sheet1!B1
=VLOOKUP(B1,Sheet1:B$1:H$7,2,0)
will return the name

in Sheet1!D1
=VLOOKUP(B1,Sheet1:B$1:H$7,7,0)
will return the amount

 

[Special-K99]

Oops, my mistake

The results should are going into Sheet 2 aren't they?

in which case that should be

in Sheet2!B1
=VLOOKUP(B1,Sheet1:B$1:H$7,2,0)
will return the name

in Sheet2!D1
=VLOOKUP(B1,Sheet1:B$1:H$7,7,0)
will return the amount

 

[jagadeeshds]

Dear Sir,
Thanks for your immediate reply.

My concept is we will paste data in sheet2. Depending on the new data we have to calculate in sheet1 as given in very first question.

We have to compare all data in Sheet2!B5:Sheet2!B262 with Sheet1!B1:Shhet1!B12. And weekly Sheet 2 will be get updated, at that time we have to get result automatically. Hope, this is enough.

Oops, my mistake

The results should are going into Sheet 2 aren't they?

in which case that should be

in Sheet2!B1
=VLOOKUP(B1,Sheet1:B$1:H$7,2,0)
will return the name

in Sheet2!D1
=VLOOKUP(B1,Sheet1:B$1:H$7,7,0)
will return the amount

 

[Special-K99]

You havent said whether this worked or not.
Just adjust the ranges as required.

 

[jagadeeshds]

Dear Sir,
Thank you. I got the result.

You havent said whether this worked or not.
Just adjust the ranges as required.