populating a table with data associated by Week Ending date

[wtsublette]

Hello All,

Here is the issue I am having... I have a tab with all the associated data and would like to populate another tab (which I can print a report from) with that data according to the Week Ending Date (column G). Data includes names, employee ID, specific date worked, standard hours, overtime hours, standard and overtime rates, area worked, sub area worked and the description of work. All of that information is on a single row. I have tried VLOOKUP but it only sees the first matching date. Not sure how to pull the information that I need and put it into the specific printable format. Any assistance would be greatly appreciated.

Thanks,

Bill Sublette

[bentleybob]

Can you post your file or at least an excerpt from it? Easier to answer the question if we have something to work with.

[wtsublette]

Here is part of the file...

Thanks,

Bill Sublette

[bentleybob]

Ok, thanks. Helps to see it live.

So to further understand what you're looking for, you want a new tab that maybe allows you to input a date for "Week Ending" and it pulls all records with that ending date from the MASTER tab? That's doable.

[bentleybob]

Does this basic approach work for you? I added a helper column in the MASTER tab (col AC) and a new, print tab (Sheet1). Sorray about the zip file, but I was having triuble uploading this to the forum site even though I trimmed it down to well below the max file size.

[wtsublette]

Wow!!! That is absolutely perfect!!!! Thank you very much!

[wtsublette]

I've been testing it and it doesn't seem to want to pull other dates that fall within the Week Ending date. For example on 7/18/10 there should be 51 different entries and for some reason only 2 are showing up. I'll keep working it though.

[bentleybob]

I did note that some of your date entries were text (i.e., had a " prefix), which would make the count lookup fail. Forgot to mention that. You can revise the formula to accept either form of entry. Let me know if you want help with this. A better solution, though, would be to format the entire col H in the MASTER tab to date and then fix the ones that are currently text. Any new entries will automatically be entered in the correct format.

[wtsublette]

Thanks... I also noted the Match keeps looking for a file instead of just pulling from the tab. Is that a function of the "Match" or did I do something wrong... Here's what it changed the formula to...

I renamed the Master blah blah tab to just Master. Here is my formula:

=IF($B13="","",INDEX(MASTER!G$2:G$1603,MATCH($B13,MASTER!$AC$2:$AC$1603,0)))

There are 1603 total lines so I changed it to the appropriate number and I changed where it was starting (B13) and the column that the date is.


Thanks,

Bill Sublette

[bentleybob]

Not sure why MATCH would be looking for a file. But have you now resolved any remaining issues and is it working as expected?

[wtsublette]

I figured it out... I was missing the formula in column AC. That worked perfectly. It does exactly what I needed it to!

Thanks,

Bill Sublette