Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Query about Last Cell reference.
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. |
#2
|
|||
|
|||
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? InsertNameDefine 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Syntax for inferred cell references | Excel Worksheet Functions | |||
Formula with text and reference to a date cell | Excel Discussion (Misc queries) | |||
international absulute cell reference | Excel Discussion (Misc queries) | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |