Good afternoon. I'm working on a workbook that will track staffing patterns.
The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden".
On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:
The following are pulling data from Sheet1 "RCS"...B25, B26, B27
=ColorFunction($B$25,$C$1:$C$115,TRUE)
=ColorFunction($B$26,$D$5:$D$115,TRUE)
=ColorFunction($B$27,$G$5:$G$115,TRUE)
The following are pulling data from Sheet2 "HCT"...B15, B16, B17, B18
=ColorFunction($B$15,$C$5:$C$110,TRUE)
=ColorFunction($B$16,$D$5:$D$110,TRUE)
=ColorFunction($B$17,$E$5:$E$110,TRUE)
=ColorFunction($B$18,$H$1:$H$110,TRUE)
The problem is, when I moved the "data collection tables" from Sheet1 "RCS" and Sheet2 "HCT" to Sheet3 "Hidden" the formulas above will not work. I know I need to somehow reference Sheet1 and Sheet2 but all my attempts have thus far failed; ', !, *', [], (), etc.
I appreciate any feedback you might be able to offer to me. Thanks.
Matthew
Bookmarks