If each weekly sheet contains this same pair of tables, and each copy of COMPUTATIONS collects data from only the copy of WEEKLY SUMMARY on its own Sheet, then there's no need for the fancy footwork of INDIRECT. Your initial screen shot shows cell G2 on a table named COMPUTATIONS selected, and a formula below it referencing cell T3 on a table named Weekly Summary.Īre these two tables on different sheets, or are they both on the same sheet? My response above assumed they were on different sheets, and that the formula needed to collect information from a table with the same name but on a different sheet for each week. I think there's confusion at both ends of the conversation. Note: The -1 adjusts the ROW number to the WEEK number.
It is an "invalid reference" error" due to the formula attempting to collect a value from cell T3 of the table Weekly Summary on the sheet Week 15, a sheet that does not yet exist in my document. Is converted by INDIRECT from a text string to an address readable by Numbers.Īs the formula is filled down, the ROW()-1 result increases by 1 for each row, but the text strings remain the same, and the address points to the same cell on the same table on the next week s Sheet. The text string "Week ", the number 12, and the text string "::Weekly Summary::T3", and the result, In cell G13, ROW()-1 returns the number 12. The same formula, using INDIRECT to construct the addresses for cell T3 each week, is shown below the basic version.īoth iterations of INDIRECT create the same cell address: That formula, revised to use Week 12, is in cell G2 of the example table below, and shown in the single line Formula Editor below the table. So your formula, written out in full, would be:
FIND INVALID REFERENCE IN EXCEL FOR MAC 2017 FULL
The full address of that cell in the table on the sheet named Week 12 is: The target cell in the formula presented is T3. On each of these sheets, the target cells are on a table named Weekly Summary. Your data tables are on Sheets with the names Week 1, Week 2, Week 3, etcetera. To tell Numbers which cell(s) to get the information from, you need to supply the full address of each cell or range of cells. The Table has the same name as its counterpart on each of the 17 sheets in the document.
Your formula is pulling data from a table on a different sheet.
0 kommentar(er)
