Problem with the formula reference in excel

[dolke]

Hi to all;

I have one on the first look, simple issue but I'm struggling to solve it for quite some time now.

I made an example file and uploaded it to the megupload as I cannot attach files on the forum. You can download it HERE

Please advise, ty
Dean

 

[mikerickson]

Many of the folk here won't download a file from a third party location.
If you can describe your problem, it might be resolved without reference to your file.

 

[dolke]

I see. OK then I'll do my best.

I have 2 sheets. In sheet 1 there is raw data and in sheet 2 there are formulas that uses that data.

Functions need to use data from the cells in sheet 1. They need to use data from the exact cell no mater user cuts date from that cell or not… here is description to clerify: For example here A1 (sheet2) is showing data from A1(sheet1). If user cuts data from the A1(sheet1) and paste is somewhere formula in A1(sheet2) will piont to new location, new cell where user pasted the data. I would like to stop this, to make my formulas in this sheet, sheet2 to always point to the same cell no mather if user cuts the data in sheet 1 and paste it somewhere else. So here A1 cell has formula: ='1'!A1 and I would like this cell to always pinpoint to A1 in sheet1 no mather what someone do with the data in A1 sheet1.

 

[T. Valko]

I see. OK then I'll do my best.

I have 2 sheets. In sheet 1 there is raw data and in sheet 2 there are formulas that uses that data.

Functions need to use data from the cells in sheet 1. They need to use data from the exact cell no mater user cuts date from that cell or not… here is description to clerify: For example here A1 (sheet2) is showing data from A1(sheet1). If user cuts data from the A1(sheet1) and paste is somewhere formula in A1(sheet2) will piont to new location, new cell where user pasted the data. I would like to stop this, to make my formulas in this sheet, sheet2 to always point to the same cell no mather if user cuts the data in sheet 1 and paste it somewhere else. So here A1 cell has formula: ='1'!A1 and I would like this cell to always pinpoint to A1 in sheet1 no mather what someone do with the data in A1 sheet1.

Use the INDIRECT function.

=INDIRECT("Sheet1!A1")

That formula will always refer to Sheet1 A1.

 

[Marcelo Branco]

Hi,

Maybe this

In sheet 2 A1 insert this formula
=INDEX('1'!$A$1:$D$18,ROWS($1:1),COLUMN(A:A))

copy across till D1 and down

HTH

M.

 

[dolke]

@T. Valko

Ty for the suggestion but it doesn't work for me.
I cannot spread that formula to another cells but I have to write every cell manually. As I have thousands it simply wont work...

@Marcelo Branco

Ty also for taking interest in my issue.
The formula that you gave me reports an error when I paste it into my example.
Can you send me the file where you have implemented this solution? Thanks.

Dean

 

[mikerickson]

Perhaps it would be best if you could train your users to use Copy/Paste instead of Cut/Paste.
Also, there are formulas like
=INDIRECT("Sheet1!" & CHAR(64+COLUMN(A1)) & ROW(A1)) that will change when dragged and filled like =Sheet1!A1 would.

 

[dolke]

@mikerickson

Ty friend, but problem lies in that Cut/Paste thing... as there are many data that they deal with sometimes, by accident, they cut and then paste data who knows where in the sheet, and that makes quite a mess...

I'll try what u suggested and get back here...

EDIT: Mr Mike, u are genius It works!

 

[Aladin Akyurek]

Let A1:C2 house the data on Sheet1.

Let's reference this on Sheet2 with a formula in A2:

=INDEX(Sheet1!A:A,ROWS($A$2:A2))

which is copied to B2 then down.

Moving the data from A1:C2 on Sheet1 using cut-and-paste oruing mouse won't change the formula reference on Sheet2. However, deleting Sheet1 will cause #REF! errors, involving the sheet name.