Hi all, I'm new to this forum and fairly new to formulas and conditional formatting etc so any help would be greatly appreciated!
I currently have a 2 sheets, Sheet1 is a 'sign out' sheet for Items that contains several columns that will be filled out by me and my colleagues 1 row at a time, Sheet2 lists all of the possible Items (a fixed drop down list) and their current status.
The data is very similar on both sheets but different people will use them for different reasons so I do need both.
The 'status' (Column G) on Sheet1 is currently controlled by a drop down list featuring 2 options, 'IN USE' and 'IN USE (OUT OF OFFICE)" and is colour coded using conditional formatting to search for a text match and then auto fill the cell with the right colour.
Sheet 1
SHEET1_SCREENSHOT.png
Sheet 2
SHEET2_SCREENSHOT.png
I used to simply colour code the status on each sheet manually to match but this often caused issues due to my own human error so I got it into my head that maybe a formula/conditional formatting could do this for me?
I'm not sure this is even possible, but what I would like is for the cell next to the item name on Sheet2 (the Status column - Column C) to auto fill the colour to match the current status colour when it is changed on Sheet1 - so it's yellow for IN USE and orange for IN USE (OUT OF OFFICE).
I assume the formula will need to check Sheet 1 so that if, for example: Column A says "ITEM A1" and also Column G says "IN USE" then cell Sheet2-C2 should fill colour to YELLOW, if it only finds 1 or none of these criteria then it will leave it blank.
I have managed to get this to work using conditional formatting using the following 2 conditions:
*=AND('Sheet1'!$A3="ITEM A1", 'Sheet1'!$G3="IN USE")*
*=AND('Sheet1'!$A3="ITEM A1", 'Sheet1'!$G3="IN USE (OUT OF OFFICE)")*
But this only works when the information is input into the specific cells (Sheet1) A3 and G3. The problem I have is that the sheet will be constantly added to so I need the formula to be able to search all the rows in Column A to find the correct item name then search the corresponding row in Column G to find a text match there and then fill the cell on Sheet2 with the correct colour!
Can this be done or is this wishful thinking?
Apologies if this is not clear, let me know if you have any questions.
Workbook
KALIUS EXAMPLE.xlsx
Thanks
Bookmarks