| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absoute Cell Ref Changes When Inserting New Row in Referenced Shee
I have an application where I need to update cell B5 in Sheet 1 with a cell
value from a specific cell (C4) on Sheet 2. The issue is that in this instance, I need to be able to select Row 4 in Sheet 2 and insert a new row each month, so that I can record new data in Row 4 for the current month. I thought that by making the cell reference in Sheet 1 absoute ($B$5) the current value in Sheet 2, C4 would always be captured. In fact what happens is that when I select Row 4, Sheet 2 and insert a new row, the $B$5,Sheet 1 cell reference changes to $B$6,Sheet 1. How can I make sure that the cell reference in Sheet 1 remains absolute with respect to capturing the value in C4, Sheet 2 when a new line in Sheet 2 is inserted? Thanks for any help you can provide. John |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Absoute Cell Ref Changes When Inserting New Row in Referenced Shee
This will *always* refer to cell B5:
=INDIRECT("B5") -- Biff Microsoft Excel MVP "jj" wrote in message ... I have an application where I need to update cell B5 in Sheet 1 with a cell value from a specific cell (C4) on Sheet 2. The issue is that in this instance, I need to be able to select Row 4 in Sheet 2 and insert a new row each month, so that I can record new data in Row 4 for the current month. I thought that by making the cell reference in Sheet 1 absoute ($B$5) the current value in Sheet 2, C4 would always be captured. In fact what happens is that when I select Row 4, Sheet 2 and insert a new row, the $B$5,Sheet 1 cell reference changes to $B$6,Sheet 1. How can I make sure that the cell reference in Sheet 1 remains absolute with respect to capturing the value in C4, Sheet 2 when a new line in Sheet 2 is inserted? Thanks for any help you can provide. John |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Absoute Cell Ref Changes When Inserting New Row in Referenced Shee
I think you have a typo or two in your description but maybe this will help.
In B5 of Sheet1 enter =INDIRECT("Sheet2!C4") Will always refer to Sheet1!C4 Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 10:45:02 -0700, jj wrote: I have an application where I need to update cell B5 in Sheet 1 with a cell value from a specific cell (C4) on Sheet 2. The issue is that in this instance, I need to be able to select Row 4 in Sheet 2 and insert a new row each month, so that I can record new data in Row 4 for the current month. I thought that by making the cell reference in Sheet 1 absoute ($B$5) the current value in Sheet 2, C4 would always be captured. In fact what happens is that when I select Row 4, Sheet 2 and insert a new row, the $B$5,Sheet 1 cell reference changes to $B$6,Sheet 1. How can I make sure that the cell reference in Sheet 1 remains absolute with respect to capturing the value in C4, Sheet 2 when a new line in Sheet 2 is inserted? Thanks for any help you can provide. John |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Absoute Cell Ref Changes When Inserting New Row in Referenced
THANKS SO MUCH!!
"Gord Dibben" wrote: I think you have a typo or two in your description but maybe this will help. In B5 of Sheet1 enter =INDIRECT("Sheet2!C4") Will always refer to Sheet1!C4 Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 10:45:02 -0700, jj wrote: I have an application where I need to update cell B5 in Sheet 1 with a cell value from a specific cell (C4) on Sheet 2. The issue is that in this instance, I need to be able to select Row 4 in Sheet 2 and insert a new row each month, so that I can record new data in Row 4 for the current month. I thought that by making the cell reference in Sheet 1 absoute ($B$5) the current value in Sheet 2, C4 would always be captured. In fact what happens is that when I select Row 4, Sheet 2 and insert a new row, the $B$5,Sheet 1 cell reference changes to $B$6,Sheet 1. How can I make sure that the cell reference in Sheet 1 remains absolute with respect to capturing the value in C4, Sheet 2 when a new line in Sheet 2 is inserted? Thanks for any help you can provide. John . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Absoute Cell Ref Changes When Inserting New Row in Referenced
Hi again. It didn't work... the dreaded #REF! error.
In the B5, Sheet 1 cell, I have =INDIRECT('Unit 0 Data'!A7). The A7 field is formatted as a date, and displays MMM-YY for the entered value mm/dd/yyyy. Also, both Sheet 1 and Sheet 2 are worksheets in the same workbook. This is Excel 2002 on a Windows 2000 machine. Thanks for any ideas. John "Gord Dibben" wrote: I think you have a typo or two in your description but maybe this will help. In B5 of Sheet1 enter =INDIRECT("Sheet2!C4") Will always refer to Sheet1!C4 Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 10:45:02 -0700, jj wrote: I have an application where I need to update cell B5 in Sheet 1 with a cell value from a specific cell (C4) on Sheet 2. The issue is that in this instance, I need to be able to select Row 4 in Sheet 2 and insert a new row each month, so that I can record new data in Row 4 for the current month. I thought that by making the cell reference in Sheet 1 absoute ($B$5) the current value in Sheet 2, C4 would always be captured. In fact what happens is that when I select Row 4, Sheet 2 and insert a new row, the $B$5,Sheet 1 cell reference changes to $B$6,Sheet 1. How can I make sure that the cell reference in Sheet 1 remains absolute with respect to capturing the value in C4, Sheet 2 when a new line in Sheet 2 is inserted? Thanks for any help you can provide. John . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Absoute Cell Ref Changes When Inserting New Row in Referenced
You missed the double quotes.
=INDIRECT("'Unit 0 Data'!A7") Gord On Thu, 1 Apr 2010 12:18:05 -0700, jj wrote: Hi again. It didn't work... the dreaded #REF! error. In the B5, Sheet 1 cell, I have =INDIRECT('Unit 0 Data'!A7). The A7 field is formatted as a date, and displays MMM-YY for the entered value mm/dd/yyyy. Also, both Sheet 1 and Sheet 2 are worksheets in the same workbook. This is Excel 2002 on a Windows 2000 machine. Thanks for any ideas. John "Gord Dibben" wrote: I think you have a typo or two in your description but maybe this will help. In B5 of Sheet1 enter =INDIRECT("Sheet2!C4") Will always refer to Sheet1!C4 Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 10:45:02 -0700, jj wrote: I have an application where I need to update cell B5 in Sheet 1 with a cell value from a specific cell (C4) on Sheet 2. The issue is that in this instance, I need to be able to select Row 4 in Sheet 2 and insert a new row each month, so that I can record new data in Row 4 for the current month. I thought that by making the cell reference in Sheet 1 absoute ($B$5) the current value in Sheet 2, C4 would always be captured. In fact what happens is that when I select Row 4, Sheet 2 and insert a new row, the $B$5,Sheet 1 cell reference changes to $B$6,Sheet 1. How can I make sure that the cell reference in Sheet 1 remains absolute with respect to capturing the value in C4, Sheet 2 when a new line in Sheet 2 is inserted? Thanks for any help you can provide. John . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Absoute Cell Ref Changes When Inserting New Row in Referenced
Thanks, Gord. That got it. What are the significance of single quotes and
double quotes? In other words, how are they parsed by Excel? Thanks for the education... John "Gord Dibben" wrote: You missed the double quotes. =INDIRECT("'Unit 0 Data'!A7") Gord On Thu, 1 Apr 2010 12:18:05 -0700, jj wrote: Hi again. It didn't work... the dreaded #REF! error. In the B5, Sheet 1 cell, I have =INDIRECT('Unit 0 Data'!A7). The A7 field is formatted as a date, and displays MMM-YY for the entered value mm/dd/yyyy. Also, both Sheet 1 and Sheet 2 are worksheets in the same workbook. This is Excel 2002 on a Windows 2000 machine. Thanks for any ideas. John "Gord Dibben" wrote: I think you have a typo or two in your description but maybe this will help. In B5 of Sheet1 enter =INDIRECT("Sheet2!C4") Will always refer to Sheet1!C4 Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 10:45:02 -0700, jj wrote: I have an application where I need to update cell B5 in Sheet 1 with a cell value from a specific cell (C4) on Sheet 2. The issue is that in this instance, I need to be able to select Row 4 in Sheet 2 and insert a new row each month, so that I can record new data in Row 4 for the current month. I thought that by making the cell reference in Sheet 1 absoute ($B$5) the current value in Sheet 2, C4 would always be captured. In fact what happens is that when I select Row 4, Sheet 2 and insert a new row, the $B$5,Sheet 1 cell reference changes to $B$6,Sheet 1. How can I make sure that the cell reference in Sheet 1 remains absolute with respect to capturing the value in C4, Sheet 2 when a new line in Sheet 2 is inserted? Thanks for any help you can provide. John . . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Absoute Cell Ref Changes When Inserting New Row in Referenced
Single quotes are used when your sheet name has spaces.
The double quotes denote a string(text) Indirect uses a string. See INDIRECT help for much more on its use. Gord On Thu, 1 Apr 2010 13:32:01 -0700, jj wrote: Thanks, Gord. That got it. What are the significance of single quotes and double quotes? In other words, how are they parsed by Excel? Thanks for the education... John "Gord Dibben" wrote: You missed the double quotes. =INDIRECT("'Unit 0 Data'!A7") Gord On Thu, 1 Apr 2010 12:18:05 -0700, jj wrote: Hi again. It didn't work... the dreaded #REF! error. In the B5, Sheet 1 cell, I have =INDIRECT('Unit 0 Data'!A7). The A7 field is formatted as a date, and displays MMM-YY for the entered value mm/dd/yyyy. Also, both Sheet 1 and Sheet 2 are worksheets in the same workbook. This is Excel 2002 on a Windows 2000 machine. Thanks for any ideas. John "Gord Dibben" wrote: I think you have a typo or two in your description but maybe this will help. In B5 of Sheet1 enter =INDIRECT("Sheet2!C4") Will always refer to Sheet1!C4 Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 10:45:02 -0700, jj wrote: I have an application where I need to update cell B5 in Sheet 1 with a cell value from a specific cell (C4) on Sheet 2. The issue is that in this instance, I need to be able to select Row 4 in Sheet 2 and insert a new row each month, so that I can record new data in Row 4 for the current month. I thought that by making the cell reference in Sheet 1 absoute ($B$5) the current value in Sheet 2, C4 would always be captured. In fact what happens is that when I select Row 4, Sheet 2 and insert a new row, the $B$5,Sheet 1 cell reference changes to $B$6,Sheet 1. How can I make sure that the cell reference in Sheet 1 remains absolute with respect to capturing the value in C4, Sheet 2 when a new line in Sheet 2 is inserted? Thanks for any help you can provide. John . . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Absoute Cell Ref Changes When Inserting New Row in Referenced
Perfect. It worked. Thanks again.
"Gord Dibben" wrote: You missed the double quotes. =INDIRECT("'Unit 0 Data'!A7") Gord On Thu, 1 Apr 2010 12:18:05 -0700, jj wrote: Hi again. It didn't work... the dreaded #REF! error. In the B5, Sheet 1 cell, I have =INDIRECT('Unit 0 Data'!A7). The A7 field is formatted as a date, and displays MMM-YY for the entered value mm/dd/yyyy. Also, both Sheet 1 and Sheet 2 are worksheets in the same workbook. This is Excel 2002 on a Windows 2000 machine. Thanks for any ideas. John "Gord Dibben" wrote: I think you have a typo or two in your description but maybe this will help. In B5 of Sheet1 enter =INDIRECT("Sheet2!C4") Will always refer to Sheet1!C4 Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 10:45:02 -0700, jj wrote: I have an application where I need to update cell B5 in Sheet 1 with a cell value from a specific cell (C4) on Sheet 2. The issue is that in this instance, I need to be able to select Row 4 in Sheet 2 and insert a new row each month, so that I can record new data in Row 4 for the current month. I thought that by making the cell reference in Sheet 1 absoute ($B$5) the current value in Sheet 2, C4 would always be captured. In fact what happens is that when I select Row 4, Sheet 2 and insert a new row, the $B$5,Sheet 1 cell reference changes to $B$6,Sheet 1. How can I make sure that the cell reference in Sheet 1 remains absolute with respect to capturing the value in C4, Sheet 2 when a new line in Sheet 2 is inserted? Thanks for any help you can provide. John . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Totalling Values From The Same Cell In 12 Worksheets Onto New Shee | Excel Worksheet Functions | |||
| Totalling Values From The Same Cell In 12 Worksheets Onto New Shee | Setting up and Configuration of Excel | |||
| How can I insert the tab name into a cell in a different shee... | Excel Discussion (Misc queries) | |||
| a unique cell value returns multiple cell values from another shee | Excel Worksheet Functions | |||
| How can I enter information in a different cell in one Excel shee. | Setting up and Configuration of Excel | |||
Hybrid Mode
