Add indirect formula to move tabs

Hi Beh,

Try replacing 'Week 7::Weekly Summary::$N$24:$N$39,C$1' in the current formula in B9 with

'INDIRECT("Week "&ROW()-2&"::Weekly Summary::$N$24:$N$39,C$1")'

Don't include the single quotes (used here to mark of what is being replaced and what is to replace it).

Enter in B9, then fill down for as many rows as needed.

Regards,

Barry

Leave what's above in B9 for now.

Put this part of the formula into B10:

B10: COUNTIF(INDIRECT("Week "&ROW(cell)−2&"::Weekly Summary::$N$24:$N$39,C$1",addr-style),C$1)

Does that give a correct count for Week 8?

Does it give an invalid reference error?

Does it give a different error message?

Regards,

Barry

The text (without any tokens) indicates Numbers isn't recognizing it as a 'proper' formula.

The issue appears to be that the optional arguments have been inserted by Numbers, appear as text when they are copied as part of the formula, and, as text, prevent the string from being recognozed as a formula.

For the partial version, paste it into the formula editor, then delete the parts in bold below:

B10: COUNTIF(INDIRECT("Week "&ROW(cell)−2&"::Weekly Summary::$N$24:$N$39,C$1",addr-style),C$1)

in the full version, delete the same parts in bold below both times they appear:

IF(COUNTIF(INDIRECT("Week "&ROW(cell)−2&"::Weekly Summary::$N$24:$N$39,C$1",addr-style),C$1)=0,"",COUNTIF(INDIRECT("Week "&ROW(cell)−2&"::Weekly Summary::$N$24:$N$39,C$1",addr-style),C$1))

Regards,

Barry

Unless you have a sheet named Week -1 containing a table named Weekly Summary, the formula above will give you a REF# error (unless it returns a differen error).

Screen shots, please:

1. The main table showing the formula you have in B9, and with the formula editor moved off cell B9 so that any result in that cell is visible.
   
2. The Weekly Summary table on the sheet (tab) referenced by the formula in the cell named above (B9), showing all of the details listed:
   The Tab for the sheet containing this table.
   The row and column headers of the table. (clicking on any cell in the table will make these show)
   The name of the table as it appears above the table.
   All Header rows and Header columns of the table. If there are no Header rows or no header columns, show at least row 1 and column A at the top and left of the table.
   Hide as many rows as necessary and as many columns as necessary to fit Cells N24 to N31 of the table into the screen shot.
   
3. The Weekly Summary table for the following week, with the same details shown as listed above.

Add copies of the formula as is written in B9 and in B10.

These should be text copies, not screen shots.

Click the cell to open the Formula Editor

Press command-A to select All

Press command-C to copy.

Click in your post and go Edit (menu) > Paste and match style.

Repeat for cell B9, placing the second formula directly below the first.

If the formulas are presenting an error triangle,

Click once on the triangle to show the error message.

Select the whole message.

Copy.

Paste in your message below the two formulas.

If the second formula is presenting a different error message, repeat for that cell.

Regards,

Barry

My bad—not reading closely enough.

Current formula in B3:

IF(COUNTIF(INDIRECT("Week "&ROW()−2&"::Weekly Summary::$N$24:$N$39,C$1"),C$1)=0,"",COUNTIF(INDIRECT("Week "&ROW()−2&"::Weekly Summary::$N$24:$N$39,C$1"),C$1))

COUNTIF part:

COUNTIF(

INDIRECT("Week "&ROW()−2&"::Weekly Summary::$N$24:$N$39,C$1") --range

,C$1) --condition

C$1 is the cell containing the condition (value to be matched), and not part of the range of cells to be searched for a match.

INDIRECT builds the range from the parts that are inside the parentheses in the 'range' line above. The invalid reference error comes from including ',C$1' inside the parentheses (and inside the quotes), something I hadn't noticed on earlier readings.

Corrected formula for B3:

IF(COUNTIF(INDIRECT("Week "&ROW()−2&"::Weekly Summary::$N$24:$N$39"),C$1)=0,"",COUNTIF(INDIRECT("Week "&ROW()−2&"::Weekly Summary::$N$24:$N$39"),C$1))

Thanks for the screen shots and views of the formula both as text and in the formula editor. Needed all to track the formula and discover the error.

Another thing that will require attention is that you alternate the names of the city and the team in row 1 of the table on the win-loss landscape sheet, but use only the team names in the win and loss columns of the weekly summary tables.

The formulas in columns B and C will both need to reference C$1 for the condition of COUNTIF.

Regards,

Barry