Excel MS - Excel

[bthadeshwar]

Hi

I have an excel file containing 2 spreadsheets - say SP1 and SP2.

On SP1, i have entered dates across the columns and amounts in respective rows.

On SP2, I have linked the contents of SP1 with formulas in the same columns as that of SP1 but different rows as that of SP1.

The problem that I have is when i move contents say from column M to column N on SP1, the same does not get tracked on SP2 and it gives me #REF! message.

Could somebody help please!

 

[alow]

How are you moving the data in SP1? And do you want the formulas in SP2 to 'follow' the moved data? Say you have a formula in sp2 that references SP1!M41 and you moved that data to column N, do you want the formula to stay the same and show with the new values in column N? Or do you want the formula to update and and change the reference to SP1!N41? Also, what version of Excel are you using?

 

[bthadeshwar]

Hi Alow

How are you moving the data in SP1? -

By dragging the same across. I have even tried cutting and pasting but it gives the same response.

And do you want the formulas in SP2 to 'follow' the moved data? Say you have a formula in sp2 that references SP1!M41 and you moved that data to column N, do you want the formula to stay the same and show with the new values in column N? Or do you want the formula to update and and change the reference to SP1!N41? -

Say, I have written 1,258,000 in N1 of SP1. On SP2, on N1, I have linked the same. If i move 1,258,000 on SP1 to say Q1, on my SP2, N1 still refers to Q1. What I want is in SP2, N1 should always refer to N1 of SP1.

Also, what version of Excel are you using? - 2010

Thanks

 

[alow]

What you can do is where you have a reference to SP2!N1, you can change the reference to INDIRECT("SP2!N1")

That should work, good luck!

 

[bthadeshwar]

Hi

Thanks for that. I tried doing that. But now the problem is i cannot copy the formula to other cells. Would there be a way of copying the formula across? I literally have to do it to atleast 1000 cells.

Cheers

 

[alow]

I see your problem....Try this:
INDIRECT("SP2!N"&ROW(N1))
That should allow you to copy the formula down and always look on the same row of SP2 as the current cell in SP1. If you're starting in Row 2, change N1 in the formula to N2. Good luck again!