Can a formula produced Cell identity ever be used to extract the contents of the cell ?

WHAT do you want to do? Can you provide more context? Numbers does not have a way to move data programatically or "formulaically"... If you want that kind of functionality then Applescript is your friend.

For your questions

1) Your can alter the formula you provided to conditionally place a value in the cell where the formula resides by:

=IF((ROW()-2)>(ADDRESS(ROW(),3,4)),C5,"Unknown")

2) I suggest reviewing the function associated with Reference:

The MATCH function returns the position of a value within a range.

The VLOOKUP function returns a value from a range of columns by using the left column of values to pick a row and a column number to pick a column in that row.

The HLOOKUP function returns a value from a range of rows by using the top row of values to pick a column and a row number to pick a row within that column.

2Skip2 wrote:

1) Does anyone know if the contents of a cell whos address is obtained with ' ADDRESS(ROW(),3,4) ' can be placed in another cell ?

I have only been able to use it as a reference such as:- =IF((ROW()-2)>(ADDRESS(ROW(),3,4)),"Y","N") This uses the content of column C on the same row as the function, and compares to a row number 2 rows further up; (ROW()-2) but you can't seem to obtain or place the content in another cell. like you can with =C5.

...

Yes, the result of the ADDRESS function is a text string, not an address variable. To convert to the address data type, use the INDIRECT function, like this:

Regards,

Jerry

2Skip2 wrote:

...

2) Does anyone know how to locate the row/cell in a column range, say, D2:D8 that is empty or has a specified value ?

...

Skip,

I have found that a very good way to test for an empty range is to sum the character lengths for the range and look for a zero result.

This can be done by having an auxiliary column that calculates the length of the content. For example, you could add a Column E with the expression =LEN(D)

Columns with the same expression in each and every body row will repeat the expression in any added row, so the "=LEN(D)" formula will be present in every added row. The column with the calculation may be hidden.

Jerry

Are you looking for the INDIRECT function?

=INDIRECT("C5") will give the same answer as =C5

Skip,

You may not yet fully appreciate the significance of Header and Footer Rows and Columns. Some careful reading of the User Guide may clear that up for you.

If you use true Headers, titles in your headers won't count in terms of calculations on entire columns or rows, and in this particular case, won't count in terms of all cells in a particular column having to be exactly the same in order to replicate a formula when rows are added.

Jerry

Hi Skip,

Ithink you may have hit the nail on the head with "My problem is I am too impatient."

Earlier you wrote:

"What I want to do is locate the row, tag it onto the known column ID "C" and then copy the content of that cell into another cell. What I can't get the software to do is put the "C" together with a figure derived by formula and then get the software to recognise it as a cell reference."

And continued:

"I can copy the content of say D6 into another cell using =D6.

"If I put together "D" and a number derived by software to get D6 it will not work even though it looks the same. I can get a lagitimate D6 but I think it is all a string, not a string+ number."

'I even tried CONCATINATION but it wont accept a figure. It seem to thwart all attepts. most annoying."

Badunit replied:

"=INDIRECT("C5") will give the same answer as =C5"

To which you replied:

"Appreciate you taking the time to answer but no, it was the software composed "C5" that was giving the problem. You don't seem to be able to use it if used indirectly. sorry no connection with INDIRECT, intended."

Breaking that down, using the C5 example:

You have the text string "C"

You have, using a formula, determined the row number 5.

You want to get the content of cell C5

Use CONCATENATE to build the text string "C5"

Use that text string "C5" as the argument for INDIRECT

Your formula will be =INDIRECT(CONCATENATE("C",sum(2,3)))

with 'sum(2,3)' replaced by the formula you are using to determine the row number needed for the cell reference.

Example follows.

Regards,

Barry

Example:

Regards,

Barry

2Skip2 wrote:

I've not yet got to the next part which is to write something in that empty cell.

When a new row is added, one of two things will happen.

1. If a column in your table has the exact same formula in every row, that formula will be copied to the corresponding cell of a newly added row. By "exact same formula", I mean either it is exactly the same characters such as =3*2 or it must be a formula that fills down such as =B (which will fill in as =B in the new row) or, as a very simple example, must be like =B2, =B3, =B4 etc (which would fill in as =Bwhatever if a new row is added at the bottom). The contents of headers and footers are not included in the requirement for "every row" to have the same formula but ALL of the other rows must have the formula. You should experiment with a new table to see how this works.
2. If every row in the column does not have the same formula, the corresponding cell in the newly added row will be blank.

There are no formulas that will write something to another cell. Spreadsheets don't work that way. Each cell determines its own contents. For a cell in a new row to have something in it, you need to have what I described above in #1.

Sometimes the requirement for every row to have the exact same formula can only be satisfied by using INDIRECT or OFFSET. An example is the balance column in the checking register template.

HI Skip,

"Returning to my original question, nowhere in the relivant part of the manual could I find words that explain the peculiarity of string to not all string involved here."

Possibly it's just familiarity, but i don't see such a 'peculiarity' arising here. Can you explain what you see as a peculiarity?

"To complete the scinario I would like to be able to place a value say "sheep" in your example into a remote cell say F8, from where you are in I2 ?"

A formula in I2 can 'place' a value only in cell I2. No formula can 'push' it's value into another cell.

You could place a formula in F8 which would 'pull' the value from I2: =I2,

or you could place a formula in F8 that would determine the value in the same manner as the formula in I2: =INDIRECT(CONCATENATE("C",H2)).

Either way, the final formula, to place a value in F8, must itself be in F8.

Regards,

Barry