Autofilter showing particular cell (Excel 97)

Topic

New Reply

Hi all,
I am stuck at the following problem. I have a small table (pls. see attachment) and below it another listing of brands and in the next column numbers, of which I made an autofilter. The result of the autofilter will be always one row, and what I want to achieve is that the number (in column which is visible from the autofilter to be shown (linked or otherwise) to the cell B19 (shown in yellow) in the table above.
I cannot link the number to the table because every time there can be a different row that is visible, depending on what has been selected in the filter.
Could anyone help me get this result, as I have no idea what method to use for it.

Thanks a lot,
K.

Reply | Quote

Replies

I have inserted a formula into B19 which either takes the filtered value, or if the filter is not in place takes the first value (B29). File attached.

Does that give you what you need ?.

Andrew C

Reply | Quote

Thank you very much, it is EXACTLY what I wanted.

Cheers,
K.

Reply | Quote

An excellent reminder that the Subtotal function ignores hidden rows. Subtotal is such a useful function; no wonder Microsoft made it so cryptic. I would prefer the following similar formula for B19

=IF(SUBTOTAL(2,B29:B38)=1,SUBTOTAL(9,B29:B38),"No Chain")

Reply | Quote

Well that unfortunately gives some nasty looking VALUE# errors in all the other cells in the table.

Andrew C

Reply | Quote