+ Reply to Thread
Results 1 to 5 of 5

Refer to same cell with a formula even if rows added

  1. #1
    Registered User
    Join Date
    03-13-2023
    Location
    Mankato, MN, USA
    MS-Off Ver
    MS Office 2021
    Posts
    16

    Refer to same cell with a formula even if rows added

    I have 2 spreadsheets on Google Drive Sheets, one is a summary page (page 1) and the other is the detailed page (page 2). On page 1, I have the formula (=importrange) to a particular cell on page 2. However, lines are added and removed frequently on page 2 moving the linked cell constantly. I have tried $ before the column but that doesn't work. Is there a way for my linked cell on page 1 to keep with the ever moving cell on page 2?

    Thank you for any help!
    Deb

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    43,732

    Re: Refer to same cell with a formula even if rows added

    Don't know about Google Drive Sheets, but in Excel you could use =INDEX(A:A,1) instead of =A1 to fix the link.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    03-13-2023
    Location
    Mankato, MN, USA
    MS-Off Ver
    MS Office 2021
    Posts
    16

    Re: Refer to same cell with a formula even if rows added

    Thank you for your advise, but that did not work. Here is more detail if this helps.

    I am using the following formulas currently:


    1. In cell D25 on page 2: COUNTA(D2:D24)

    Rows are added and removed as orders are received and shipped throughout the day and the calculation in cell D25 is the total orders we have left at the end of the day.


    2. In cell B2 on page 1: (IMPORTRANGE("docs.google.com/spreadsheetsxxxxx","PAGE 2!D25"))

    This links cell D25 from Page 2 to Page 1 so my summary on Page 1 is always current.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    43,732

    Re: Refer to same cell with a formula even if rows added

    Given you have (IMPORTRANGE("docs.google.com/spreadsheetsxxxxx","PAGE 2!D25")) and "PAGE 2!D25" is text, I don't know how that changes.

    In the absence of a viable solution that does exactly what you require, I would be inclined to move the total to row 1.

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,545

    Re: Refer to same cell with a formula even if rows added

    I don't have G-shts...just a guess:
    Select cell D25, give it a name (e.g. lastrow)
    In some unused cell, create the formula
    ="Page 2!D2:D" & (ROW(lastrow)-1),
    Give that cell a name also...(say, myRange)

    Refer to the dynamic named range with the INDIRECT function:
    =SUM(INDIRECT(myRange))


    ROW(lastrow) should adjust the current row of the cell as rows above are added/deleted...
    Last edited by protonLeah; 08-16-2023 at 03:28 PM.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Refer to cell, search sheet column for refer cell including other cell?
    By notpaidtodothis in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-16-2023, 03:00 PM
  2. Replies: 5
    Last Post: 04-03-2013, 04:15 PM
  3. [SOLVED] Formula updating when rows are added on other sheet
    By D-smoke in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-21-2013, 07:30 AM
  4. Formula updating when rows are added on other sheet
    By D-smoke in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2013, 06:37 AM
  5. auto populate a formula in a cell as new rows of data are added
    By CharterJP in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-30-2012, 06:43 PM
  6. Sum values in cell from variable rows (rows are added and deleted)
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2010, 08:38 AM
  7. Replies: 0
    Last Post: 07-20-2005, 10:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1