1 2 Previous Next 26 Replies Latest reply: Jun 1, 2012 2:35 PM by Jerrold Green1
2Skip2 Level 1 Level 1 (0 points)

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.

 

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 ?

 

 

The idea is to determin    1) if a a row has been added and, because any cells in that row will be empty,   2)  locate the row.

I realise that both solutions are not necessary for the obove but, they would be useful for other uses if they can be done.


iMac, Mac OS X (10.6.8), 4Gb memory
  • 1. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
    Wayne Contello Level 6 Level 6 (13,620 points)

    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:

    Screen Shot 2012-05-30 at 5.24.37 PM.png

     

    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.

  • 2. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    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:

    Screen Shot 2012-05-30 at 7.34.19 pm.png

     

    Regards,

     

    Jerry

  • 3. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    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

  • 4. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
    2Skip2 Level 1 Level 1 (0 points)

    Hi Wayne,

    Bit tied up right now but will try what you say.  From initial reading, =C5 was just an example row. That is the part I have to find. I know the column "C".    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.

    eg;

    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.

     

    The ability to write scripts within Numbers  would be a major step up for Numbers. Also the the ability to change from string to number and back would be useful.

     

    Don't know Apple script. Can it be used as an addon to Numbers ?

     

    Thanks for your time.

  • 5. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
    Badunit Level 6 Level 6 (10,815 points)

    Are you looking for the INDIRECT function?

     

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

  • 6. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
    2Skip2 Level 1 Level 1 (0 points)

    Hi Jerry,

    Your first suggestion solves my first question exactly,   And I thought I had tried all the options. Thanks.

     

    Your second question suggests precisely the way  I am attempting to do it except, I have an existing column of consecutive numbers, representing day dates.  If a row is added an empty gap will appear in that column. I can alarm that case if the number of rows do not match the mumber in the date column.  What I am trying to do is locate the row number or where the gap is. Then, already knowing the column, I can pinpoint the cell. The necessary calculations are made in a hidden column, like you say.  Not sure I entirely grasp your last paragraph, I'll have to play a little.  

     

    I've not yet got to the next part which is to write something in that empty cell. It might present a similar problem to 1).   If you know how to do that software wise, I'd be glad to hear it. I forsee a problem that if I write in it it is no longer empty, what will happen then ???     I'm hoping to get away with Text instead of figures.

  • 7. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    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

  • 8. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
    2Skip2 Level 1 Level 1 (0 points)

    Hi Badunit,

     

    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.

     

    Thanks.

  • 9. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
    2Skip2 Level 1 Level 1 (0 points)

    Hi jerry,

     

    Without have read the section you say, yet, I can't be sure but, think I have discoverd that or partly that by experimenting.

    Like you say, I undoubtedly need to do a lot more reading. My problem is I am to impatient.

     

    I find it to be a general problem with Apple software/manuals;   "Why would I look up 'Headers' or 'Footers' to search for the resolution to a column *** cell, question."  Now you have brought it to my attention I will read it.  The alternative is to read the whole manual and hope to remember all the interconnections.  No chance, to many other fingers in pies.  A instructive workshop would be better but I have never seen one advertised that is within practicality.  I am beginning to realise a quote from another member; "Numbers is not intended to be a serious Spreadsheet", may be reason why.  It does, at the moment, seem to be a poor relation compared to Excel which is the one I am used to.  Still, I am willing to learn more hoping to have my mind changed.

     

    Thanks for the directive.

  • 10. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
    Barry Level 7 Level 7 (29,180 points)

    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

  • 11. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
    Barry Level 7 Level 7 (29,180 points)

    Example:

    Picture 7.png

    Regards,

    Barry

  • 12. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
    2Skip2 Level 1 Level 1 (0 points)

    Hi Barry,

    Sorry but I don't feel bad about being impatient to learn quickly, always have been.

    Yes, I agree and I see your point.   Now I understand a bit more, I can read what Badunit said with different eyes and I can appologise for taking it to literally. At that time I was not able read into it that which someone with just a little more knowledge could.  Ignorance has that effect and is same from both sides of the subject if you think about it. I can't appologise for that. It is the inevertable process everyone goes through. Making mistakes is the best teacher and everyone does it, albeit can be dangerous at times.

     

    I appreciate everyone who takes the time to answer the questions particularly if they are my questions. But, it is voluntary and  answers are bound to be missunderstood at times. Pictures and all that. If I could learn quicker by reading the manual from end to end then I would do it.

     

    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. Your example confirms Jerrys example.

    I am aware of it now. My latest post re INDIRECT possibly suffers from similar ignorance but I'll be interested in the answer.

     

    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  ?

     

    Regards and thanks for your time.

  • 13. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
    Badunit Level 6 Level 6 (10,815 points)

    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.

  • 14. Re: Can a formula produced Cell identity ever be used to extract the contents of the cell ?
    Barry Level 7 Level 7 (29,180 points)

    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

1 2 Previous Next