My spreadsheet has analysis sheet in the front fed by numerous data sheets. The data sheets are named by dates (typically quarter ends e.g. 3/31/05, 6/30/05, etc.) The data sheets analyze construction projects as they progress. New data sheets are added as time progresses. The analysis sheet allows the user to look at a single project over time. The analysis sheet has three columns headed "Original Contract Price", "Adjusted Contract Price" and "Original Gross Profit". I would like a formula (preferably non-VBA) that can look across the row and find the first column in which real data appears ("non-data columns have "" in them) and then go to the correct sheet to pull the "Origninal" data and another formula that looks at the last column in which real data appears and then go to the correct sheet to pull the "Adjusted Contract Price". Clear as mud? I have attached an HTML page of the analysis sheet showing the headings and one job. There isn't room to include one of the data sheets. If if is permissable within the rules of this forum I would gladly e-mail a copy of the workbook to anyone that wants to see the whole deal. I appreciate any assistance.
| TestWOH.xls | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Acme Construction Company, Inc. | |||||||||||||||
| 2 | Job No. | Project | Original Contract Price | Adjusted Contract Price | Original Gross Profit | Date | 03/31/2004 | 06/30/2004 | 09/30/2004 | 12/31/2004 | 03/31/2005 | 06/30/2005 | 09/30/2005 | |||
| 3 | 15 | Office Building | $ 476,000 | $ 476,000 | $ 95,875 | % Comp. | 49% | 89% | 100% | #REF! | ||||||
| 4 | Profit | $ 95,875 | $ 96,800 | $ 96,800 | #REF! | |||||||||||
Analysis | ||||||||||||||||
