Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference error in excel
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference error in excel
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 - Business Explorer - Missing object or reference error | Excel Discussion (Misc queries) | |||
"Reference not valid" error when I try to edit the macro in excel | Excel Discussion (Misc queries) | |||
reference #value error | Excel Worksheet Functions | |||
removal of circular reference error on excel workbook load | Excel Programming | |||
VBA Action on excel #Reference! error 4? | Excel Programming |