I'm an basic to intermediate user of Excel, and recently i've been task with building a large spreadsheet. I've got it built and it works however its a very large file size (66mb). Here is the what the spreadsheet needs to do.
There are multiple columns that the user inputs date or picks from a dropdown list (Data Validation List). There is one set of formulas to calculate if the cell mets the criteria
Example: =IF(C3='referred',1,0)
This formula is repeated once for each of the columns that need to be calculated.
Then I have a formula that looks at the A column for the date, looks at the cell for the previously stated formula, If both meet the value then it gives a value of 1.
Example2: P2 in this example is the date that is updated from a different spreadsheet in the workbook. =IF(And(A1=P2,B1=1),1,0) also I use the formulas similar to this =IF(And(A1=Sum(P2+1),B1=1),1,0)
The value of this formula is updated on the Main spreadsheet of the work book. So I have example2 repeated 7 times, one for each day of the week... for each of the 7 columns of the row, for hundreds of rows, and this is repeated on about 12-15 tabs.
So while this works it leaves the workbook to be very large... So the question is, is there a way to have the formulas set up just one time on one spreadsheet, that will calculate the info and then update the cover sheet on the dated row for the specified spreadsheet?
I hope this is clear, its a complicated set up and I'm hoping to find an answer to this. I'm using Excel 2003