Reference error in excel

[ucanalways]

I have data in Column A of Sheet1. In Sheet2, I have the following
formula in cells A1 and B1

=MIN(Sheet1!A:A) =>formula in cell A1
=MIN(Sheet1!B:B) =>formula in cell B1

When I cut the values of column A in sheet1 and paste in column B, the
formula of cells A1 and B1 change as given below.

=MIN(Sheet1!B:B) =>formula in cell A1
=MIN(Sheet1!#REF!) =>formula in cell B1, thereby giving me error.

Please let me know any way to overcome this error. Is there anyway to
lock the fomula in cells A1 and B1 of sheet2? I know that copying the
values of column A to column B and then deleting the values of column
A prevents the formula change in cells A1, B1. But, is there any way
to restrict this for the cut operation?

Thanks

 

[JE McGimpsey]

One way:

Sheet2:
A1: =MIN(INDIRECT("'Sheet1'!A:A"))
B1: =MIN(INDIRECT("'Sheet1'!B:B"))

 

[ucanalways]

One way:

Sheet2:
A1: =MIN(INDIRECT("'Sheet1'!A:A"))
B1: =MIN(INDIRECT("'Sheet1'!B:B"))








- Show quoted text -

Perfect. Works great.

 

[ucanalways]

One way:

Sheet2:
A1: =MIN(INDIRECT("'Sheet1'!A:A"))
B1: =MIN(INDIRECT("'Sheet1'!B:B"))


Perfect. Works great.- Hide quoted text -

- Show quoted text -

Now, I am trying to drag the formula across IV cells using the + icon,
so that MIN(INDIRECT("'Sheet1'!A:A")) is for cell A1 and
MIN(INDIRECT("'Sheet1'!IV:IV")) is for cell IV1. The series is not
getting filled instead the formula is just copied. Anyway to fill the
series. Please let me know. Thanks

 

[JE McGimpsey]

One way:

=MIN(INDEX(INDIRECT("'Sheet1'!A:IV"),,COLUMN()))

So make In article

 

[ucanalways]

One way:

=MIN(INDEX(INDIRECT("'Sheet1'!A:IV"),,COLUMN()))

So make In article





- Show quoted text -

Excellent. Thank you

 

[ucanalways]

One way:

=MIN(INDEX(INDIRECT("'Sheet1'!A:IV"),,COLUMN()))

So make In article





- Show quoted text -

Excellent. Thank you