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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <Robert@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DD5E1656-AC75-4D9C-9009-F26FD8AACAF2@xxxxxxxxxxxxxxxx
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

"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" <Robert@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F4818174-AAF7-4174-9108-4AAF6C22673B@xxxxxxxxxxxxxxxx
Thanks.
I've done that and the file is here:
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" <Robert@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:55C245E0-30B6-4585-BA08-B87F9B0FFC20@xxxxxxxxxxxxxxxx
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








.

Relevant Pages

  • Re: Export chart to GIF no longer works after installing Excel 200
    ... PNG and GIF were working but TIF was not. ... Jon Peltier, Microsoft Excel MVP ... I ultimately copied a filter from another computer into this directory, ...
    (microsoft.public.excel.charting)
  • Re: AutoFilter
    ... .Weight = xlMedium ... .ColorIndex = xlAutomatic ... I need to just select the filter data only. ...
    (microsoft.public.excel.programming)
  • Re: Using Filter string with IF function
    ... Microsoft Excel MVP ... When no filter is set the formula should show a BLANK cell or Display ... Array formulas are entered differently than a regular formula. ...
    (microsoft.public.excel)
  • Re: How to sumproduct only filtered data
    ... Microsoft Excel MVP ... Valko" skrev: ... Assume you filter on column A = West ... "Sheeloo" skrev: ...
    (microsoft.public.excel.misc)
  • Re: How to sumproduct only filtered data
    ... Microsoft Excel MVP ... Valko" wrote: ... Assume you filter on column A = West ... formular also includes the non-visible rows. ...
    (microsoft.public.excel.misc)