I can't figure out this formula and recreate it

[wednesday77]

I have recreated a workbook that we use at work to input data gathered from different websites. I finally thought I had the raw data page formatted. The first sheet in the work book is a summary page that extrapulates the data from the raw data sheet. It looks at the date, and matches the data and pulls the corresponding data.

This is the formula, when it pulls the data it gives me a number like it is working but it is obviously counting too much or pulling the number from I don't know where, honestly I am not sure what the heck it is pulling. That number is nowhere in the raw data sheet.

=OFFSET('VRA data'!$A$2,$9:$9,MATCH($A11,'VRA data'!$2:$2,0)-1)

The original formula that I fixed from another workbook was:
=OFFSET(UA.com!$A$2,$9:$9,MATCH($A11,UA.com!$2:$2,0)-1)

Honestly this doesn't make sense, the cells don't match up with corresponding data so I am not sure how the original author did this.

For instance in the WB, on the summary sheet the first calender date is in cell A11, in the sheet I want the data pulled from, the first set of data I want is total # of items for the same calender date as is in A11 is E3 (in the raw data sheet), then I want different data in the next 4 cells in the same row of the summary sheet row 11 all pulling from the date in the raw data sheet. The cells of A2 (or rather all of colunm A) in the raw data sheet are blank with a color, because I was told that needs to be a blank column so the formula stops reading. The issue is that every week, a new date is addeds, the 15 rows are copied then inserted (including the blank column), then the new data is input with a new calender date and new numbers so the old data always moves back several columns with each new submission, hence why I need to match the dates.

I thought I was OK at excel but this is beyond me. I really appreciate any help someone could give me. AND I am going to take some additional classes to help with macros, dynamic tables, and arrays, etc if anyone could suggest where to go besides my local community college!

 

[mctopher]

Are you able to post the workbook you're using, or a sample workbook?

 

[jasonb75]

The value being returned will be in the sheet somewhere, it might appear different as a result of formatting in 'VRA data' sheet.

Your description of your sheet layout is a little confusing, try using formula evaluation to see where the result is coming from. (in excel 2007 press Alt, m, v).

 

[wednesday77]

http://i1125.photobucket.com/albums/l596/mb121377/vrasummary.jpg

http://i1125.photobucket.com/albums/l596/mb121377/vrajpeg.jpg

 

[wednesday77]

I attached the two sheets as html links that should be working correctly.

 

[jasonb75]

Have a look at row 9 on the sheet that you copied the original formula from, I expect that it contains hidden values which control the row offset.

In your sheet, those cells are empty so the formula has a 0 row offset, the match part returns the column holding the date, so you're getting the date in serial number format (40954 is the date serial number for Feb 15 2012).