# Refer to same cell with a formula even if rows added

1. ## 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. ## 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.

3. ## 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. ## 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. ## 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...

##### Users Browsing this Thread

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

#### 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