Excel use of system resources

I am creating a sheaf of spreadsheets with dozens of worksheets each, and each worksheet will contain a LOT of comparison functions. This job is big enough that I have worries about the system resources needed by its operations. Each comparison by itself requires very little, but there will be thousands by the time this thing is finished. And, each worksheet will contain a large number of cross-references, where cells in one sheet draw data from several other sheets. The time needed to save this thing is a concern and I'm beginning to wonder if RAM use is becoming excessive.

This spreadsheet is supposed to work like a flow chart - the path that the reader has to take branches, depending on his responses. Each worksheet contains several cells that have pull-down lists, where the reader is supposed to pick Yes/No answers to questions. The answers to those questions determine where text appears lower in the sheet, and what that text is.

For example, suppose there is a question in cell C16, and a Yes/No list in cell C17, and rows D and E are blank. If the reader selects "Yes" in cell C17, cell D16 shows another question, and row E remains blank. If he selects "No" in cell C17, cell D16 displays a prompt to skip that row, and cell E16 shows him a third question.

So, each worksheet contains dozens of cells where the content is conditional in two ways. In the example explained above, the content in cell D16 is supposed to remain invisible as long as cell C17 is blank. And the further questions are conditional depending on the exact answers. If cell C17 shows "Yes," cell D16 shows Message_1, and if cell C17 shows "No," cell D16 shows Message_2.

Here's an example of the branching structure used in a case like this:

=IF(C17="","",IF(C17="yes","Message_1","Message_2"))


The part =IF(C17="","" ensures that the cell remains blank as long as cell C17 is blank. The rest provides two possible entries, and which one is shown depends on what is selected in cell C17.

The idea of not hiding text is not an option - the client specifically wants it set up so the reader is not shown a possible branch at all if he chooses options that do not point that way.



My first question is, which way of making the text conditionally invisible uses fewer system resources? There are two ways of hiding the text:

1) Use the formula shown above, with the ="","" specification.
2) Use this formula: =IF(C17="yes","Message_1","Message_2") and for that cell, set up conditional formatting so the font color in cell D16 is white if cell C17 is blank.

Both approaches basically require Excel to run two comparison operations, but does one approach require significantly more RAM or processing cycles than the other?

Or, does the number of comparisons not really matter, and the sheer size of the spreadsheets is the only real worry? There are three spreadsheets that must work together. Their sizes are 846kb, 387kb, and 149kb. Respectively, they have this many worksheets: 58, 17, and 12. I expect each of them will more than double in size before the job is finished.




My second question is in the case of multiple conditional comparisons to determine if cell content is hidden. Here's an example of something that I've had to do a lot:

Cell E16 should show text if cell C17 shows the word "Yes." But sometimes, it should show text if cell D17 shows "No," even if cell C17 does not show the word "Yes." Below is a formula that satisfies the need for comparisons:

=IF(C17="Yes","Message_1",if(D17="No","Message_1","Message_2"),"Message_2")


Here is another way this could be done:

=IF(OR(C17="yes",D17="no"),"Message_1","Message_2")


Which of these is more efficient in terms of system resource use?



I've attached a small file that shows a slightly-closer-to-real example of the things I'm doing in this spreadsheet.

Actually, you haven't written anything I haven't already thought about, but...

First choice: It's my job. I'm supposed to tell the guy who signs the checks that I can't do what he wants and he should fire me and hire someone else? I don't think so. I can do what he wants, it will just be uglier and slower than what a programmer could make.

Second choice: No got VBA in knowledge set.

Boss wants a spreadsheet. I want employment. If the only way I can make this work with my skills is to produce bloatware that runs 8 times as slow as what a VBA guru could produce, so be it. He already knows it's an ugly monster. I've told him it's an ugly monster. The worry about the performance is mine, not his. My fear is it will work while I'm writing it and then crash and die when he tries the final version. And he wants to have this used by other people in the organization and I have to assume some of them will have systems that have less capacity than my machine.

You mentioned that conditional formatting is volatile. What's the deal there? I've never had any problems with it. But I have never used it in a bloated pig of a spreadsheet like this thing.