Bdtyktl

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

Am I'm understanding correctly that you would like the same set of formulas on one sheet to variably reference the values on other sheets (including those on other workbooks), based on some condition (such as a particular data). That would certainly be possible using the INDIRECT function.

For example,

A1 = "WorkbookA.xlsx" 'These values could be made dynamic by having them be the
'result of a pick from a dropdown menu. Doable, but perhaps
A2 = "Sheet1" 'overly complicated in practice.

A3 = =IF(INDIRECT("["&A1&"]!"&A2&"F14")=TRUE,1,)

If you would like the same set of formulas to actually generate a set of results, with one row deriving from one sheet, another row deriving from another sheet, then the only non-VBA possibility I see would be using a two-way data table (normally used for "what-if" analysis), where the inputs could be different sheet names or dates or whatever, and the calculated amounts would again make use of the INDIRECT function. The catch there is that you might end up needing a separate data table for each output formula. Might also incur long calculation times.