It is my goal to develop well structured, organized, user-friendly, and easy to maintain spreadsheets to give out well presented and meaningful information.  I once read somewhere that a hallmark of a well developed spreadsheet is that data should be in a list form separate from the output. The data then should be manipulated to achieve the desired output. This is exactly what I want to achieve, however, with a bit of automation.

With that intro, explanation on the spreadsheet is as follows:

(1)  1) The attached spreadsheet is a detail analysis of progress billing and Work in Process (WIP) along with reconciliation between the GL and the sub-ledger.

(2)  2) Tab ‘Summary’ is the desired output. There are primarily three sections in this tab. The top section contains the GL balances for the related GL accounts. The middle section is Sub-ledger information related to the GL accounts. The last section is the variance between the GL & SL.

(3)  3) The sub-ledger section has again three sections:  customers with Canadian $ billing, customers with US$ billing and customers with NO billing but only cost. (The only twist here is that sometimes a customer/project might have multiple work orders, many being just cost accumulation WOs and no billing would initiated against such WOs. I will have to include manually. For the learning process this twist can be ignored).  Each of these raw data billing sheets can have upto 1,000 lines of data.

(4)  4) The three tabs, namely, ‘C$ Progress Billing & WIP’, ‘US$ Progress Billing & WIP’, ‘WIP with NO Progress Billing’ are the intermediate steps for categorizing, combining, summarizing & totaling the two sets of raw data. This requires the bulk of my manual intervention. I would like to either automate these intermediate tabs or completely eliminate them. Whatever you suggest!

(5)  5) The common field for the raw data is “Customer” and “WO#”.

(6)  6) Taking tab ‘C$ Progress Billing & WIP’ as an example you will notice that I just combine the lines from the progress billings with the lines from WIP for a customer. Please note that I am unable to combine or put on the same line the progress billing amount and the WP values.

(7)  7) Once the intermediate tabs are ready, I then manually copy the customer names to the summary sheet in their respective category and link them to their values in the intermediate tabs.

(8)  8) Every month there is addition to and deletion from the raw data.

Wish List:

As the raw data is dynamic, the automation that I need is to be able to pickup data, such as Customer, WO#, progress billing value and the cost value from the progress billing and the WIP raw data and summarize all that in the intermediate sheet without much of manual intervention. The information from the intermediate sheet would then roll up in the summary sheet automatically. Any changes in the raw data would accordingly change the intermediate and the summary sheet.

From: Shahid Jameel

Workaround

I have been able to figure out a workaround to our problem at hand after a fair bit of struggle and a great learning experience.  Please find attached the workbook with my solution.  My solution is given in sheet "Summary (Automated)".  The other sheet i have added is "Ashish Analysis" which is the machinery behind the summary sheet.  You may hide this sheet if you wish to but please do not make any changes to it.  I have tried my level best to ensure that you would never need to go to this sheet.  All you have to do is keep adding your records in the two input sheets.  I have actually been able to do away with the intermediate sheets as well. 

Please also keep the following in mind:

1.  Since there are a lot of formulas running in the background, it takes some time (a few seconds more than normal) for the workbook to recompute when you add a record.  So the output in the "Summary (Automated)" sheet may not be visible immediately.  I sugest you view the output when the lower left portion of the workbook reads "Ready".

2.  All you have to do is keep adding records in the two "raw data" sheets. The range A11:L100 in the "Summary (Automated)" sheet is absolutely automated i.e. it recalibrates on its own for new records added.  Everything right from record additions to formatting are automated so you would not have to copy paste at all.  You may make changes to the range A1:L10 in the "Summary (Automated)" sheet.

To ensure proper functioning of this model, please ensure the following:

1.  Do not insert or delete any rows or columns in the two raw data sheets.  If you wish to delete a record, please delete the contents and not the row (Do not worry about the blank rows)

2.  I have replicated your "Summary" sheet in my "Summary (Automated)" sheet except for the row which carries the "Difference b/w GL & SL".  It was difficult to add this row due to Excel's limitations of the number of nested IF's which can be used.

3.  Do not make any changes to the "Ashish Analysis" sheet

Please review this workbook and give me your feedback.

 Question 4 - Summarising based on multiple conditions 

 

I have been unable to come up with a formula that will lookup both multiple conditions and multiple options.  I need the formula in the hours column to lookup the information in the hours worksheet against both the name and activity.  If both are true, then input the correct hours.  The possibilities are multiple activities for one employee and also multiple employees for one activity.

 

From: Beth Kettenring

 

Workaround

 

Hi, please find attached the workbook which contains my solution in range I2:I157 of sheet "Work Copy".  It is an aray formula solution (confirmed by Ctrl Shift Enter) as against the conventional Enter.  You may replace the values in column D with the formula in column I.

