Forum Home
Press F1
Thread ID: 29857	2003-02-04 11:37:00	Excel IF function and cell formats	rugila (214)	Press F1

Post ID	Timestamp	Content	User
118352	2003-02-04 11:37:00	Sometimes I want to select disjunct (separated from each other) cells from an Excel spreadsheet to do arithmetical operations on them. Eg. say select cells B2, J18, and Q203 and add their contents, ignoring all other cells. I know =B2+J18+Q203 is one way, but this requires re-entering the formula each time which can be a long job with a lot of cells.. My approach is to mark the cells by some format such as boldfont them or interior.color=32 or whatever. Does anyone know if the IF function works with formats as well as with cell contents? That is, Excel works OK with things like =IF(A1>0,A1,"") where you have a non-null entry equal to A1 content only if A1 is positive. Can you use something like =IF(A1.interior.color=32,A1,"") to have a non-null entry equal to A1 content only if A1 is marked with the chosen colour format? It's easy enough to write VB macros to do this, but I want to know firstly if the IF function is able to do it at all, and secondly if so then how. I haven't found a way yet.	rugila (214)
118353	2003-02-04 21:31:00	Not sure about the IF function but I see two easier ways (depending on just what you are trying to do) 1st Instead of "=b5+c8+q10..." try "=sum( <then hold down the CTRL key and select each of the cells> )" or 2nd Select the cells and then name them. Then you can use "=sum(name)" It is easy to add another cell to the name at any time. HTH Cheers Shroeder	Shroeder (492)
118354	2003-02-05 06:43:00	Hi rugila Have you played with conditional formatting. It's under the format menu. You can make a cell or it's contents behave in the required way if the cell has the specified value. Is that of any help. Cheers Craig.	Craigb (688)
118355	2003-02-05 08:34:00	Appreciate your replies. But what I specifically want is to mark cells by specific formats and want to know whether or not Excel's =IF() function works with =IF(cellformat=??,,) as well as with =IF(cellvalue=??,,). I can do workarounds OK, but am here mainly looking at trying to get a definite "yes" or "no" to whther IF works with formats.	rugila (214)
118356	2003-02-06 22:17:00	Hi Ruglia, I would give it a definite no with the way you described using the IF function. However there are other ways to do this ... www.cpearson.com for some VBA Procedure on how to look for colours www.cpearson.com for creating your own custom function (eg a formula) that can return the colour. I believe you could build your own custom formula to return a value and then use this as an argument of an IF function.	parry (27)
118357	2003-02-07 01:20:00	Hi, I suppose I should add some general info on functions versus sub procedures. As noted in the first link in previous post, there is no existing function in excel that deals with formatting however you can create your own that can lookup formats in a cell and return a value. Functions must always return a value and cannot amend the environment. eg you cannot amend the formatting or value of a cell based on a formula - use a procedure if you want to do that. You can of course use a formula within conditional formatting but that is effectively putting the formula as a argument within a pre-built sub procedure. You can use the function in the second link as is to return a number indicating the colour index. If you decide to use this within an IF function then be aware that IF will only be able to cope with a maximum of 7 varations (ie 7 nested IF's). It may be better to include functionality within your custom function to sum cells if you have lots of colour/font iterations or use a vlookup etc. cheers Parry	parry (27)
1