Query about Last Cell reference.

[Pank Mehta]

I have 12 sheets, which holds monthly information.

Data exists in Column A through K, row 1 down to row 500.

I have a formula in cell AZ1 (done to ensure no one can find it and
subsequently delete it).

When I depress CTRL+END it takes me to AZ500 (as expected).

The question I have is that as all my data is going to be in Columns A
through K and rows 1 to 500, if I reset the Last Cell to say K500 would it
achieve faster saves to a LAN?. Please note that the value from AZ1 is used
in column G1 – G500.

All views greatly appreciated.

[Ron Coderre]

First, you can olnly "reset" the last cell to K500 by moving the AZ1 formula
somewhere within the A1:K500 range.

Second, I'm not sure if moving that formula will significantly impact save
time in this instance.

Third, is there a reason you can't just create a Named Calculation to handle
the AZ1 formula?
Insert>Name>Define
Name: myCalc
RefersTo: =(whatever formula you want here)

That way your A1:K500 cells can refer to myCalc instead of AZ1, resulting in
a more secure calculation that won't be accidentally deleted with a row or
column.

Does that help?
--
Regards,
Ron

[Dave Peterson]

If you use column AZ, then the last used column will be at least AZ.

The only way to shrink that usedrange is to move the formula.

One way is to put it in column H and then hide column H. It may be enough to
keep it safe.

Another alternative would be to add a new worksheet and put the formula in A1 of
that new sheet.

I'm not sure which is more "expensive" in time/size, though.

Pank Mehta wrote:
>
> I have 12 sheets, which holds monthly information.
>
> Data exists in Column A through K, row 1 down to row 500.
>
> I have a formula in cell AZ1 (done to ensure no one can find it and
> subsequently delete it).
>
> When I depress CTRL+END it takes me to AZ500 (as expected).
>
> The question I have is that as all my data is going to be in Columns A
> through K and rows 1 to 500, if I reset the Last Cell to say K500 would it
> achieve faster saves to a LAN?. Please note that the value from AZ1 is used
> in column G1 – G500.
>
> All views greatly appreciated.

--

Dave Peterson

[Pank Mehta]

Ron, Dave,

Many thanks for your time and comments.

"Dave Peterson" wrote:

> If you use column AZ, then the last used column will be at least AZ.
>
> The only way to shrink that usedrange is to move the formula.
>
> One way is to put it in column H and then hide column H. It may be enough to
> keep it safe.
>
> Another alternative would be to add a new worksheet and put the formula in A1 of
> that new sheet.
>
> I'm not sure which is more "expensive" in time/size, though.
>
> Pank Mehta wrote:
> >
> > I have 12 sheets, which holds monthly information.
> >
> > Data exists in Column A through K, row 1 down to row 500.
> >
> > I have a formula in cell AZ1 (done to ensure no one can find it and
> > subsequently delete it).
> >
> > When I depress CTRL+END it takes me to AZ500 (as expected).
> >
> > The question I have is that as all my data is going to be in Columns A
> > through K and rows 1 to 500, if I reset the Last Cell to say K500 would it
> > achieve faster saves to a LAN?. Please note that the value from AZ1 is used
> > in column G1 – G500.
> >
> > All views greatly appreciated.
>
> --
>
> Dave Peterson
>