Nov 08 2021 08:32 AM
Hi everyone
When I enter a formula in a single cell and copy it to the cells below, it computes correctly. However when I force a recalculation (which I have to do as some of the work is dependent on picking up the colour of a cell which Excel does not detect on its own), the formula appears to be modified to an array formula (I think) - indicated by the { } placed around my formula - and the result of all the calculations show as errors (#value). I cannot seem to beat Excel at its game and have my formula left as I entered them. Can anyone shed some light on this problem please.
Nov 08 2021 09:15 AM - edited Nov 08 2021 09:22 AM
Writing a formula that is dependent on picking up the colour of a cell--especially given your qualifier that "Excel doesn't detect [the colour] on its own"--to me sounds like asking for trouble. So some questions:
Nov 08 2021 10:03 AM
Thank-you for your message mathetes.
I have a column where the user can set the colour of individual cells and also enter a value - and I am already using a second column which is populated with a call to my user-function that returns the colour number of the adjacent cell. It is this column that is the problem - it works fine until I recalculate the spreadsheet, then the {} appear around my function and the whole column evaluates to #Value. My end purpose is to calculate the sum of the values grouped by colour code.
I have found a work-around and solution by writing a function that deals with the whole array, filters by colour and gives me the total. More concise and no need for additional hidden columns!
(I still do not understand how to defeat the problem that Excel has caused by this new design feature!)
Thank-you again
Nov 08 2021 12:45 PM - edited Nov 08 2021 12:46 PM
@Sue_L5912 wrote: I have a column where the user can set the colour of individual cells and also enter a value - and I am already using a second column which is populated with a call to my user-function that returns the colour number of the adjacent cell.
(I still do not understand how to defeat the problem that Excel has caused by this new design feature!)
What is the "new design feature" you refer to? There are array functions newly released, but I don't think that they're what you're talking about (they don't involve the squiggly brackets).
Nov 09 2021 03:55 PM
Nov 09 2021 05:04 PM
OK. I would like to suggest an alternative way to approach that visual aid aspect of picking a colour...This is a method I use in one of my spreadsheets to identify common characteristics of some rows by colour:
Since the user has to select something anyway (presumably by taking an action on the toolbar), that same "selecting" can be done by a data-validation drop down (showing the group labels) in a cell next to the cell showing hours, which entry can itself then be used: