Hi! I'm trying to figure out if this is possible. I have two spreadsheets containing a list of items within categories. The same items are in each category; the only difference is the name of the category.
For example, if the categories are Red, Blue, and Black, the items under each category might be Shirt, Pants, and Dress. Using this example I made a spreadsheet showing which Red, Black, and Blue items I have - Shirts, Pants, or Dresses. When I have the shirt, pants, or dress of a certain color I change the color of the cell. When I don't have the shirt, pants, or dress of a certain color I do nothing to the cell. There are no values in the cells; just colored cells for positive and plain white cells for negative.
In my second spreadsheet, I want to do the opposite of the first. I want to see which shirts, pants, and dresses I DON'T have of the colors red, blue, and black (using the above example). Instead of having to manually color the cells in the second spreadsheet, I want it to look at the first and apply cell colors to the cells from the first spreadsheet that are white. I tried to use conditional formatting but can't seem to find a way to assign a value to a cell based on its formatting. If I assign a value of "1" to colored cells and "2" to blank cells, for example, the second spreadsheet can use a duplicate of the first table but only apply formatting to the cells with a value of "2" from the first spreadsheet.
As a shorter way to do this, I would prefer to just have the second spreadsheet format itself by looking at the formatting of the first, without having to apply a value to cells of different colors/formats.
Is there a formula/function in Excel that can look at a cell's formatting and either apply a value to it or format another cell based on the format (or lack thereof) of the first cell? I've attached an example picture to show what I'm trying to do. Can someone please give me some advice? Thank you.
Bookmarks