Who can help a beginner?

[PAtesting]

Hello all,

See screenshot below:

I have a =COUNTA and a =COUNTIF formula (in this example they are in Dutch), which range for both starts in the table above (G2). This table is automated and has AutoFill to fill in the cells. However, when it does, the range in the aforementioned formula automatically switches to G3..G4...G5 and so on. It doesn't stay locked on G2:G-), which means that it keeps the formula from working. See second screenshot:

see formula range, ''G3'' which is outside of the table

see formula range ''G3'', which is outside of the table.

Anyone knows what to do? Hope my problem is clear enough.

Thanks

[#2]

@PAtesting You are working with structured tables, so it's better not to use direct cell references. Rather than writing G2:G9998, hover the pointer over the column header (i.e. the blue cell in column G) until you see a thick black arrow pointing downwards. Click, and a structured reference to the entire column in the table will be created. This reference will automatically expand when you add new rows to the table.

Otherwise, Google for structured Excel tables and you'll find plenty of tutorials how to handle them.

[#3 · Accepted solution]

@PAtesting 

There is a special notation that is used to refer to areas of an Excel Table.  For a complete column the table name comes first then the column name (taken from the header) in square brackets.  The cells referenced by such notation extend as rows are added to the table, for example

= TABLE1[AFGEHANDELD_BINNEN_DEADLINE]