Changing cell reference

EddieL

New Member
Joined
Jan 20, 2019
Messages
5
Hi All

I have a total in a cell using the following formula:

=SUM(INDIRECT("W97:W"&ROW()-1))

The formula is written that way so that if extra rows are added after 97, the total would include the added rows. The problem I have is that I have a link from another spreadsheet which references this total, and when rows are added the reference to this total obviously changes. Is there any way of my link following the cell containing the total?

Any help would be much appreciated.

EddieL
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
One way..
You could assign a RangeName to the one-cell formula - "=SUM(INDIRECT("W97:W"&ROW()-1))" and Call it "MyTotal". That will keep track of it regardless os where it gets moved.

But change the Other Formula on the other spreadsheet to refer to "MyTotal" versus the Cell Reference/ HTH; Jim
 

EddieL

New Member
Joined
Jan 20, 2019
Messages
5
Hi Jim

Thanks for your suggestion, but the portion of ROW()-1 in the formula doesn't show which cell this value is in. It actually returns 0 not the value in the cell.
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
I'm surprised that doesn't work since you would be naming the cell with the formula that produces the results. Hummmm...
Thanks for your reply.
Jim
 

Watch MrExcel Video

Forum statistics

Threads
1,109,445
Messages
5,528,800
Members
409,835
Latest member
Mafu1267

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top