0

Is there is a way to 'mark' a cell (or a set of cells) throughout a worksheet or workbook in Google Sheets, such that I can perform operations on these easily later.

The real world application of this is that I have built a 2 year budget, but some costs are potentially not going to be incurred, and thus this budget becomes "free-able". I would like to be able to mark these cells in some way and then easily add up the total 'free-able' cash elsewhere.

The only way I could think to do this would be the colour the cells a particular colour, and then build some sort of IF/SUM that adds up a cell if it is coloured a particular hex value or similar, however from a bit of googling it appears that Sheets isn't capable of knowing what colour a cell is for use in a calculation. Almost a kind of 'inverse conditional formatting'?

If this approach doesn't work, there any other way of 'marking' these cells such that I can easily identify and add them later?

0
1

If the figures of interest are in the same column, insert a column next to the numbers and use a tag like freeable cash in that column by the figures of interest. You can then use filter() to pick up the values when necessary.

If the figure cells are not in the same column, you can get a list of their values by using a formula like this:

=flatten(A2, A4, A5, B3, B6, Z4)

The cell references in the list will automatically adjust as you insert or delete rows or cut and paste cells around, which makes it much easier to use than a textual list of cell locations.

The flattened list is easy to sum up, including summing it up to some max limit. The downside is that the process to "mark" a cell means that you will need to manually add it to the list in the flatten() formula, which takes a bit more effort than simply painting a cell with a color.

2
  • Unfortunately not - as I mentioned these are quite 'disparate' cells, so they can be scattered throughout the sheet
    – Joe Healey
    Nov 24 '21 at 15:06
  • Edited the answer per OP's specific requirements. Nov 24 '21 at 16:19
0

Absolutely! The terminology used is 'naming' as opposed to 'marking'. Specifically 'naming ranges' or 'named ranges'.

A named range could be a single cell or multiple cells. It is an alternative to using the normal cell references you see in formulas and is very powerful tool.

Here is a Google Support article that may be of assistance:

Name a range of cells https://support.google.com/docs/answer/63175?hl=en&co=GENIE.Platform%3DDesktop

4
  • Ah this is getting closer to what I'm looking for I think! Gives me another set of keywords to google at least!
    – Joe Healey
    Nov 24 '21 at 15:09
  • Steady on there - its not quite a complete match to what I was asking. Naming the ranges helps somewhat, but it doesn't actually help with the identifying of the cells - all it really does is save me some typing, but I still have to manually identify each of the cells which would need to be pulled in to the range. Based on some limited testing, the named ranges also doesn't seem to like the disparate part of the question, and only seems to pick up cells which are continuous with one another. I'm pretty sure what I'm trying to achieve may only be possible with Google Scripts in reality
    – Joe Healey
    Nov 24 '21 at 16:06
  • Thanks for the efforts here, and taking the time to write a GScript - I took this as a good opportunity to learn some JavaScript and managed to get something of my own working. I don't think it's worth me opening a new question, but would be interested in seeing the approach you took vs mine given your greater familiarity
    – Joe Healey
    Nov 26 '21 at 15:07
  • Perfect! If you have any other questions don't hesitate to ask. Nov 27 '21 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.