Find and Replace cells’ formula - increasing efficiency

2009-01-10T07:31:00.000-08:00

Most of my friends are quite capable of doing some basic formula with Excel / OpenOfficeCalc, but when it comes to routine works that require same formulas to process daily/weekly/monthly whatsoever data, they tend to re-create the formula again and again instead re-use/re-cycle the formulas. Well it’s time for some efficiency example. Suppose we are in some company that sells their products through branches and agents, we have a task to recapitulate weekly data, in this example those files are data-week1.xls and data-week2.xls as shown below

data-week1.xls

data-week2.xls

We need to have a recapitulation of Sales, Cost, and Agent Fee
Create a new file to recapitulate data in those two files, here we choose the SUMIF syntax for this task
Lets name it 02_summary-weeks.xls

We just recapitulated the Total Cost for Week 1, yes we can do the similar things to Sales & Agent Fee
But instead of doing the rather lengthy way, lets take a shortcut.

Copy the formula from B4:B11 to C4:C11

Notice that the formula still referring to the same cells ($E$2:$E$101) the Sales columns in data-week1.xls, we need those $E switched to $F

Block cells range C4:C11 then do a Find and Replace, Find $E$ replace with $F$, the Total Sales will be created

Do similar things to Total Agent Fee, and we’ll have Week-1 recapitulaztion report.

How about week 2 ? not so much different

Copy cells range B4 :D 11 to E4:G11
Both files (data-week1.xls & data-week2.xls) must be opened
Block cells range E4:G11 then do a Find and Replace again , Find data-week1.xls replace with data-week2.xls

Voila Week2 recapitulation is done

You can do a lot with Find and Replace formulas, the application depends on your creativity
Get the above example files here

Cell Options / Validation for Budgeting or Forecasting

2009-01-09T06:51:00.000-08:00

Have you ever been working on your budgeting or forecasting task, which most of it has more than one options/assumptions. Yes if you use spreadsheet, changing cells already help you a lot, but ever considering a more convenient way and still looking for it ? Well here is your answer, in Microsoft Excel it’s called Validation, in OpenofficeCalc we call it Validity.

By combining with some simple VLOOKUP or HLOOKUP syntax will bring you efficiency just like a snap.

Step 1: After creating your assumption, create the validation / validity

Menu -> Data -> Validation

Step 2: Change ‘Any Value’ to ‘List’

Step 3a: Fill your options

Step 3b: or alternatively choose your options

Step 4: Link your options with VLOOKUP

Well that’s all, need some improvisation for a better appreance

Download this file for above example

Good luck!

Applied Excel

Find and Replace cells’ formula - increasing efficiency

Cell Options / Validation for Budgeting or Forecasting