How NOT to extend formula/refrence to a cell

[PankajSharma4u@gmail.com]

Hi
One of the cells( of sheet2) of my excel has formula as =Sheet1!C2
I want the next (say 30 ) cells to have the same formula =Sheet1!C2.
I dont want excel to automatically change the refrence to C3,C4,C5.....

Turning off tools>>options>edit>>extend data range formats and formula
Doesnt help. Can someone help me out.
Thanks in advance...Pankaj

[Dave Peterson]

You could use:

=sheet1!$c$2

And drag down.

Or you could just use a formula that points at that first formula:

If you have =Sheet1!c2 in A1
then just use =$a$1

Then you only have one formula that points back to that other sheet--if/when you
want to change it.

PankajSharma4u@gmail.com wrote:
>
> Hi
> One of the cells( of sheet2) of my excel has formula as =Sheet1!C2
> I want the next (say 30 ) cells to have the same formula =Sheet1!C2.
> I dont want excel to automatically change the refrence to C3,C4,C5.....
>
> Turning off tools>>options>edit>>extend data range formats and formula
> Doesnt help. Can someone help me out.
> Thanks in advance...Pankaj

--

Dave Peterson

[Pankaj]

it worked .Thank you so much ,Dave


Dave Peterson wrote:
> You could use:
>
> =sheet1!$c$2
>
> And drag down.
>
> Or you could just use a formula that points at that first formula:
>
> If you have =Sheet1!c2 in A1
> then just use =$a$1
>
> Then you only have one formula that points back to that other sheet--if/when you
> want to change it.
>
> PankajSharma4u@gmail.com wrote:
> >
> > Hi
> > One of the cells( of sheet2) of my excel has formula as =Sheet1!C2
> > I want the next (say 30 ) cells to have the same formula =Sheet1!C2.
> > I dont want excel to automatically change the refrence to C3,C4,C5.....
> >
> > Turning off tools>>options>edit>>extend data range formats and formula
> > Doesnt help. Can someone help me out.
> > Thanks in advance...Pankaj
>
> --
>
> Dave Peterson