OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Condtional formulas - is it possible?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
eekfonky23
Power User
Power User


Joined: 18 May 2007
Posts: 55
Location: Scotland

PostPosted: Thu Oct 11, 2007 2:37 am    Post subject: Condtional formulas - is it possible? Reply with quote

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?
Back to top
View user's profile Send private message Send e-mail MSN Messenger
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Thu Oct 11, 2007 4:31 am    Post subject: Reply with quote

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. Smile

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.
Back to top
View user's profile Send private message
eekfonky23
Power User
Power User


Joined: 18 May 2007
Posts: 55
Location: Scotland

PostPosted: Thu Oct 11, 2007 5:48 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail MSN Messenger
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Thu Oct 11, 2007 12:10 pm    Post subject: Reply with quote

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

David.
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Thu Oct 11, 2007 1:51 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
eekfonky23
Power User
Power User


Joined: 18 May 2007
Posts: 55
Location: Scotland

PostPosted: Thu Oct 11, 2007 3:48 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail MSN Messenger
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Sun Oct 14, 2007 6:15 am    Post subject: Reply with quote

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... Smile
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group