Thread: Formulas across worksheets

I have a workbook that has multiple sheets based on the name of the business in order to keep track of how many RVU's each of our doctors read at each location. For the most part I've got most of the locations formatted correctly to that it is easy to populate a total. But I am having difficulty with one worksheet because it has to be laid out differently then the other sheets. The problem worksheet is titled BCC and the formulas that I have manually entered are on the worksheet titled BCC TOTAL. Basically, what I would like to do is enter some formula that would autmatically populate the Sum Total from BCC into the BCC total under for each doctor by date of service.
example;
See BCC TOTAL Column C dates 01/01/2011 - 02/28/2011. I manually entered each of the column C formulas.

Saturday C3 = BCC!C3
Sunday C5 = BCC!C4 (plus one row)
Monday C7 = BCC!C12 (plus 8 rows)
Tuesday C9 = BCC!C20 (plus 8 rows)
Wednesday C11 = BCC!C28 (plus 8 rows)
Thursday C13 = BCC!C36 (plus 8 rows)
Friday C15 = BCC!C44 (plus 8 rows)
Saturday C17 = BCC!C45 (plus one row)
Sunday C19 = BCC!C46 (plus one row)
Monday C21 = BCC!C54 (plus 8 rows)
Tuesday C23 = BCC!C62 (plus 8 rows)
Wednesday C25 = BCC!C70 (plus 8 rows)
Thursday C27 = BCC!C78 (plus 8 rows)
Friday C29 = BCC!C86 (plus 8 rows)


**that pattern continues for the entire month.

How do i write a formula that would populate those formulas without me having to type them all in? I can't drag to copy them becuase the worksheets aren't in the same format. And when I copy/paste the formula the cell references are wrong.

My workbook was too large so I only attached the two worksheets that I am having problems with.


Thank you so much for your time and help!!
Julia

I can't believe no one has responded to this. Sorry. If you still need an answer you can try this formula in cell C7 on sheet BCC Total

=HLOOKUP(C$1,BCC!C$1:C$10000,MATCH($B7,BCC!$B$1:$B$10000)+IF(OR(WEEKDAY($B7)=1,WEEKDAY($B7)=7),0,7),0)

It can be dragged across the columns. Once you have done that you can copy that first row and paste it for any other row you want. Use Paste/Special - Formulas to avoid messing up your Sat-Sun formats.

The 10000 references in the formula can be changed to whatever number you want but it has to be large enough to accommodate your future expansion of the sheet.

If/when the need arises you can highlight a row of formulas and do a Find/Replace to change the $10000 to say $15000 to keep you going OR instead of just adding on to your spreadsheet insert rows whenever necessary and the formulas will auto adjust.

Here is amended version to compensate for the last 3 columns that do not have a name at top. If/when a name is added this will return a result, until then it will return a 0.

NOTE: This is only for Excel 2007 and 2010 and I provide it only based upon your profile info.

=IFERROR(HLOOKUP(C$1,BCC!C$1:C$10000,MATCH($B7,BCC!$B$1:$B$10000)+IF(OR(WEEKDAY($B7)=1,WEEKDAY($B7)=7),0,7),0),0)

The version for older Excel versions is much longer. If needed let us know. We'll make sure to answer more promptly.