# Sum, indirect, substitute, address, row problem

#### David Wale

##### New Member
Excel 2016 (Windows) 32 bit
1ARS1111111ARS1111111
2ARS1234567ARS11123334445556677
3ARS123456789101112131415161718ARS123456789101112131415161718
4ARS1311333ARS11131111113333333
5
6BLB1111111BLB1111111
7BLB1234567BLB111123344455556677
8BLB123456789101112131415161718BLB123456789101112131415161718
9BLB3000103BLB333300000011110033
10
11CHE1111111CHE1111111
12CHE1234567CHE111123333444556667
13CHE123456789101112131415161718CHE123456789101112131415161718
14CHE1101330CHE111110000111333330

</tbody>
Sheet6 (2)
Hello Forum
I have created the tables, (on the left), and have filled in the tables, (on the right), with what I wish to see.
There are two parts to this problem:-
Part one - For example, take CHE table. (on the left), B12=1, C12, D12 and E12 are blank. So, in the, (right hand table), using the CHE, (right hand table), what I wish to see is, V12, W12, X12 and Y12=1. And like wise for 2,3,4,5,6,7 etc.
Part two - For example, take CHE table. (on the left), B14=1, C14, D14 and E14 are blank. So, in the, (right hand table), using the CHE, (right hand table), what I wish to see is, V14, W14, X14 and Y14=1. And like wise for 0,1,3 etc.
Thank you
David Wale

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### sandy666

##### Well-known Member
you can try Power Query (Get&Transform)

eg.

 Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 Column9 Column10 Column11 Column12 Column13 Column14 Column15 Column16 Column17 Column18 Column19 ARS 1​ 1​ 1​ 1​ 1​ 1​ 1​ ARS 1​ 1​ 1​ 2​ 3​ 3​ 3​ 4​ 4​ 4​ 5​ 5​ 5​ 6​ 6​ 7​ 7​ ARS 1​ 2​ 3​ 4​ 5​ 6​ 7​ 8​ 9​ 10​ 11​ 12​ 13​ 14​ 15​ 16​ 17​ 18​ ARS 1​ 1​ 1​ 3​ 1​ 1​ 1​ 1​ 1​ 1​ 3​ 3​ 3​ 3​ 3​ 3​ 3​

Code:
``````[SIZE=1]// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Transpose = Table.Transpose(Source),
Fill = Table.FillDown(Promote,{"ARS_1", "ARS_2", "ARS_3"}),
TransposeBack = Table.Transpose(Demote),
Extract = Table.TransformColumns(TransposeBack, {{"Column1", each Text.Start(_, 3), type text}})
in
Extract[/SIZE]``````

#### David Wale

##### New Member
Good morning Sandy666
Thank you for supplying a solution to my, (sum, indirect, substitute, address, row problem). At the moment, I am unsure how to make this work, as I am unfamiliar with Power Query (Get&Transform), but I am reading through and printing articles, to get a better understanding.
So thank you again for your help.
David Wale

#### sandy666

##### Well-known Member
You are welcome
feel free if you have more questions
Have a nice day

Replies
1
Views
36
Replies
2
Views
69
Replies
5
Views
41
Replies
2
Views
25
Replies
11
Views
115