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

R

Robert

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
 
C

Carlo

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
 
R

Robert

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
 
R

Robert

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

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.
 
R

Robert

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
 
R

Robert

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
 
R

Robert

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

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compare and sum 1
Finding the weighted average of text that have been given values 1
creating a form in 2007 that picks from a four column table 3
Merging from an Excel file with graphics 1
adjusting sums 6
input value from weight scales 1
return a value depending on two previous values 1
Construct table from selected rows 4

Top