fill series in filter mode (excel 97)

Topic

New Reply

In the filter mode, how can I fill series on the visible cell only.
for example, row 2 and row 3 is filtered, and put 210 on c1, try to increment by 1 to 219 on the visible cells only.
thanks

Reply | Quote

Replies

– Filter the list
Enter this formula next to it:
=SUBTOTAL(3,A2:$A$2)+209
Copy down.
Select the formula’s, copy, paste-special, values.

Reply | Quote

Pieterse
thank you for the help.
I am still struggling with this formula.
1) when I return to the unfiler mode the value will change. I tried to copy and pastspecial value only in filter mode, It won’t work.
2) I want to insert a name for that formula (something like Xfill=subtotal(3,a2:$a$2) and I sometimes need to put this starting from c10 or some other cell, then the formula will not work right. Any suggestion will be appreciated.

Reply | Quote

Hi,

<>

It worked for me!
Was my description unclear?

<>

Assuming the original formula does work when used from cell C10 and on…

Make sure you’re on cel A2 when you define the name, otherwise the A2 without dollar signs will be adjusted *relative* to the cell you are on during the definition.

Example: If you define this name : OneLeft, refersto: =A1 Whilst on cell B1 and you use the name in cell S1 (=OneLeft), the name will refer to cell R1 (one column to the left).

Reply | Quote