“I have a question that I feel is most likely quite simple, however the solution eludes me. (I have done this before but cannot recall it)

I have a value in a cell (c2) for a mileage rate of .50

I then have different cities listed going down with their mileage from my home.

I then enter the formula for the first city =c2*e7  (e7 is the cell with the mileage)

I then want to drag the formula down by clicking and holding the little box in the lower right hand corner down across all my city miles.

The problem, is the c2 advances along with the mileage and the results are not what I am looking for.

I want the “c2” to remain constant to the equation as drag the box down across all the other values.”

In this instance the cell referencing wants to be locked to a specific cell.

Which ever cell you want to remain constant add the following.
For example E3 is to be the same all the time therefore change it to $E$3. This will maintain the reference when copying the formula.
Depending on different situations you may only what to lock in the column or the row. To do that only enter one $
For example E$3 or $E3

Downloading SAP reports to excel is relatively easy in most companies. Unfortunately, when the data is downloaded to excel, the data is not in a user friendly format.

ExcelHelp has created several excel macros for several companies to help with SAP to Excel downloads. If you need help with you, send us a request.

This file contains five working sheets.

The first sheet is the masterlist which contains all the raw data. Each row has a category assigned to it. Note that there are four categories.

Each category also has their own corresponding spreadsheet. When the master list is updated, the corresponding category sheet is also updated. This is performed by pressing a button with a macro attached to it.

This file manipulates a sheet which contains all raw data related to depreciation of fixed assets. It then reclassifies the assets according to remaining depreciation amounts to separate sheets, to allow for easy analysis.

Vlookup 2003

Click on the above to download an example of Vlookup or the running of a macro which uses the SelectCase.

The vlookup applies a search for a matching value or amount and then returns a mapped value to the found value or amount.

The macro shows the use of the SelectCase function.

Rather than using IF statements, the SelectCase function can be applied easily in a VBA form to determine the discounting.

This free download template was built to attempt to apply a value to a range of input values.

ExcelHelp is trying to get through as many queries as possible. However due to volume will we be addressing those that are paying queries first followed by easy fixes first and foremost.

ExcelHelp

The inventory report shows the total quantity of stock required to be purchased for each part or inventory id. The inventory id total quantity is determined by a pivot table and also with the use of a sum if formula.

Feel free to download and see how the macro operates.

Student Report 2003

The Student Report shows which objectives each student is meeting or participating in.

The student report uses a macro to automate the reporting of each students classes. The macro uses a pivot table to create the report.

Please feel free to down load the report by clicking on the above, save and click on enable macros. (2007 for Excel 2007 and 2003 for Excel 2003).

Report manipulation

This excel macro was created for SAP. SAP could not further aggregate the general ledger (GL) data into a useable format. The raw data from SAP, therefore had to be dumped to excel, in which the user spent several hours each month to manipulate the data into a useable format.

This report, therefore saves the user time, through the macro. The macro, completes the process of deleting blank columns and rows, moving headings, etc, faster than the user could.

Please feel free to download the above SAP report.

The accounting template is used for numbering months of a financial year to period numbering. This helps with comparing month to month, actual v budget, qtr to qtr, year to year variances.

It is also the starting point for other reports that require the use of months.

Please feel free to download the template by click on the above.