Reference error in excel


U

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
 
Ad

Advertisements

J

JE McGimpsey

One way:

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

ucanalways

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top