First column in spreadsheet doesnt update on second spreadsheet using "=Sheet!A1"

Question

The first column of spreadsheet A should also be on spreadsheet B within the same file, to do this I use "=Sheet!A1"

Now that works fine, but if after applying the formulas I edit the original first column again, the second spreadsheet doesnt automatically update.

I have heard from other people that their spreadsheet does update automatically, so it's probably a setting somewhere that dictates when stuff updates.

Can someone tell me where I need to check this? I'm Using Excel 2010

Cheers, Raefe

Answer 1

There is almost no way around the changing of the formulas when you insert/delete rows on the referenced sheet.  Excel is always going to adjust the formulas.  I don't necessarily think that's right, but that's the way it is.  But I said 'almost no way'...

We can use INDIRECT() and a little math.  INDIRECT() takes a text string and treats it as if it were an address.  Let's say that your first formula, ='Tracking List'!$B$2  goes into a cell on row 2 of the other sheet.

Enter this formula:

=INDIRECT("'Tracking List'!A" & 1+ROW()-1)

and now drag that down the sheet.

For this discussion, let us say you are going to put these formulas into column X.  But it could be any column.  I'm using X to clearly distinguish cells on this 'other' sheet and the ones on your Tracking List sheet.

Things to note:  First, notice that Tracking List is enclosed in single-quote marks, so the formula is starting out like:  =INDIRECT(  "  '  Tracking List'    but without the spaces I've just shown.

Second:  the 1+ROW()-1  is calculating a row number.  If we put that first formula into X2 on the 'other' sheet, then it evaluates to  1+2-1 = 2.  We need the math like that in case you fill it up to row 1.

If we wanted the formula in X2 to pick up the value from 'Tracking List'!A1  we would change the math portion to become:

=INDIRECT("'Tracking List'!A" & ROW()-1)

ROW() is going to return the row that the formula is in, so it evaluates to 2-1 = 1  and gets us the information from Tracking List!A1.  You just have to play with that math to get the first formula right and then fill down.

If the formula you entered in the other sheet to get 'Tracking List'A1 was put into row 9 on this other sheet, then you would start with:

=INDIRECT("'Tracking List'!A" & ROW()-8)

since ROW() = 9, then 9-8 = 1 is the result of the evaluation.

If you change the name of sheet 'Tracking List', you have to change it in these formulas: you could either edit one and fill-again, or select them all and use Edit --> Replace (with "look in formulas") to change them all to the new sheet name.

Answer 2

Oh, I understand more now.

What you need to do on that second sheet is use

=Sheet1!$A$1

The $ sign makes each part of it 'absolute'.  So when you add a new row 1 on Sheet1, the formula will still look in (the new) A1 instead of automatically changing to A2.  Since only rows seem to be involved, you could actually use  =Sheet1!A$1  which would make only the row absolute instead of both the column and the row.

Hope that helps.

Thank you, yes this is what I need. But how do I drag the formula down on spreadsheet B?

If I manually enter ='Tracking List'!$B$2 and then manually do it three times changing the cell (='Tracking List'!$B$3, ='Tracking List'!$B$4) and drag down then I just repeatedly get the first three cells. I dont want to have to manually add the Formular for each cell, how can I drag the formula down?

Also, if once adding this formula and I add a new line in the original spreadsheet A, then spreadsheet B automatically jumps one cell so that the added row is again excluded.

Awaiting a reply I remain,

Best regards,

Raefe

Answer 3

Oh, I understand more now.

What you need to do on that second sheet is use

=Sheet1!$A$1

The $ sign makes each part of it 'absolute'.  So when you add a new row 1 on Sheet1, the formula will still look in (the new) A1 instead of automatically changing to A2.  Since only rows seem to be involved, you could actually use  =Sheet1!A$1  which would make only the row absolute instead of both the column and the row.

Hope that helps.

Answer 4

Thanks but the automatic is already selected. The problem isnt that the cells arent updated, because whatever is changed on the cell also occurs automatically on the second sheet.

The issue is that whenever I add rows on the first spreadsheet, these are not taken automatically into account on the other one. Should I possibly use another method e.g. freezing the pane and replicating it on the other spreadsheet, instead of using "=Sheet!A1"?

Because obviously whenever I add a row in the original sheet, the changes dont occur on the second spreadsheet (B) if there is no Formular for it to grab the data from spreadsheet A.

Any ideas?

Answer 5

Go to the [File] tab, click the {Formulas} option.

In the window that appears, in the first section, "Calculation options", make sure that Automatic is chosen.  Click [OK] and close the dialog.