ExcelBanter - Reference error in excel

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Reference error in excel (https://www.excelbanter.com/excel-programming/400117-reference-error-excel.html)

Reference error in excel

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

Reference error in excel

On Oct 26, 9:03 pm, JE McGimpsey wrote:
One way:

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

In article .com,

wrote:
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- Hide quoted text -

- Show quoted text -

Perfect. Works great.

Reference error in excel

On Oct 26, 10:00 pm, wrote:
On Oct 26, 9:03 pm, JE McGimpsey wrote:

One way:

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

In article .com,

wrote:
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- Hide quoted text -

- Show quoted text -

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

Reference error in excel

On Oct 29, 5:23 am, JE McGimpsey wrote:
One way:

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

So make In article
.com,

wrote:
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- Hide quoted text -

- Show quoted text -

Excellent. Thank you

Reference error in excel