How can I produce the SUM of cells from another Table and/or Sheet using as LEN INDIRECT formula?

HI br,

I'm assuming that in this line: "Needed (D64=Day 1::E3 + G4)" the 4 is an error, and should be a 3 to match the pattern of the similar formulas in the body of your text.

As I read your description:

• Row 64 of Checkpoint is to contain the sums of the values in two cells, E3 and G3, on each of the Day n tables, starting with Day 1 in column D of Checkpoint, and continuing to Day 31 in column AH
• Row 65 of Checkpoint is to contain the sums of the values in two cells, E4 and G4, on each of the Day n tables, starting with Day 1 in column D of Checkpoint, and continuing to Day 31 in column AH
• The pattern continues, with the number part of En and Gn incrementing by one in step with the row containing the formula, and the number part of "Day n" incrementing by one in step with the column containing the formula.

Applying that to the formula listed in your post:

"D64=IF(LEN(INDIRECT("Day "&COLUMN()−3&"::e3"))<1,"",INDIRECT("Day "&COLUMN()−3&"::e3")) "

The 'working part' of this formula is the secont INDIRECT statement (in bold). The rest is an IF statement that checks for input in cell E3, and suppresses the calculation if none is found.

COLUMN()-3 is used in the formula above to increment the number part of the table name from which each value is to come.

E3 is a reference to a single cell, and is a fixed text value. As such, it will not increment as the formula is filled down its column, and must be edited in each row. As COLUMN() was used above to increment the table name as the formula was filled right, ROW() may be used to increment the row part of E3 as the formula is filled down. ROW() returns the number of the row contining the formula (row 64), so an offset (-61) is needed to reduce this to the 3 we need in this row. With that change, the 'working part' becomes:

=INDIRECT("Day "&COLUMN()−3&"::E"&ROW()--61)

This would return the value in E3 of the Day n table. Repeating this part with G replacing E, and a + sign separating the two parts will return the total of the values in the two cells:

=INDIRECT("Day "&COLUMN()−3&"::E"&ROW()--61)+INDIRECT("Day "&COLUMN()−3&"::G"&ROW()--61)

This formula will increment automatically as it is filled right or down from D64.

Note that the addition operator expects number values in both cells, and will generate an error message if it finds a non-numerical value in either En or Gn. Empty cells will be evaluated as containing zeros.

Regards,

Barry

PS: The formula should survive the move of Checkpoint to another sheet, provided the table names Day 1, Day 2...Day 31 are each used only once in the document.

Hi br

"I tried using the formula as shown: D64:=INDIRECT("Day "&COLUMN()−3&"::E"&ROW()--61)+INDIRECT("Day "&COLUMN()−3&"::G"&ROW()--61) "

Typo. Remove the second - before 61 (both instances)

D64: =INDIRECT("Day "&COLUMN()−3&"::E"&ROW()-61)+INDIRECT("Day "&COLUMN()−3&"::G"&ROW()-61)

Alternate (see discussion below):

D64: =SUM(INDIRECT("Day "&COLUMN()−3&"::E"&ROW()--61),INDIRECT("Day "&COLUMN()−3&"::G"&ROW()--61))

Last night I tried adjusting the previously mentioned formula: D64=IF(INDIRECT("Day "&COLUMN()−3&"::e3")+INDIRECT("Day "&COLUMN()−3&"::g3")<0,"0",INDIRECT("Day "&COLUMN()−3&"::e3")+INDIRECT("Day "&COLUMN()−3&"::g3"))

I get D64=36 (E3+G3)

Which day? E3+G3 = 12, 24 and 17 on the three tables shown in your examples. C3 + G3 = 36 on the Day 1 table.

When the formula encounters E3 with no value + G3 with a positive value (7) I get D64=error, initially! But when I add a positive number to E3 (3) then D64=10. If I remove the value from E3, leaving the value in G3 (7) then D64=7. Cunfusing!

When the formula encounters E3(no value) + G3 with a negative value (-12) then D64=error (The oereator "+" expects a number, date or duration but found ".". But by following secondary process mentioned above I get the value of D64=0

E3 contains a formula that auto fills the cell. I can edit the formula in E3=IF($B$1>"",IF(C3≥A3÷3,"",A3),"") to show a 0 instead of "" if that makes things simpler. G3 is a manual entry cell and will remain blank unless a value is typed into it. I would prefer to not have to enter 0 into it.

The addition operator ( + ) expects either a pair of numerical values, a pair of duration values or a Date and Time value and a duration. If it gets any other type of value, or a number and either a duration or a Date and Time, it will generate an error message. The formula in E3 returns the number in A3 IF the value in B1 is 'greater than' a null string AND the value in C3 is less than one third the value in A3. IF B1 is empty (or contains a null string), OR if C3 is equal to or greater than one third of A3, the formula returns a null string. A null string is a text value. + can't handle text, and throws the error you see.

There are two ways to solve this.

In D64, instead of 'part 1 + part 2'

Use 'SUM(part 1,part 2)'

Where the parts are the part of the D64 formula before and after the + in your example above.

OR

Replace the "" (both instances) in your formula for E3 with 0

Which to use depends whether you want E3 to hold a zero or to appear empty when no value is calculated for it.

When the formula encounters E3=(12) + G3(-24) D64=0, this should be D64=(-12)

As is should.

D64=IF(the calculation result)<0,"0",the calculation result)

In English: IF the calculation result is negative (less than zero), use zero, otherwise use the calculation result.