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.

0
Pank (119)
5/6/2005 12:01:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
475 Views

Similar Articles

[PageSpeed] 15

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

0
5/6/2005 12:37:31 PM
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
0
ec357201 (5290)
5/6/2005 12:50:51 PM
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
> 
0
Pank (119)
5/9/2005 9:47:01 AM
Reply:

Similar Artilces:

Column Reference to External Source As a Variable
Can anyone help me convert the column referenced in the formula below into a variable that the user can define? More specifically, I have several columns that I need to read from an external workbook (Short_Billy.xls). Each column to the right of column C represents an additional day out in a 14 day projection from today (whose data is held in column C). In cell I5 of my active workbook (Inventory.xls), I would like the user to be able to enter a value representing the number of days out they would like to see the projection for (0=today=Column C, 1=Tomorrow=Column D, etc.). In cell I6, I...

The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Need Syntax for "AND" to Evaluate 2 Cells
I need to evaluate 2 cells while inside an "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". I thought AND would work but I cannot get it to work; I receive a syntax error on the AND(Range... line. Can someone please provide me the proper syntax to evaluate the 2 cells? Here's my code... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveSheet.Name = "Sheet1" Then And(Range("I3") <> "", Range("K4") = "") Then Range("K4") = Range("K3") End...

Enter "1", cell show ".01". Why?
Any number typed into a cell is divided by 100. If proceded by "=" the number is correct. What caused this and how can I fix it? Try this .. Click Tools > Options > Edit tab Uncheck "Fixed decimal" > OK Things should be back to normal now .. (it's a fixed decimal setting !) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Yonian" <Yonian@discussions.microsoft.com> wrote in message news:40499CA4-7FAF-42A6-8B19-A90881735C50@microsoft.com... > Any number typed into a cell is divided by 100. > If p...

selecting a cell
I seem unable to select a single cell, or a single row--click on one in the normal manner, and the two below also highlight, then delete or whatever command is given. If I input a number/text, that just goes into the one cell. tapping F8 increases this to two wide and three high automatically selected. Also, very slow to do almost anything. Thanks Pat, Are you by any chance using Excel 2007? If so there is a known bug that causes multiple cell selection and I understand this has been reported to Microsoft. If you take the zoom level up and down this is reported to cl...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

Why is Excel changing the last 2 digits of a 17 digit num to 00.
When I enter a 17 digit number in a cell in Excel, the last 2 digits turn to 00 when I leave the cell. Format - Cell does not have a setting to stop this 'feature'. How do I make Excel recongize the large number? On Thu, 28 Jul 2005 19:09:01 -0700, "Allie" <Allie@discussions.microsoft.com> wrote: >When I enter a 17 digit number in a cell in Excel, the last 2 digits turn to >00 when I leave the cell. Format - Cell does not have a setting to stop this >'feature'. Excel Specifications and Limits: Calculation specifications Feature Maximu...

Summing in A Query
Hello, I have a database which fuel records are stored in. The data is stored in two tables. The first records the daily logs that operators use each time they fuel up. It stores their name, the key they used (keylock fuel system - it's ancient) the unit number of the equipment using the fuel, and the amount of fuel they took. The second table stores the month end information retrieved from the key lock print out. It keeps a running total of the amount of fuel taken with each key, and the operator using that key. We have problems making sure all of the fuel is accounted for each mon...

cell will not center
Hi. I have a user with an Excel worksheet. There are multiple rows and columns and they are all set on center alignment, (center alignment icon on the toolbar as well as Format Cells --> Horizontal Alignment --> Center.) The alphabetical characters align correctly but the numerical don't, as they will only left align. Format Cells --> Number is set to General, so I don't know why it won't change the alignment. Other than the worksheet being corrupted, I don't know what could be wrong with it. Any suggestions are much appreciated. Thanks! Hilary =?Utf-8?B?SG...

Query fields
Is it possible to write a criteria where the value of an empty field is "0.00"? Background: I have three queries with different customer account groups. Not every salesperson has customer accounts in every accountgroup - so, he will not shown up in that query. But he has accounts included in another query. Now, I would like to get a sum of commission earned by each salesman calculated from all three queries together. Since the salesman has no record in one query the total sum of that specific salesman is not shown. Any idea how to solve that problem? Thanks Klaus On Wed, 29 A...

Count # of cells b/w cells ...
Hello, I have the following data in a column: 7 0 0 0 7 0 0 0 0 0 7 0 0 7 0 0 0 0 0 0 7 etc. The number of zero's between the 7's is random. I want a formula tha would count the number of zeros between the 7's. Thanks, Ari Bar -- AriBar ----------------------------------------------------------------------- AriBari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2504 View this thread: http://www.excelforum.com/showthread.php?threadid=38806 Assume A5:A20 is the data, try this: B5 = A5+B4 (copy formula down) Now make a table with 2 column...

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

Query to hide duplicate records
I recall that this used to easy in previous versions (Unique values only ??), but in 2007, I can't get this to work at all. I have a table with our companie's job numbers in it. The job numbers show up multiple times because of different phases of the project: ProjNum ProjDescription 3077 Univ. of Vermont/UC/LEED 3077 Univ. of Vermont/University Commons: Building Fee 3077 Univ. of Vermont/University Commons: Excess Professiona... I need to jut have a single listing of each project number, otherwise, I get repeated records in the query that looks at this information (which ...

format cell #4
In Access, I can set up a field that "forces" the user to enter info - a date, for example - in a certain way, such as 25 Jan 05 or enter time as 12:15 AM. Is there a way that I can "force" this in excel? Thank you. Hello- Without invoking something more technical, you can select the cell(s) and go to Data>Validation and choose what type of entry be allowed in the field. Format the cell in the manner you wish to have the date or time expressed. HTH |:>) "HJC" wrote: > In Access, I can set up a field that "forces" the user to enter in...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

RPC over HTTP/S on Exchange 2003
I have been configuring my single server with exchange to use RPC over https I have followed the instructions in MS guide and another simplified guide at http://www.petri.co.il/configure_rpc_over_https_on_a_single_server.htm Server spec is: Server 2003 standard SP1, Exchange 2003 SP1, XP client SP2 with outlook 2003 sp2 The bottom line is that when testing from the WAN, the outlook client will not connect and say that the exchange server is unavailable. I have a lot of experience configuring rpc over http/s with sbs2003 but this is the first time for server 2003 standard. I have outlook ...

Formatting cells and getting pound signs
I am using Excel 2003 with all updates as of 4/28/04 and trying to format a cell using the custom category and choosing the #,##0.00 type. I am trying to add the $ symbol at the beginning of the type and add text at the end of the type to look like this $#,##0.00 "text". When I do this however it shows up in my cell on my worksheet as ##########. It does know what the value is and shows as I would expect it to when I place mouse over cell in a balloon If I use only the $ symbol befor the type it shows fine. If I use only the "text" after the type is shows fine. Using the...

Problem with vba code to export query result in excel
Hi, I have a access report that exports to excel with click of a button after choosing parameters. This works well. However I have to modify couple of fields to utilize formula in the export module. I am not sure how to do this. I am writing the above code which seems to cause problem. I appreciate any help to resolve this issue. Thanks. Code: If lngColumn = 12 Then xlc.Offset(0, lngColumn).Value = =([UnitPrice]*[OriginalShippedQty])/1000 End If It seems the fields UnitPrice and OrigianalShippedQty are not being recognized here Jack wrote: >Hi, >I have a acces...

Find and replace with bold in cells
I have a VB6 program that is executing Excel 2007, opening a worksheet, and extracting some of the cells to write data to a text file. Some of the cells contain bold text on some (not necessarily all) of the text in the cell. I would like to do a find and replace on the bold tagging to replace it with something like "<b>" at the start of it and "</b>" at the end of it. How do I set this up in VB6? Thanks! The following function will return a string including <b> and </b> tags from the text of cell R. Function BoldMarkup(R As Range) As...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

Conditional Formatting on cells beginning with a hyphen
Is it possible to do conditional formatting on cells beginning with a hyphen? Thanks, Greg 1. Place the cursor in A1 cell and select the Range 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and paste the below formula =LEFT(A1,1)="-" 4. Click Format Button>Font>Color select your desired font & Background Color pattern and then give ok Change the cell reference of A1 to your desired cell, if required. But keep in mind that when applying the conditional formatting the Active cell should be in the ce...

Formula to find last monday (tue, wedn, thu or friday) for a given month
Hi, I need a formula to calculate the date of the last monday, tuesday, wednesday, thursday or friday of a given month. Can't seem to find the answer anywhere. example: day: wednesday (or corresponding nr) month: 3 year: 2004 Result: 31/03/04 Who can help? Thank you for reading and eventually answering my question.Back Visit http://www.cpearson.com/excel/DateTimeWS.htm#DaysInMonth -- Kind Regards, Niek Otten Microsoft MVP - Excel "Michele" <mw001@pandora.be> wrote in message news:b30b6913.0402090708.556d0faa@posting.google.com... > Hi, > I need a...