Search one column, then rows and the return adjacent cell value

I have a really weird set up I am trying to figure out.

I have data that is, unfortunately, set up like this:
Example Table.JPG

On different tabs within the same workbook, each one named for a separate Region, all the Districts within each Region are set up like this:
Example Table 2.JPG

I need a formula that I can pull the TOTAL from each District on the DATA SHEET TAB and place it in the appropriate month’s column on the REPORT tab.

I would pull the data once a month, copy it from the stupid report that is formatted the way I have shown in the “DATA SHEET TAB” image above, and paste it into my workbook either on a separate tab for each month (i.e. DATA SHEET – JAN, DATA SHEET – FEB, etc.) or past it into sections of one DATA SHEET TAB that is broken down into months.

I was originally looking for a formula that would look on the DATA SHEET TAB, in column A for the District Name, find the first instance of the word “TOTAL” in the rows immediately following it and return the value one cell to the right of the word “TOTAL.” Trying to figure out how to do that has been impossible. That’s why I am here. I am not experienced in VBA so if your solution is there, I will need VERY detailed instructions.

Thanks!!

Please post a sample file]

To upload file click "Go Advanced" then scroll down to "Manage Attachments"

I hope this makes sense. Sample file attached. Thanks for any help you can offer.

I used this formula in I4

=IFERROR(INDEX('DATA SHEET'!$A$1:$N$750,MATCH($A$1& " / " &$B2,'DATA SHEET'!$A$1:$A$750,0)+3,5),0)

Copy and paste to other rows.

I put data in July 2016 as there is no date entered in the "DATA SHEET": is the date to be in A3 etc ?

So awesome!! Thank you.
To answer your question, the DATE cells on the DATA SHEET are not actually date cells in my real document. I am sorry. I should have picked some other less confusing heading. I replaced the headings to protect confidential data. The report I pull does not list any dates. I pull the data each month for the month prior so, I would need to either create a separate DATA SHEET for each month or I would need to put a date in somewhere on the DATA SHEET to title the data for it's respective month and have the formula look below that date or something. I hope that makes sense.

Can you help with the best way to go about that?


Also,
So that I can adjust little bits of the formula as needed if, when I copy and paste the report into the DATA SHEET, things are listed a little differently than the data from last month, can you walk me through the steps?

Here's what I see. Please correct me or help me fill in what I don't know/understand.


=IFERROR(INDEX('DATA SHEET'!$A$1:$N$750,MATCH($A$1& " / " &$B2,'DATA SHEET'!$A$1:$A$750,0)+3,5),0)

** =IFERROR: <---Starts off the formula with the basis for instructions that will be listed later on in the formula about what to do if the result is an "N/A" or other error.

** (INDEX <---Have not used this one before. What does this do? Are you able to give me the structure/rules of a basic "INDEX" function formula?

** ('DATA SHEET'!$A$1:$N$750, <---Telling the formula to index the entire section of data listed on the DATA SHEET tab from the 1st cell on the top left to the last cell on the bottom right. I noticed this one has the cell references listed as non-static. How does that help in this case? Is that for the purpose of the REPORT tab so that the formula can be pasted elsewhere?)

** MATCH( <---Look for a match in the cells listed right before this command to the values in the cells listed after this command

** $A$1& " / " &$B2, <---Look at the value in cell A1, on the current sheet, along with the added specified "/" sign mixed with the value of the B2 cell on the current sheet. The cell references are listed as non-static except the Row in the B column reference. The B column is static so that it always looks in that column but the row is non-static so that it looks in the appropriate cell when the formula is pasted.

** 'DATA SHEET'!$A$1:$A$750, <---Look for those MATCH values on the DATA SHEET TAB in All cells between A1 and A750. Again, why are the cell references listed as non-static here?

** 0) <--- If a match is not found, place a zero

** )+3,5 <--- If a match is found, look 3 rows down, 5 cells over and return the value in that cell *-*-*- One problem with this part: In the data that I past to the DATA SHEET tab each month, the "TOTAL" that I will be looking to pull into the REPORT tab will not always be 3 rows down. Sometimes, there will be more than one dollar amount listed that will EQUAL the final total and since they will all be listed between the Region / District heading and the final total line, it could be 4 rows down, 5 rows down and so on. Hence my conundrum.

** ),0) <--- If a match is not found, place a zero (Seems redundant but I am assuming the 1st zero is the end to the MATCH function and the 2nd zero is the end of the "IFERROR" command.



How'd I do?

Wanted to let you know I appreciate your time in helping me with this. I have been buried and unable to review the sample files you attached but will soon and let you know how it works out. Thank you so much!

ok. I was finally able to check out what you did. I LOVE the file you attached where you added a "Jul" tab and an "Aug" tab. I will need to research exactly how the INDIRECT function works and learn how it's working in this particular instance (unless you'd be willing to explain it to me?) but I believe this will work. It will take me a while to set up my file with all the month tabs and add all the formulas but, I believe, once the work is put in, this will save a TON of time each month when I have to run these reports. Just a quick copy and paste and your fantastic formula help will do the rest of the work for me. Thank you SO much!!!