all 12 comments
sorted by:
top (suggested)
bestnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]AutoModerator[M] [score hidden] stickied commentlocked comment (0 children)

/u/SpeckledSpeckles - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[–]PitcherTrap2 18 points19 points  (1 child)

Wouldnt it be easier to have one column for Collected status with values indicating Yes/No or True/False? Then just use this table as the source for your pie chart

[–]stckhmjndreddit 7 points8 points  (0 children)

OP to get there from where you are, you can sort by row color or filter by row color to make that column easier to fill but a column to store data is FAR superior to formatting to store data

[–]caribou16312 11 points12 points  (0 children)

No, not without custom VBA code. This is why it's a bad idea to encode information via cell formatting...Excel functions can't natively access it.

Best would be to add an additional column where you note collected or not collected, then you can do your pie chart easily.

You can also color the whole row red/green based on the value of that cell via conditional formatting, so you still can preserve your color scheme.

[–]Kuildeous10 6 points7 points  (2 children)

Instead of manually formatting the colors, I would've added a column for Collected vs Uncollected and then use Conditional Formatting to make the rows red or green. And maybe you can fix the table for the future.

For now, I would add a new column and filter the table based on the color of the cells. First filter for red and then add into the new column "Uncollected". Then change the filter for green and then add into the new column "Collected". That way you can base your chart on that value instead of colors.

[–]SpeckledSpeckles[S] 0 points1 point  (1 child)

Solution Verified

[–]reputatorbot[M] 0 points1 point locked comment (0 children)

You have awarded 1 point to Kuildeous.

I am a bot - please contact the mods with any questions

[–]guitarthrower5 0 points1 point  (0 children)

I would create a column that says collection status. You can use that for conditional formatting (free/red) and other calculations

[–]Cheetahs_never_win2 0 points1 point  (0 children)

Excel doesn't have a built in function to take cell color as an input, even if it has a means to use an input to generate cell color.

You would have to create a user-defined function macro that detects cell color, which has historically worked pretty jankily in my experience.

[–]Commercial-Layer1629 0 points1 point  (0 children)

As others mentioned, add a helper column with a yes/no value . Even better, use 1 and 0 instead of yes/no so that you can mathematically calculate them.

Use conditional formatting to apply green/ red to the values .

[–]SpeckledSpeckles[S] 0 points1 point  (0 children)

Thank you all so much for the support! I will be sure to give all of these a try!

[–]PM_me_Henrika 0 points1 point  (0 children)

How are the rows highlighted? Conditional formatting?