CODEWITHSUNDEEP
arraysgoogle-sheetsreferencegoogle-sheets-formula
halfiranian
halfiranian

Reputation: 59

How can I keep references to changing spreadsheet constant?

I have 2 columns in a sheet that are referencing another dynamic sheet which has new rows added at the top all the time.

I want column A to be a copy of column A in Sheet1, so this works to put in cell A1:

={Sheet1!A:A}

However, I want column B to a formula applied to every row in column B of Sheet1. Problem is, when I put in a a formula, e.g. 

=formula(B1)

then it changes to

=formula(B30)

when 29 new rows added

I want it to stay as B1, but it won't. If I use an absolute reference $B$1 then I can't copy the formula down the column.

Any wizards out there to help me out?

Upvotes: 1

Views: 2529

Answers (3)

player0
player0

Reputation: 1

the proper way would be to use INDIRECT like:

=INDIRECT("Sheet1!B1:B")

Upvotes: 0

Chronocidal
Chronocidal

Reputation: 7951

If you want to get the matching row from a column of another worksheet, then use INDEX and ROW, like so:

=FORMULA(INDEX(Sheet1!$B:$B, ROW(), 1))

This will always return the value in Column B of Sheet1, on the same Row as the formula is in on Sheet2 - even if you insert rows at the top of Sheet1

Upvotes: 5

Pau
Pau

Reputation: 71

You can do "partially absolute reference" (I don't know the correct way of saying this).

You can lock only the column so would be =$A1 which means that it will never change the column but when you drag down the formula, it will change to =$A2, =$A3...

Or you can lock only the row typing =A$1 This way it will be locked on the row only.

You can do this also by pressing F4 several times: 1 time will lock both, the 2nd time will lock the row only, the 3rd time the column only and the 4th time will delete the locking.

Upvotes: 0

Related Questions