I have a large Excel file with multiple pivot tables on separate worksheets that we use to monitor issues in construction projects. We have one tab for entry of all of the issues...for example, there may be a question issued from the contractor to us to get more information. Sometimes that will cause us to issue one of three types of change documents, such as proposal request for the added work. In response to our change document, the contractor will issue a formal proposal. Once the value is negotiated and accepted by the Owner, that item will be combined with others in a Changer Order, and a cost category is assigned from a data validation pull down list with categories such as Owner added scope, Concealed conditions, Design issues, 3rd Party added scope, and G.C. Claims. All of that information is recorded in a single row in the Excel spreadsheet.
We also have separate tabs as pivot tables the report the data in the main data entry tab. The one tab I'm having difficulty with is the Change Order Summary tab, where I have five columns for the five cost categories. The goal is to use the cost category selected on the data entry tab to get the change order amount reported on the summary page. I have fields for each category that read as "=IF('Additional Cost Category'="GC Claim",'Change Order Amount',0) which would report the amount of the change if it was due to a General Contractors claim. "G.C. Claim" in the formula is a text value...the rest of field names from the Data Entry table. The result I get is 0 for any case, and while I got no error warnings while finishing the creation of the formula, I suspect if breaks a Pivot Table limitation. I'm not a full time excel author, so can anyone suggest what's wrong, or propose a solution?
Bookmarks