Condtional formulas - is it possible?

[eekfonky23]

I have a series of linked workbooks. I have to change the formulas relating to the font colour of the cells. It's for a wine business and to put certain wine into a mixed case the formula for that wine will change accordingly. Rather than go through 7 workbooks trying to change each formula I would like to determine it from the parent workbook. e.g if I change a font colour can that change the formula automatically?

[David]

I don't know what you mean by a "formula for that wine", so you might be a little more specific? Also, although a colour can be determined by a formula [conditional formatting] I don't know that it works the other way around. But for a case of wine, I'd work on it.

What you need might be available in the macro conference, but you might first be a little more specific for the good folk here to be able to help.

David.

[eekfonky23]

If a wine is in a mixed case the formula for it changes. I would love to send you some wine for helping but unfortunately we only deliver in mainland UK. Haven't reached Canada yet...sorry

[David]

This sounds very much like you'd benefit from using a relational database instead of a spreadsheet.

David.

[keme]

Some format info is available through the CELL() function, but not character specifics (like font face, colour, decoration, etc.). Anyway, as those can be applied both at cell and character level, and similar colors may have different names, I guess you'd have trouble getting consistent results with this approach.

I suggest you have a selection cell with valid values (those being colours if you like) beside your data cell, and base the calculation on that. You mention 7 workbooks and a parent. If you can be more specific on what you have and how you use it (like: what colours will be used?), we can be more specific on what might solve your problem.

[eekfonky23]

I can send you what I have as workbooks. I do realise I should be using a database and am currently trying to implement this. The problem is that I do need 2 systems right now as, running a small business I need 1 to work (spreadsheets) and 1 in development (database). I, however know nothing about databases and although the spreadsheets work they are cumbersome. My problem is easier to show than to explain. If you can help, great, but if not, no problem as I understand this is quite a major task unless you can do macros (e.g. VBA) I only have limited experience and my programming is not what you would call efficient. lol

Anyway, let me know

Thanks

Chris

[keme]

I offered private assistance, to avoid publishing possibly business critical documents, and the OP sent me his worksheets. I'll outline the solution here for the sake of completeness.

The colouring was not the primary data entry for this case, as could be expected. Some cells were coloured to show that the wines identified by those cells were in a particular selection group. That selection group was available in tabular form (a separate sheet in one workbook).

The solution was, then, to use VLOOKUP() to inspect the table. That function uses the same parameter for both indicating a sorted table and requiring an exact match. (Unsorted = requires exact match.) When requiring an exact mathch, no match will return an error.

The complete solution was to add +IF(ISERROR(VLOOKUP(C5;'file:selections.ods'#$Mix.<SelectionGroup>;1;0));0;<selection amount>) twice to each formula (there were two selection groups). This resulted in a rather lengthy formula, doing a lookup several times, copied to several hundred cells. I guess that number of remote lookups (to data in a different workbook) may have an impact on performance, but the response was that "it all works now", so I guess that's acceptable.

I suggested using conditional formatting, essentially using the same ISERROR function as above, instead of colouring manually. I also suggested inserting a linked range to the selection groups, and using that for the lookup instead. That may speed things up a little, but I don't know if the OP did it.
Further optimizing can be done by using the numerical representation of the boolean values, as simple math is normally faster than a function call.
...+(1-ISERROR(VLOOKUP(C5;'file:selections.ods'#$Mix.<SelectionGroup>;1;0)))*(<selection amount>)
That's much harder to read than the IF() function, though, and the time saving is probably minimal compared to the lookup, so I didn't suggest that.

As for the reference to "UK mainland" (in case the OP is still around), I'll be attending BETT in London in January...