Referencing multiple tables

It sounds like you have the data in almost the right format. However, to take it further, we'd need to see a sample workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

This was not too bad. You had the data well-organized and that's always a good start.

I had to modify the calendar a bit. I noticed that when I changed months, it broke and I could not get May to come back. However, it was a minor fix. I unhid all the rows and columns on the calendar. So I could see what went on "under the hood." I left them unhidden so you can see what's going on too. For cosmetic purposes you can hide what you do not want to see.

I added an extra formula in cell F4 - I needed the person's name, so I did a VLOOKUP against the employee list.

Since we are dealing with the same formulas applied to multiple sheets, I made extensive use of INDIRECT. One such formula is in Cell Q4: =MATCH(F4,INDIRECT($Q$7&"!B:B"),0) - This finds where the the person's name appears on the monthly sheet.

Since we are using INDIRECT, the monthly names in Cell A5 must match the tab names, so I have a helper cell in Q7 to truncate the full month name as displayed on the calendar to match the tabs.

The main "meat" of the formula is =IFERROR(OFFSET(INDIRECT($Q$7&"!$A$1"),$Q$4-1,MATCH(A9,INDIRECT($Q$7&"!9:9"),0)-1,1,1),"") What this formula essentially says is go to Cell A1 on the monthly sheet, Go down to the row containing the person's name, find the column associated with the date in row 9. Give me the result of the intersection.

There is a slight issue with this formula: if the intersection is blank, for some reason the formula returns zero instead of blank. There are a couple of ways to handle this. The one I opted for was to move the formulas "off to the side" in columns S:AE. Then I could use the relatively simple formula: =IF(S10<>0,S10,"") to fill in the calendar.

P.S. Is there a reason you have this in pre-2007 format (XLS)? If you save it as XLSX, you could use Excel tables.

One of the advantages of Excel Tables is that they "self extend" meaning that when you add a row at the bottom or a column to the right, they become part of the table. This means that tables know how big they are, so any charts, pivot tables or formulas that reference them do not need to be changed when data is added or deleted from the table. So you don't have to do something like =SUM(B:B) to look at more than a million rows or =SUM(B2:B100) and hope that the data doesn't go beyond row 100. Instead you can use something like =SUM(Table_Data[Column_Name]) and Excel knows exactly how many rows are in Column_Name.

Another advantage of tables is that the copy down formulas, formats and validations automatically as rows are added. No need to "pad" them manually.

This might come in handy on the Monthly Sheets and on the Employee Sheets. If you are adding and deleting a lot of employees, the formula you now have in column K can get cumbersome to maintain. You could probably come up with a formula to find the employee in the various tables and do the summation.

Here is something to get you started on Tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

Indirect takes a string that looks like it should be a range and converts it into an actual range. So if Cell B1 contains the name of a month, for example May then "'"&B1&"'!A:A" translates to 'May'!A:A. So I can do something like =SUM("'"&B1&"'!A:A") and I will get the sum in column A on the sheet whose month is in cell B1. INDIRECT was invented pretty much for this reason (switching sheet named dynamically).

IFERROR has the syntax of IF(This expression has an error, Return this value) - otherwise, it just evaluates the expression.

Prior to Excel 2007, "escaping" an error took a lot more work. For example, if you were trying to do =VLOOKUP(A2,Some_Range,2,False) and the value in A2 isn't in Some_Range, you get a #N/A error. If you did not want to show the error then you would have to write:
=IF(ISNA(VLOOKUP(A2,Some_Range,2,False)), "",VLOOKUP(A2,Some_Range,2,False)) Which not only is a long formula, but it also means Excel had to compute the VLOOKUP and then determine if it evaluated to #N/A and if it didn't compute the VLOOKUP again.

=IFERROR(VLOOKUP(A2,Some_Range,2,False),"") might do the same thing behind the scenes, but it's a lot less typing. However it will return the null string on any error such as #VALUE (which is what I think you get if you try to reference a column beyond the range).

Similarly, you used to have to check the denominator in a division =IF(B2<>0,A2/B2,0) - now you can use IFERROR(A2/B2,0).

Finding a value in a range is what MATCH is for. =Match(This value, in this range, flag). If the flag is zero, match looks for an exact match and returns where, in the range of values the value is found (usually the row or column number) - or #N/A if not found.

With a flag of -1 it looks for the value and if it can't find it, it goes to the next lowest value. With a flag of +1 it looks for the value and if it can't find it, it goes to the next highest value. However, in both cases, the data has to be sorted by what you are searching on.

The only other trick I used was OFFSET, and this I do have documented: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges - I probably could have also used INDEX instead. The two commands are closely related.

The discussion on Tables was a suggested improvement. Tables are not involved with the solution at all.