Respective names required [Closed]

Report
-
Mazzaropi
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
-
Hello CCM,

Please give me solutions to solve my problem..

1) I have created 2 worksheets,

one sheet having two columns like below

Code member name
19401 xxxxxxxxxx
19402 xxxxxxxxxx
.
.
.
19415 xxxxxxxxx

The second sheet having below columns like below.

Code key type1 type2 type3 type4 member name

19415 xx xx xx xx xx xxxxxxxxxxxxxxxx
19407 xx xx xx xx xx xxxxxxxxxxxxxxxxx

My question is that:

if i enter code number code column in sheet 2, automatically the name of the member in sheet 1 of member column will appear in the sheet 2 of member name column.


Thanks,

Regards,
Ganesh

6 replies

Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
135
Ganesh, Good Morning.

The VLOOKUP Function was specially created for this case.

Sheet 1
Code........... --> A1:A100
Member name --> B1:B100

Sheet 2
Code --> A1:A50
Key.. --> B1:B50
Type1 -> C1:C50
Type2 -> D1:D50
Type3 -> E1:E50
Type4 -> F1:F50
Member name --> G1:G50 --> FORMULA

Try to use:
G1 --> =VLOOKUP(A1,'Sheet 1'!A1:B100,2,FALSE)

Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Hello Belo Horizonte, Brasil.
Marcílio Lobão,

Thank you sir.. the problem is resolved..

One more doubt...?

Column 1 column 2
BM 4.30
CM 5.30
BM 6.03
BM 4.50
CM 9.10
.
.
.
BM 2.81

My Question : how to calculate the BM values sum only?
Give me formula ?

Kind Regards,
Ganesh
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
135
Ganesh, Good Morning.

The SUMIF Function will work for you.

Code --> A1:A100
Value --> B1:B100

Try to use:
D1 --> =SUMIF(A1:A100,"BM",B1:B100)

Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Hello Sir,

This is Ganesh. .

I have one more doubt..?

Please click below link.

https://www.dropbox.com/s/1j2ieeyc5jxqq28/Sample.xlsx?dl=0

How i have two columns fat,snf one sheet(1001-bm) . i have another sheet(buffalo) that contains fat and snf values i want give lookup or meet the coincide value.

So please give a formula .

Thanks,

Ganesh
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
135
Ganesh, Good Morning.

The VLOOKUP Function will be useful for you in this case.

Your necessity is not clear.

Do you want a Exact Cross Reference data using Colunm B for FAT and Line 2 for SNF ?

How did you find a 45,6 result presented in your example?
I think the exact result must be 43,8.

For you, what is the result for FAT=5,40 and SNF=8,12 ?

Please, tell us how you find the desired result?
Hello Sir,

Thank you for prompt reply.

as you said, I want a Exact Cross Reference data using Column B for FAT and Line 2 for SNF ?

Yes. as per my example,the result is 43.80

Also i want the results like as below

For example

FAT SNF Value

5.00 7.40 27.80

5.00 7.41 27.82

5.00 7.42 27.84

.
.
.
5.00 7.50 28.00

I Mean if we incease SNF value to 0.01,it automatically the rate 0.02 increased

So please give me vlookup function formula .

.

Thanks

Ganesh
Hello Mazzaropi,

as you said, I want a Exact Cross Reference data using Column B for FAT and Line 2 for SNF ?

Yes. as per my example,the result is 43.80

Also i want the results like as below

For example

FAT SNF Value

5.00 7.40 27.80

5.00 7.41 27.82

5.00 7.42 27.84

.
.
.
5.00 7.50 28.00

I Mean if we incease SNF value to 0.01,it automatically the rate 0.02 increased

So please give me vlookup function formula .

.

Thanks

Ganesh
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
135
Ganesh, Good evening.

We asked you if you want a Exact Cross Reference data using Colunm B for FAT and Line 2 for SNF.
And you said: YES.
But reading your new explanation it's not corret.
You need a more complex search than a simple Cross Reference.

I'm not sure if I understood well your necessity.
Take a look at this example that a included a formula for you.
https://www.sendspace.com/file/xqiauj

Do many tests to certify the formula is giving you a CORRECT result.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Hello there,

first of all, thank you so much for your help.I ever grateful to you.

I have one more error in my file..please click on the below link.

when i want to transfer data from Main database to other sheets, the values in the columns "Rate/kg & amount" is changed to 01.4,0.00,0.12..etc

So Can you look at this file & give me solution.

Also i want to maintain a every day data in a separate excel sheet. Can you please give me an idea & solution.


https://www.dropbox.com/s/ybdjtqu7o74f0ac/sample%202.xlsm?dl=0


Thanks ,

Your's
Ganesh
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
135
Ganesh, Good morning.

I don't know Excel VBA programming.

"...the values in the columns "Rate/kg & amount" is changed to 01.4, 0.00, 0.12 ..etc ..."

When your macro runs it is erasing part of the data of the two tabs: Buffalo and Cow.
So the search is all wrong.
The problem is in the Macro.

In tab Buffalo, column B line 18 to 53, the values have several decimal places.
As the cell is formatted to show only two decimal places you can see 6.50 but actually is 6.49999999999999.
This will make the search fails when you search this value.
I advise you to correct these numbers to prevent future problems.

"...Also i want to maintain a every day data in a separate excel sheet. Can you please give me an idea & solution. ..."

It may be helpful for you to have the spreadsheet with daily data in a file and another file with data separated by codes.
Thus, using VBA you can update them whenever necessary.
As we do not know the logistics of your business it is difficult to speak with precision the best solution.

Checking more carefully your spreadsheet, we have noticed that you use different formulas for each type of milk.

Do you enter the formulas manually for each type of milk found?
If the answer is YES, this is not necessary.
It is hard work and time spent typing.

Make Excel work for you.
The automation serves this purpose.
Besides being much faster and much more secure and professional.

Try to use:
Main Database --> N6

=IF(F6="BM",INDEX(Buffalo!$C$3:$X$53,MATCH(J6,Buffalo!$B$3:$B$53,1),MATCH(K6,Buffalo!$C$2:$X$2,1)),IF(F6="CM",INDEX(Cow!$C$3:$W$28,MATCH(J6,Cow!$B$3:$B$28,1),MATCH(K6,Cow!$C$2:$W$2,1)),"")) + MOD(K6,0.1)*2

Copy it down as necessary.

I hope it helps.
Have a nice weekend.
--
Belo Horizonte, Brasil.
Marcílio Lobão