Prev: I want to show only the first three columns
Next: VBA to sidestep popups...
From: jj on 1 Apr 2010 13:45
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: T. Valko on 1 Apr 2010 13:54
This will *always* refer to cell B5:

=INDIRECT("B5")

--
Biff
Microsoft Excel MVP


"jj" <jj(a)discussions.microsoft.com> wrote in message
news:CDCA73BC-BF66-473A-9C28-66FDB676565B(a)microsoft.com...
>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 14:07
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

 | 
Pages: 1
Prev: I want to show only the first three columns
Next: VBA to sidestep popups...