Building a table in excel and automatically puttiing structured formulas in

[bob2dis]

I have a very complex table to build in which every row will require a different formula to be entered into it and because the table is so large I wondered if there was a way to automatically do this.

The structure of the table will be as follows:

1 Europe Middle East & Africa
1.1 Western Europe
1.1.1 NORDICs
1.1.1.1 Denmark
1.1.1.1.1 Segment 1
1.1.1.1.2 Segment 2
1.1.1.1.3 Segment 3
1.1.1.1.4 (...... Continued, segments all on the same level)
1.1.1.2 Sweden
1.1.1.2.1 Segment 1
1.1.1.2.2 Segment 2
1.1.1.2.3 Segment 3
1.1.1.2.4 (...... Continued, segments all on the same level)

1.1.2 DACH
1.1.2.1 Germany
1.1.2.1.1 Segment 1
1.1.2.1.2 Segment 2
1.1.2.1.3 Segment 3
1.1.2.1.4 (...... Continued, segments all on the same level)
1.1.2.2 Austria
1.1.2.2.1 Segment 1
1.1.2.2.2 Segment 2
1.1.2.2.3 Segment 3
(...)

For every item there is 4 rows of information and 13 columns, what I essentially need is to create a reference table for the structure like above and then based on that reference table formulas are entered into the cells, for example Row 1 of Denmark would be the total of row 1 of Segment 1 + Segment 2 + Segment 3+(...). The Nordics row 1 would be Row 1 for Sweden + Demark all the way up to the top. Each row will need a different formula with different dependencies but they will be the same actual formula all the way up, if that makes any sense. Can anyone help me come up with a faster way to write this than doing every cell one by one in VB and having to type all the separate formulas out.

[westconn1]

I wondered if there was a way to automatically do this.

you should be able to create the structure, but the data would need to come from somewhere, and know the number of segments for each section

[Joacim Andersson]

Moved to the Office Development forum.

[bob2dis]

Hello westconn1
The data originally will be loaded in as flat values so its actually irrelevant, its getting the correct formulas into the cell to calculate dependancies are the key. I need to build this so when you change any value you any cells based on where you've edited it inserts the correct formulas into the other cells, and calculates the rest of the cells based on your input. So how would I go about doing this if we say I have a reference (as above) for the above table in excel how I could I then get excel to build it with dependancies and insert formulas based on its location in the table. For each element there will be FY10, FY11, FY12, FY13, FY14, FY15, FY16, then company results for FY10, FY11, FY12, then Marketshare for FY10, FY11, FY12.

[vbfbryce]

Bob,

Can you give us a spreadsheet showing both the raw data and what you'd want the results to be (for at least a small sample of the data)?

I think I'm following what you're after, but want to make sure first.

Bryce

[bob2dis]

vbfbryce you are a hero, I used your other advice on the vb cases which is another part of this.

Here is an example of what I am trying to do: http://www.rasync.com/Example.xlsx

The segments (Green) total into the countries above them (red) and the countries total into the sub regions above them (blue) and the sub regions total into the topline region (grey). I want to build a table that puts different formulas into the sheet depending on what number is changed for example if I change the Segment 1 total (c15) for Austria it does the following

1) Recalculates Austria C12=C15+C18+C21
2) Recalculates DACH (c9) = C12+C24+C36
3) Recalculates Western Europe (sum of Blue ones)
4) recalculates Segment Market Size contribution (MS Contribution) of the segments, C17 =C15/C12, C20=C18/C12 C23=C21/C12
5) recalculates Country Market Size contribution (MS Contribution) of the segments, C14 =C12/C9, C26=C24/C9 C38=C36/C9
6) recalculates Sub Region Market Size contribution (MS Contribution) of the segments (Red Ms Contribution = Red totals/Western Europe
7) Recalculates the new Segments market share M15=J15/C15, M18=J18/C18, M21 = J21/C21
8) Recalculates Country Market share ie Austria M12 = j12/c12
9) Recalculates Region Market share ie DACH M9 = j9/c9
10) Recalculates western Europe Market share ie DACH M6 = j6/c6

It also recalculates all the growths under every number (some not inserted) ie D16=D15/C15-1

I also need to get this to work the other way so when a growth number is changed it recalculates all the dependancies.

Does that make sense so every number is calculated up and down

Thanks

[vbfbryce]

Bob,

You're much too kind!

Will take a look at your sheet and see what it looks like...

Bryce

[bob2dis]

Any ideas of how this can be done? Ideally I want to create a table of dependancies and have excel write them to that structure.

[vbfbryce]

I'm still a little unclear on exactly what you need. I will PM you my e mail address if you don't mind some Q & A that way, then we can update the forum with the results.