automatic table# change when dragging formula

Question

Hi, I've created a table with many different countif, countifs, and sumifs equations to pull data that my team inputs from different sheets where they work, to count up the total efforts/results of the team.

For example, here's an equation I use to count up completed solicitations when these 2 results are found: =COUNTIFS(Table11[PG Solicitation Increase Ask?],">a", Table11[MGQ Meeting Complete?],">a").

I have 12 different tables to pull from to find that data for each team member. In order to get that from the next person, I have to copy/paste the formula, then change the table numbers. For instance, the next formula looks like this: =COUNTIFS(Table12[PG Solicitation Increase Ask?],">a", Table12[MGQ Meeting Complete?],">a")

Is there any way to change the formula so that everything stays the same except that Table number, which would automatically change when I drag down the column? I know that if I drag across the row, the whole formula changes, so I'm doing that. But when I drag the formula down the column, it just duplicates the formula and then I have to go change all the individual table numbers.

Thanks!

Answer 1

Let's say the formula for Table11 is in row14. You could use

=COUNTIFS(INDIRECT("Table"&ROW()-3&"[PG Solicitation Increase Ask?]"), ">a", INDIRECT("Table&ROW()-3&[MGQ Meeting Complete?]"), ">a")

Adjust the -3 if the formula is in another row.

You should then be able to fill it down.