One formula for a single column in Excel 2003
I am using MS Excel 2003. I have a spreadsheet with the exactly the same formula in every cell of a given column (This is possible by using the row() and the indirect() funtions). The spreadsheet is using 6000 rows. Currently, I have copied the formula into each cell. This increases the calculation time of the spreadsheet.
Is it possible to designate a formula for a given column? If so, this would mean I would have only one formula for the whole column and possible speed up the calculation time.
Software/Hardware used:
MS Excel 2003
Is it possible to designate a formula for a given column? If so, this would mean I would have only one formula for the whole column and possible speed up the calculation time.
Software/Hardware used:
MS Excel 2003
ASKED:
February 8, 2011 4:54 PM
UPDATED:
August 18, 2011 6:50 PM
Answer Wiki:
hii i m using ms access 2003 for calculation of closing stock date wise date . for that i have to use three table of opning stock , purchase table, issue table . in which the entry is done date wise date . i have to use the closing stock formula which is
closing stock= opning stock+purchase-issue. in these three table the entry is like that
opning stock table
os value date
20000 1/4/2011
and purchase table
purchase value date
20000 1/4/2011
12000 5/4/2011
20000 20/5/2011
3000 15/5/2011
12000 20/5/2011
issue table
issue value date
12000 1/4/2011
30000 3/4/2011
20000 10/4/2011
11000 10/5/2011
4000 20/5/2011
now i want to create closing stock table date wise by using formula which i have mentioned above
now for the 1/4/2011 it will take the entry from opening stock table and purchase, issue
so i will get the closing stock on 1/4/2011 but on 2/4/2011 opening stock will be the closing stock of 1/4/2011 same thing will be done for other dates as well but one more thing which we need to take care is if there are the entry of opening stock, issue on 1/4/2011 but there is no entry of purchase so in this condition it should take zero value of purchase on 1/4/2011 thank u pls help me i m new user
To see all answers submitted to the Answer Wiki: View Answer History.
You should investigate a macro, that will start at the top and work down to the end of the column, performing the calculation on each cell.
The macro can be assigned a button on the work sheet if you want to make it easier for the user, so thy don’t need to know the macro name.
Although not exactly what you asked, you might want to look at Tools, Options, Calculations to see if changing it to Manual helps. That way, at least you are choosing when your spreadsheet will be bogged down.
Chippy088′s answer is the best. Create a Macro (or VBA script) that runs against the worksheet and calculates all the cells as needed. Then you don’t need any formulas in any of the computed cells – just a button to cause the macro to run and compute the values.
In Excel Help, look at Macro, VBA, Developer.
As an example, the following VBA code will put the row and column numbers in a string of the form “row,column” in the first 6000 rows x 3 columns of a spreadsheet (in much less than one second):
Sub FillWithJunk Dim iRow as integer Dim iCol as Integer For iRow = 1 to 6000 For iCol = 1 to 3 Worksheets(1).Cells(iRow,iCol).Value = iRow & "," & iCol Next ' iCol Next ' iRow End Sub