Returning a Value from a Table, dependant on the word Typed

[Guest]

Hi
I'm trying return the weight of an item into an adjacent cell, when I type
the word.
To Explain: If I have A1 and B1 as Item and Weight, then underneath those I
have Sofa, Chair, Bed etc, with the relevant weights in the B Column.

Next I have another Worksheet or the Same Worksheet, where I have Name, Item
and Weight. I want to insert automatically the Weight based on typing the
right Item (i.e. Sofa) in the cell next to the persons name.

Hope that makes sense. Can't seem to upload a sample. I may have about 100
items to access along with their weights.
Thanks

 

[T. Valko]

For one way, see this screencap:

http://img530.imageshack.us/img530/2452/sumifer2.jpg

 

[Guest]

look in the help for vlookup, that's the formula that does exactly what you
want.
If you need help in applying this formula, just ask.

hth

Carlo

 

[Guest]

Hi
I did think that VLOOKUP would be the answer, but trying as I have done, I
always get #VALUE.
I made a small example of it, and if I could send it anywhere, you would see
where I'm going wrong. Is that possible.
Robert

 

[Guest]

Thanks a lot but I tried that SUMIF Function on the basis of what you showed,
but I only get #NAME?

Nice try, but I still have this problem.!!!
Sorry to be a pain, but I did check the Syntax Very carefully and no joy I'm
afraid.

 

[T. Valko]

You can upload small files here for free:

http://tinyurl.com/24xfnt

This is a French site translated into English. The files will remain active
for about 3 weeks. After you upload the file you'll get a link to that file
then you can post the link for anyone wanting to look at the file. I post
sample files like this quite often.

 

[Peo Sjoblom]

Are you using a English language version of Excel?


--


Regards,


Peo Sjoblom

 

[Guest]

No
I'm using Star Office for this.
Would that be any different.
I'll try it in excel as well and let you know.

Thanks

 

[Guest]

Thanks.
I've done that and the file is here:
http://cjoint.com/?kFuFfycjWl

Robert

 

[T. Valko]

Here's a screencap of the file you posted with the formulas entered. As you
can see, the formula I suggested does return the correct results. I made a
slight tweak to the formula to account for empty cells like B3.

http://img219.imageshack.us/img219/1644/sumif2ok8.jpg

I entered the formula in cell C3 then drag copied down to cell C5.

 

[Guest]

Ok...here is an actual file I'm working with now, given your forumula, which
did work, so thanks for that. However now in the file that is attached at:
http://cjoint.com/?lbqLmGZyyh Why am I getting Zero Returned in the Sheet
Called Beko at the Cell Ref: G2 and the rest of that column.

Thanks.
Robert

 

[Guest]

Thanks again, as I've now realised I had one wrong Cell Reference. What a
Bad Boy I am. Seriously, this has helped me a lot and I want to thank you
for your help.
May I ask one further question on this though..please..!!

I've copied up the revised version with that Formula Fixed. ITs Here:
http://cjoint.com/?lbsOyZZRYw
My Question is again relating to the Worksheet contained in the file, which
is Entitled Beko. At the bottom I've put in a calculation for The Total
Units and Tonnage, but I want to know whether its possible to get it to
change the result of the calculation, whenever the Filtering is altered. For
Example I may change the Date to Filter on, but I would then want to see the
Totals Change also. At the moment, it doesn't alter it but actually hides
it, and even if I have hidden it through filtering, its still doesn't JUST
calcuate on what I've filtered.

Hope that makes sense.
Many Many thanks
Robert

 

[T. Valko]

Insert 2 or 3 rows at the very top of the sheet and put the formulas *above*
the filter. That way they're *always* visible.

Use the SUBTOTAL function to do calculations on filtered data. See Excel
help on the SUBTOTAL function to see all the calculations that it can do.

Here's the formulas you should use. I inserted 2 new rows at the top so the
filtered data starts on row 4.

=SUBTOTAL(9,B4:B218)
=SUBTOTAL(9,G4:G218)