How can I use the indirect function to increment the cell as well

Hi Sam,

Numbers will increment the row part of a cell reference as a formula is filled down a column, but it will not increment text expressions. Replace the "3" in your formula with a function( ROW() comes to mind ) that will return the number 3 in the current copy, and the number 4 in the next copy, one row down.

For a cell in row 7 (for example) that you want to reference cell E3 on the table March Balance, the following should do what you want. I've put it in K7 on a table that is not named "March Balance".

K7: INDIRECT ($B$54 &"::E"&ROW()-4)

Tested version (using smaller tables)

The formula shown below the March Balance table is in the selected cell of the second table, and is filled down from A7 to the end of the column (A16). The error triangles flag this error: "The formula contains an invalid reference." as the ROW function will return 15 in the first flagged row, -4 will convert that to 11, and the formula will attempt to retrieve a value from cell E11 of March Balance. This is an invalid reference as March balance does not include a "row 11."

Regards,

Barry

Hi Sam,

You wrote:

"Like I stated in the initial post is like this:

The formula shows INDIRECT ($B$54 &"::E3") ---This should refer to the month of March sheet

The cell $B$54 = MARCH-BALANCE -- This is the table name in the sheet of March (or April or the next month as the sheet progresses it will change at the $B$54 change the month and so the INDIRECT cell "I __" )"

I take this to mean:

• Your document has a Sheet two Sheets for each month of the year. Each sheet is named with: the name of its month, the last two digits of the year, and either "EXP" or "SUMM", separated by hyphens.
• One of the two sheets for each month contains a table named "month-BALANCE", where "month" is the name of the month that is part of the Sheet name of the sheet containing that table.
• You want to copy the value in cell E3 of the table named MARCH-BALANCE to an unspecified cell on and unspecified table on one of the April sheets.
• (from your initial post) in the cell below the one receiving the value from MARCH-BALANCE::E3, you want to get a copy of the value in MARCH-BALANCE::E4, and in the cell below that one, a copy of the value in MARCH-BALANCE::E5.
• Cell B54 on the (April) table that is to receive the copy contains the text "MARCH-BALANCE"
  Cell B54 on the (May) table that is to receive the copy contains the text "APRIL-BALANCE"
  Cell B54 on the (June) table that is to receive the copy contains the text "MAY-BALANCE"
  etc.

This is pretty much exactly what my example does.

The smaller table represents the 'april' table that is to receive the values from column E of the table MARCH BALANCE (I omitted the hyphen, which will need to be included in the data in B54 on your table to match your march table name).

B1 represents your B54.

K7 represents the cell that is to get the data from E3 of MARCH BALANCE.

MARCH BALANCE represents your MARCH-BALANCE table, with only the target cells (E3 and below) filled in.

"I tried the ROW function that you recommended but that didn't work when is a reference sheet."

Clarify, please.

What was you actual formula?

In what way did ROW() (or the formula) "not work"?

What did the user do? What response did the computer make?

"that didn't work when is a reference sheet."

What does this mean?

"I typed INDIRECT(B$54,&"::I"&ROW()-59) but is calling the local sheet (APRIL) when what I need is to go back to MARCH in this case.

The formula above will create a cell reference to contentofB54::In where contentofB54 is the text in cell B54, and n is the result of the number of the row containing the formula minus 59.

If B54 contains "MARCH-BALANCE" and the formula is in a cell in row 62, the cell reference constructed will be to

MARCH-BALANCE::I3 and the cell should display 81.39, the value in I3 of MARCH-BALANCE, as shown in the screen shot in your second post in this thread.

"I also tried to type directly at the cell and start typing with apostrophes but it didn't let me continue when I tried the colon

I don't know why?"

Were you typing "in the cell" or had you typed = to open the Formula Editor, then typed in the Formula Editor.

What do you mean by "started typing with apostrophes"?

Typing a single apostrophe as the first character of an entry in a cell signals Numbers "what follows is to be formatted as text".

One useful place for this is US ZiP codes (postal codes), several of which start with zeros.

Entering 01527 is recognized as a number and displayed as 1527

Entering '01527 is recognized as text and displayed as 01527

