Ask Your Question

Background color based on the background color of another cell?

asked 2016-09-16 09:07:17 +0200

zotteken gravatar image

updated 2020-07-10 22:11:01 +0200

Alex Kemp gravatar image

I have a spreadsheet with 2 worksheets. (See Attachment) On one I have an alphabetical listing off all available pokemon. On the other I have a Pokemon Evolution Chart. On the alphabetical listing I mark pokemon I already have with a background/fill color.(magenta in this case) & so I am able to keep track which ones I already have. Would there be a way of giving the pokemon in the Evolution Chart the same color as they have in the alphabetical listing?

Because manually changing that is a time consuming thing... And i'd rather spend that time catching them than keeping track of them...

Thanks for the help... Cheers Zotteken


EDit by mark_t:- Updated your document to use conditional formatting based on an additional column named "Got It". I found it easier to modify your data format, to make conditional formatting formula simpler. As its based on @Lupp's suggestion I added to your question instead of adding my own answer. pokemon.ods

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-09-16 09:29:06 +0200

Lupp gravatar image

updated 2016-09-17 17:02:12 +0200

Add a dedicated column to your alphabetical list where you maintain explicit information of the meaning "I have it", say, an "X" entered if so and left blank if not. Based on this information colour the rows (or parts) of both the sheets by conditional formatting. I did not find information in the question in what way you associate the datasets of the second sheet with those of the list. This is a relevant aspect concerning the reliability of the solution. I even do not really understand for what reason you are using two sheets instead of only one. I would try to make things as simple as possible.

(Editing:) You may check the attached example. It is partly experimental.

edit flag offensive delete link more


There's no specific reason for the use of two sheets. The alphabetical list can very well go below (or above) the Evolution Chart. Maybe that even better for the conditional formatting. Association as in references between the two? No, no association. I'll probably need to add something?

zotteken gravatar imagezotteken ( 2016-09-16 14:22:27 +0200 )edit

@zotteken: You see, I never had a pet of the kind, and I don't know how something like an evolution chart would look. If such a 'chart' is represented in a Calc sheet, what is the actual content of any used cell? If it is simply a name also contained in the longlist, everything is easy.

Lupp gravatar imageLupp ( 2016-09-16 17:31:10 +0200 )edit

Yes, the list contains pokemon 001 -> 151. In the Evolution Chart I use 3 columns per evolution (the name of the pokemon (like in the long list), the candy required for evolving into that level & just a column containing "->" merely meaning evolves into). There's max 3 evolution levels.

So it might look like this: 001, 0, ->, 005, 50, ->, 099, 100 002, ,0, -> 098, 25, ->, 045, 45 etc. where the ", " represents a new column

How do I add my file to serve as example how it looks right now????

zotteken gravatar imagezotteken ( 2016-09-17 15:57:51 +0200 )edit

@zotteken: You want to share the file? I will see for enogh karma that you be allowed to attach files. As files cannot be attached to comments you will have to either edit your question or to post an answer of your own.

Lupp gravatar imageLupp ( 2016-09-24 12:02:49 +0200 )edit

Yeah, I assume that's handier than me trying to explain how it looks like... But if karma is required I'll gladly do what's needed to reach the required karma. By looking at your example file I see some things in mine that I'll have to resolve first, I guess, before the conditional formatting is attempted at all. Like the alphabetical listing has just the pokemon name in it, where the corresponding cell in the evolution chart also contains a number in front (I copied it like that of internet)

zotteken gravatar imagezotteken ( 2016-09-24 12:55:34 +0200 )edit

"... But if karma is required ..." @zotteken: You have already 11 something of it. That's enough to attach files of the acceped types (.ods among them). Simply edit your qustion or create an answer and use the attachment tool (the icon looking like a paperclip).

Lupp gravatar imageLupp ( 2016-09-24 13:16:09 +0200 )edit

OK, edited the question. As you'll see if you open the files, I have put the evolution chart on the same worksheet as the alphabetical order. I also put the references of the related cells next to the alphabetical column.

zotteken gravatar imagezotteken ( 2016-09-24 13:31:28 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-09-16 09:07:17 +0200

Seen: 442 times

Last updated: Sep 24 '16