|There is no formula I know of for getting the sheet name, which means you will need some type of VBA.|
I am not good at VBA, but try this:
This is a three-step process,
First: create code that will get the formula from a cell,
Second: cut up the formula from step one into something we can use,
Third: use Conditional Formatting to color the cell.
First the VBA Code for GetFormula
Function GetFormula(Cell as Range) as String
GetFormula = Cell.Formula
The above code is not my creation, I obtained it at:
Now to install the above code:
First, open your workbook in Excel and bring up the Visual Basic Editor by pressing Alt-F11,
There should be a Project window on the left,
A large Code window, and an “Immediate” window at the bottom.
If not then use Ctrl+R, to bring up the Project Window
Within the Project Explorer window, select your project, which is your workbook name.
If you do not see modules in your project (workbook) then on the Task Bar, invoke the Insert (menu), and then select module.
Copy and paste the above code into the module that you just picked.
Save and exit the Editor.
You can now use the =getformula() function in a cell.
Second, with your linked data in cell A1
in cell B1 enter the formula: =getformula(a1)
it should return: ='Beach Carnivals'!A1
Now, since all we want is the sheet name, we have to slice and dice a bit to get what we need.
Just so you can see what's happening,
In cell C1 enter the formula:
This should return only the sheet name Beach Carnivals which is what we want as we will be using this formula in our Condition Formatting.
Third, the Conditional Formatting.
I'm not sure how Excel on Mac has its Tool Bar arraigned but try this:
1st - Highlight your data cell, Cell A1
On the Menu Bar:
2nd - Format
3rd - Conditional Formatting
4th – Change “Cell Value is” to “Formula Is”
5th – Enter the formula:
Sub Menu Format:
6th - Patterns
7th - Select the Color Yellow
8th - Press OK
9th - Press Add
10th – Change “Cell Value is” to “Formula Is”
11th – Enter the formula:
Sub Menu Format:
12th - Patterns
13th - Select the Color Blue
14th - Press OK
15th - Press OK
If all went according to plan, the cell A1 should now be colored Yellow.
(DerbyDad03 & Humar, if you see any mistakes, your help is always appreciated.)