In formulas, quotation marks are used to mark the start and end of "literal strings" (text values).

Regards,

Barry

Hi Sam,

"K7" is a cell picked at random for the location of the formula. The 7 is significant, as it determines the value returned by ROW() in the formula, which affects the constant value (-4) to be added to that value to reach the target row (3) of the cell referenced by the formula.

The reference to B$1 is to the cell containing the name of the source table. In your initial post, $B$54:

"The cell $B$54 = MARCH-BALANCE -- This is the table name in sheet of March"

I've applied the absolute reference operator ( $ ) to the only the row. The formula will be filled down a column, but not left or right on a row, so there's no need to "preserve column".

I'm working with two tables on the same sheet, but the formulas will be the same if the tables are on different sheets, provided each table has a distinct name.

A complete cell address has three parts:

sheetname::tablename::columnandrow

Example:

Sheet 1::Table 1::A1 is the full address of the top left cell on Table 1 on Sheet 1.

If the formula referencing the cell is on the same table, only the columnandrow part is needed.

A1

If the formula referencing the cell is on a different table on the same sheet, the tablename and columnandrow parts are needed.

Table 1::A1

If the formula referencing the cell is on a different table on a different sheet, all three parts will be needed,

Sheet 1::Table 1::A1

UNLESS the referenced table has a name different from all other tables in the document.

If that is true, the sheet name may be omitted (and will be discarded by Numbers if it has been included in composing the formula)

Nameoftable::A1

Regarding your screen shots:

Please include row and column reference tabs on screen shots of tables, and an indication of which cells are significant. Also show the names of tables where this is significant.

Regards,

Barry

Notes:

APRIL-18-SUMM::G55: You have this formula: CONCATENATE("'"+A53+"−"+"18−SUMM")

+ is the addition operator, used to add two numbers.

The syntax for CONCATENATE, used to join two or more text strings into one, is:

CONCATENATE(string, string…)

string: Any value.

string…: Optionally include one or more additional values or cell references.

Note that the values inside the parentheses are separated by commas, not + signs.

In this cell you are trying to concatenate: the text ',the content of cell A53,the text -,and the text 18-SUMM

The apostrophe/single quote is a 'text flag', used only when directly entering a numeric value or quasi-numeric value (eg. date and time) that you want treated as text. It is unnecessary here for two reasons: 1. The entry is being generated by a formula, not entered directly, and 2. CONCATENATE treats everything as text, and produces a text string.

There is no need to separate the hyphen after A53 from the rest of the text that is to follow the month name.

Making these changes to the formula would provide the text string to use in retrieving data from the March summary table IF A53 contained the text MARCH (with no apostrophes or single quotes) AND that table's name was MARCH-18-SUMM.

But that name belongs to the sheet containing the table, not the table itself. The table's

name is MARCH-BALANCE

The syntax for a cell reference to a cell on a distinctly named table other than the table containing the referring formula (discussed in one of my posts yesterday) is: tablename::cell

Revised formula for APRIL-18-SUMM::G55: CONCATENATE(A53,"-BALANCE")

A53 must contain the text MARCH

OR, considering the difference in effort required, you might find it more efficient to dump the formula altogether and just enter the text string that would result from this formula ( MARCH-BALANCE ) directly into G55 (or a different out-of-the-way cell) on each on the monthly balance tables.

See separate post regarding using this value with INDIRECT to copy the values in column I of MARCH-BALANCE into the same rows of column G of APRIL-BALANCE.

Regards,

Barry

Moving "Accumulated balances" from column I of the previous month's -BALANCE table to the same rows on column G of the current month's -BALANCE table.

Example uses APRIL-BALANCE as the current month, and MARCH-BALANCE as the previous month.

Cell G55 should contain the text MARCH-BALANCE

This may be entered directly in that cell, or generated by the formula discussed in the post above. The text must be an exact match to the name of the table from which the data is to be gathered.

The tables contain data only in the cells involved in the formula, and is compacted horizontally by reducing the width of empty cells and vertically by hiding several rows. The same rows are hidden in both tables. Row numbers and Column letters may be seen in the reference tabs above and to the left of the tables,

Regards,

Barry