Index, Offset, Index... I'm getting crazy

Coontradis

New Member
Joined
Aug 28, 2019
Messages
1
For the pros…
Been working on this for the last day….
I have a monthly report that update each week. It consists of cost center and account number. I want to control the budget and have the total of each account for each cost center. Not very complicated up to now… Here is the fun part: I have 5 cost center. In each cost center I have accounts. The accounts numbers are the same for all cost center. Ex: account 60000 is for my labor, so I have a 60000 account in my production cost center, service, office, sales and maintenance.
I want to have a tab for each of my cost center that give me the total of the account. But here is how the data are coming from the download.:


ABCDEFGHI
1COST CENTER 1000060000REMY25
2JON30
3BOB30
4Result85
560001Hammer10
6Roof1000
7Result1010
8Result1095
9Cost center 2000060000Paul35
10Adele50
11Result85
1260001Belt95
13
14

<tbody>
</tbody>

SO for account 60000 (column C) for cost center 10000 (column B) I want the total as show in row 4 column I (total by account for a specific cost center).
The data of the cost center are always in column B but appear only once where the data for this cost center start. The account stands in column C and the total in I.
I would like to have the total of each account in another tab (on for each cost center). Note that the data add every week so this need to be dynamic. I also work with excel 2016

Thanks for helping me
 

ekrause

New Member
Joined
Aug 7, 2019
Messages
27
I created a formula that works based on the table your provided but can't guarantee it'll work with more data.

=INDEX(OFFSET($I$1,MATCH($K2,$B:$B,0)-1+MATCH($L2,OFFSET($C$1,MATCH($K2,$B:$B,0)-1,0,COUNT($I:$I)),0)-1,0,COUNT($I:$I)),MATCH("result",OFFSET($E$1,MATCH($K2,$B:$B,0)-1+MATCH($L2,OFFSET($C$1,MATCH($K2,$B:$B,0)-1,0,COUNT($I:$I)),0)-1,0,COUNT($I:$I)),0),1)

Here are the results this generates with the table starting on cell K2. A few notes: I changed Bob's value to 35 so there was a difference between Cost Center 10000 & 20000 Results & added a result value for 60001 for just the Belt.

COST CENTER 10000 60000 90
COST CENTER 10000 60001 1010
Cost center 20000 60000 85
Cost center 20000 60001 95

While this formula MAY work I would suggest making changes to the structure of your data before trying to summarize it.

Hopefully someone else has a more elegant solution. :)

Edit: Updated Table starting cell
 
Last edited:

Sam_D_Ben

Active Member
Joined
Oct 17, 2012
Messages
381
I created a formula that works based on the table your provided but can't guarantee it'll work with more data.

=INDEX(OFFSET($I$1,MATCH($K2,$B:$B,0)-1+MATCH($L2,OFFSET($C$1,MATCH($K2,$B:$B,0)-1,0,COUNT($I:$I)),0)-1,0,COUNT($I:$I)),MATCH("result",OFFSET($E$1,MATCH($K2,$B:$B,0)-1+MATCH($L2,OFFSET($C$1,MATCH($K2,$B:$B,0)-1,0,COUNT($I:$I)),0)-1,0,COUNT($I:$I)),0),1)
Edit: Updated Table starting cell
Would be helpful if you could frame a table along with the position of your formula. Appreciate your effort.
 

ekrause

New Member
Joined
Aug 7, 2019
Messages
27
Would be helpful if you could frame a table along with the position of your formula. Appreciate your effort.
I used the table as framed in original post. Also referenced the start of my table (cell K2) and the table I made in my post:

COST CENTER 10000 60000 90
COST CENTER 10000 60001 1010
Cost center 20000 60000 85
Cost center 20000 60001 95

Literally not sure what else I could've done aside from posting the Excel I created this in.
 

Sam_D_Ben

Active Member
Joined
Oct 17, 2012
Messages
381
I used the table as framed in original post. Also referenced the start of my table (cell K2) and the table I made in my post:

COST CENTER 10000 60000 90
COST CENTER 10000 60001 1010
Cost center 20000 60000 85
Cost center 20000 60001 95

Literally not sure what else I could've done aside from posting the Excel I created this in.
My mistake i dint see it. I appreciate it. Also Pivot helps us to get detailed output.

 

Sam_D_Ben

Active Member
Joined
Oct 17, 2012
Messages
381
I checked it with more datas. All we have to do is just adding up data daily in the same format and just refreshing the PIVOT gives the desired detailed result.

 

Forum statistics

Threads
1,082,023
Messages
5,362,725
Members
400,687
Latest member
jomelprototype

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top