Correct syntax for referencing a separate worksheet in a formula when.

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

Norie,

Thank you so very much. I had to modify it slightly since I gave the information to you in reverse....B25, B26, etc. was actually the destination not the source

I plugged you formula into my workbook and up popped my numbers. I just noticed that the following formulas will not work at written due to the source being percentages. Each range will have multiple percentages that I'll need to retrieve the average of instead of the SUM. Is this possible with a formula or will I need to incorporate VBA?

These are the two formulas that reference the ranges where the percentages are located. Thanks.

=ColorFunction($P$5,RCS!$I$5:$I$120,TRUE)

=ColorFunction($P$11,HCT!$J$1:$J$115,TRUE)

Matthew

Norie,

When I use the function/formula as it is instead of giving me the average of the, let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%. I did try using the AVERAGE in front of the function/formula but that did not produce what I was hoping for. I would very much like to use this method to get the averages but I just can't figure out how to go about it. If you'd prefer, I can mark this thread as SOLVED and re-post about the AVERAGE issue. Just let me know.

I really appreciate the help you've provided. Thanks.

Matthew

Norie,

Thanks. Will start a new thread.

Matthew

Norie,

If interested, here's the new thread. Thanks, again.

http://www.excelforum.com/excel-form...ml#post3703558

Matthew

P.S. BTW, the password for the attached workbook is "j".