---

 Question 5 - Simplifying worksheet inputs and querying

As you can see there are is a worksheet ("Seller A") belonging to different sellers of the goods sold to different buyers. There are invoices, Payments, Part payments, Debits, Credits, Commission, Added commission. I am able to maintain this worksheet manually but it is very tiresome when I have to add part payments. I have to manually add rows for part payments. When a new buyer enters I have to manually copy paste the rows and coloumns, apply formulas etc. Actually very tiresome to maintain it, eventhough I must admit that I am absolutely going perfect in maintaining my accounts in this worksheet. Everynow and then I have to look for payments to be received from a particular buyer for all and a particular seller. I have to search for payments due and not payed. I have to search for accounts payable to a particular seller from a particular buyer or all buyers. No of bales sold in a month Commission to be received from a seller I am also going to add interest payment coloumn on a later stage.

Please give me your improvisations and suggestions. Is there a way where I can add forms for everything? Should I maintain all transactions on a single sheet? I want to be able to generate reports and thereby easily take printouts. Rightnow I have to manually select, hide coloumns to take printouts.  I am otherwise thinking of paying to make a customised software of the same.

From: Rajesh Bhansali

Workaround

As i correctly understand, there are two major problems you are facing.
 
1.  Inserting rows for buyers who make part payments and copying all formulas time and again (taken care of by worksheet redesign); and

2.  Running queries such as determining the invoices raised on a particular buyer by a particular seller etc.
 
Let's try to address each of your concerns now. 
 
1.  Worksheet redesign - For this, please emphasise on range A5:Z5 downwards.  Do not bother about any rows above (we will come to that later)
 
a)  I suggest you maintain the spreadsheet (as done in Ashish solution) in a manner such that you do not segregate the details buyer wise.  Do not insert rows in situations where the buyer makes a part payment against any invoice - instead just keep entering data as and when you receive it. 
 
b)  The formula in range R6:R500 would automatically search for payments made by a buyer and debits against a particular invoice in all the rows below and accordingly return the amount due.  I have tried to depict the same through an example i.e. Please notice row 7 and row 10; row 10 depicts the part payment of an invoice which is booked in row 7.  The "Balance" and "net received" columns depict the appropriate amounts. 
 
c)  As regards receipt of part payments, please remember to enter information about "Seller", "Buyer", "Invoice number", "payment date", "amount paid" and "debit" (In fact this is what you have also done in your "Seller A" worksheet w.r.t "payment date", "amount paid" and "debit".  I am only requesting you to enter "Seller", "Buyer", "Invoice number").  Also for part payments, please do not enter any amount in column I (Again, even you have not entered this information in sheet "Seller A"). 
 
d)  Also, i have coloured ranges O6:S500, V6:V500 and Y6:Z500 to depict that these carry formulas.  Please remove these colours, if you so wish. 
 
e)  The formulas have been extended till row 500 only - please feel free to copy to more rows them as and when your data exceeds 500 rows. 
 
f)  Also, please lay particular emphasis on the formulas in range R5:R500 - these are array formulas which are confirmed by Ctrl Shift Enter as against the conventional enter.  Also, as and when your data exceeds 500 rows, please remember to manually increase the range of B, C and L in the formula formula in column R
 
2.  Running queries
 
I have provided the functionality to run queries at two levels.  You may use filters (depicted by down arrows) in rows 4 to cull out column specific data.  To know more about filters, please refer to Excel's help menu.  You may run another form of query (in range A1:Z2) which, in my opinion, is more robust than the one just described. I have basically used Excel's database functions to do this.  You will observe the "DGET" functions in the following cells - F2, I2, L2:M2, Q2:T2; V2:Z2.  The cells marked in green (range A2:E2) are input cells for querying.  I have depicted an example by entering some data in the input cells.  When no data is entered in the input range, it implies that the search is in the entire column i.e. if A2 is left blank, then the database functions would yield results from all sellers.

Please download the workbook from here.

 Question 6 - Searching across two worksheets

In the other 2 Workshhets attached PF-1D Roc and PF-1D-Roc-2,beginning on 4-15-05  the daily price change (1D-Roc) for all 25 sectors is recorded. The Symbol for each of the 25 Sectors is found at the top of worksheets  for each of  the 25 sectors.

For Example:  In the worksheet called Daily Selection, On 4-15-05, the symbol for the Current Day Selection is manually entered in Cell B4. I want to return,automatically, in Cell D4 the 1D Roc for the Symbol (out of a possible 25) entered  in Cell B4

From: Lou detrader

Workaround

Please find herein my workaround to the problem

 Question 7 - Return highest from a number of columns