Prev: Help for sharing a worksheet from a excel sheet
Next: Figuring Dates quit working!
From: jj on 1 Apr 2010 15:06
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 <jj(a)discussions.microsoft.com> 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
>
> .
>
From: jj on 1 Apr 2010 15:18
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 <jj(a)discussions.microsoft.com> 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
>
> .
>
From: Gord Dibben on 1 Apr 2010 15:35
You missed the double quotes.

=INDIRECT("'Unit 0 Data'!A7")


Gord

On Thu, 1 Apr 2010 12:18:05 -0700, jj <jj(a)discussions.microsoft.com> 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 <jj(a)discussions.microsoft.com> 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
>>
>> .
>>

From: jj on 1 Apr 2010 16:32
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 <jj(a)discussions.microsoft.com> 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 <jj(a)discussions.microsoft.com> 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
> >>
> >> .
> >>
>
> .
>
From: Gord Dibben on 1 Apr 2010 17:01
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 <jj(a)discussions.microsoft.com> 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 <jj(a)discussions.microsoft.com> 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 <jj(a)discussions.microsoft.com> 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
>> >>
>> >> .
>> >>
>>
>> .
>>

 |  Next  |  Last
Pages: 1 2
Prev: Help for sharing a worksheet from a excel sheet
Next: Figuring Dates quit working!