Lookup formulas to search multiple sheets and return tabled data

I have an Excel workbook that's getting out of hand. I need to take some of the data stored therein and import it into Access, so that I can better manage it. Unfortunately, the data isn't in a nice tabled form, so I can either manually make it so or hopefully use some nifty formulas to get the data in a nice format to import to Access.

In the attached workbook on the "dataimportation" sheet, I have entered some formulas that grab the data I need from the "Region 1" sheet. The only data I need is in the column titled "CO" which is the middle column for each month. In subsequent sheets, I only need the values in the middle column(s), but there may be more than one, with two at the most. I've highlighted the ones I need in the Region 2 sheet. The problem with my formulas is that they are pretty static and won't scale well to other sheets with data in different places. I'm sure there are other problems as well.

Can someone please save me from this mess of data? Are there any other ways to get the data besides formulas? Thanks a million.

excel forum - index.xlsm

Maybe we can start with just the first Sheet. Anyone know how I can get column "CO" for each "saler" by month?

Maybe someone can tell me if my request is ludicrous and impossible?

bump up ttt

So you want the next "Saler" (Fred) to appear in rows 15 to 25 and for his data to appear in the relevant columns, and then the next saler, Tim, to appear in row 26 onwards, and so on?

Pete

You got it. Really regretting my choice of using the "word" Saler

I'm trying to get the ugly data in a nice table form.

Okay, well take a look at the attached workbook, where I've used this formula in C3:

=IFERROR(INDEX('Region 1'!$C:$AF,MATCH($A2,'Region 1'!$A:$A,0)+MATCH(C$1,'Region 1'!$B:$B,0)-8,MATCH($B2,'Region 1'!$C$6:$AF$6,0)-1),"")

This can be copied across and down as far as you need to. I've added the monthly dates to column B by copying the block of 12 cells, and put the next two names in column A - just extend this pattern down for the other names.

I'm not sure what you want to do with the Region2 sheet.

Hope this helps.

Pete

Looks great so far! Hopefully I can adapt it for Region 2. I really appreciate your help for this. It is going to save our department a lot of time!

I forgot to mention that you had the word "apples " (with a trailing space) down column B of the Region1 sheet, but only "apples" (without a space) in C1, so to ensure they matched I added the space to C1.

Pete

Hey, thanks for the rep - I like the comment you left !

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete