Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Cell Entry (2002 XP)

    I have in sheet1 a column of numbers that grows (downward) daily. The last cell entry is linked/referenced to sheet2. How do I make the last cell entry (only) in sheet1

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Cell Entry (2002 XP)

    If you're saying that the last cell is a grand total (or similar), give it a name: Insert|Name|Define. Use that name as your Sheet2 reference - using + and then F5.
    Gre

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Cell Entry (2002 XP)

    That is done but the last cell entry keeps changing and it would be necessary to update the name. Can the naming of the last cell be dynamic?
    Hope I was clear if not let me know.
    thanks

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Cell Entry (2002 XP)

    From what you're saying, you would need a VBA procedure to derive the value. Is the location of the reference on Sheet2 always the same, or can it be pinpointed in a specific manner?
    Gre

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Cell Entry (2002 XP)

    I don't know what you mean by the naming being dynamic. But you can create a name that will refer to the last filled cell in a column.

    Say that you have a number of contiguous entries (without gaps) in column A on Sheet1, starting with A1. Select Insert | Name | Define, specify a name, for instance LastInA, and set the Refers To box to the following formula:

    =OFFSET(Sheet1!A1,COUNTA(Sheet1!A:A)-1,0)

    This name will refer to the last filled cell in column A. Is that what you mean?

  6. #6
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Cell Entry (2002 XP)

    Hans,
    This is exactly what I was looking for, it works.

    thanks again to all <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Cell Entry (2002 XP)

    A non-volatile, fast access to the last numeric value in column A...

    =LOOKUP(9.99999999999999E+307,A:A)
    Microsoft MVP - Excel

  8. #8
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Cell Entry (2002 XP)

    This also works

    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •