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

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

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

For one way, see this screencap:

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

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
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

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

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

"Robert" wrote:

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

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

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

"Carlo" wrote:

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

"Robert" wrote:

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

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

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" wrote:

For one way, see this screencap:

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

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
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

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

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.

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
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

"Carlo" wrote:

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

"Robert" wrote:

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

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

Are you using a English language version of Excel?


--


Regards,


Peo Sjoblom


"Robert" wrote in message
...
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" wrote:

For one way, see this screencap:

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

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
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

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

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

"Peo Sjoblom" wrote:

Are you using a English language version of Excel?


--


Regards,


Peo Sjoblom


"Robert" wrote in message
...
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" wrote:

For one way, see this screencap:

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

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
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

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

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

Robert

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
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

"Carlo" wrote:

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

"Robert" wrote:

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

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

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.

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
Thanks.
I've done that and the file is he
http://cjoint.com/?kFuFfycjWl

Robert

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
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

"Carlo" wrote:

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

"Robert" wrote:

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

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

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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
Thanks.
I've done that and the file is he
http://cjoint.com/?kFuFfycjWl

Robert

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
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

"Carlo" wrote:

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

"Robert" wrote:

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

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

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 He
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

"Robert" wrote:

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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
Thanks.
I've done that and the file is he
http://cjoint.com/?kFuFfycjWl

Robert

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
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

"Carlo" wrote:

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

"Robert" wrote:

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

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

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)

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
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 He
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

"Robert" wrote:

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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
Thanks.
I've done that and the file is he
http://cjoint.com/?kFuFfycjWl

Robert

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
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

"Carlo" wrote:

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

"Robert" wrote:

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