How to trick absolute reference for indirect formula

[tiong999]

B13 =
INDIRECT(ADDRESS(COLUMN(B4)-COLUMN($B$4)+ROW($B$4),ROW(B4)-ROW($B$4)+COLUMN($B$4)))

C13 =
INDIRECT(ADDRESS(COLUMN(N4)-COLUMN($N$4)+ROW($N$4);ROW(N4)-ROW($N$4)+COLUMN($N$4)))


Using above formula cost my time, because i need manually modify column.
Im trying to modify formula with extra helper column B11:CF12. then modify COLUMN() with indirect ()

C13 =
INDIRECT(ADDRESS(COLUMN(INDIRECT(C11&4))-COLUMN(INDIRECT($C11&$4))+ROW(INDIRECT($C11&$4));ROW(INDIRECT(C11&4))-ROW(INDIRECT($C11&$4))+COLUMN(INDIRECT($C11&$4))))

Please anyone helped me take evaluation, how to make my formula similar so i can drag horizontally to copy left cell earlier.


Regards,
Tiong

 

[r2c2]

@tiong999 Not sure what exactly your end goal is, but it seems you want to kind of transpose your data. If so, it is better to use INDEX.

See this:

=INDEX(B$4:CF$5,1,B$12+ROWS($A13:$A$13)) in B13 and drag down, sideways to get some numbers.

Read more about INDEX here: https://chandoo.org/wp/index-formula-usage-and-tips/

If this is not what you need, let me know what the output should be or what is the logic to extract (forget INDIRECT, ADDRESS etc, just say in plain English what you have and what you want) so someone can help you.

 

[tiong999]

@tiong999
=INDEX(B$4:CF$5,1,B$12+ROWS($A13:$A$13))

Hello,
I appreciated of your reply.
But unfortunately your formula transpose whole rows to column.

In plain english, my goal was to transpose
B4:M4 to B13:B24 B5:M5 to B25:B36
N4:Y4 to C13:C24 N5:Y5 to C25:C36
Z4:AK4 to D13:D24 Z5:AK5 to D25:D36
et cetera et cetera

the reason im not using transpose formula, because i will unable to add/deleted for further editing.

Thank in advanced for your review and feedback.

Regards,
Tiong

 

[bosco_yip]

Maybe,

Without helper in row 11 &12, formula solution .

In B13, copied across to CR13 and all copied down

=INDEX($B$4:$AQW$4,1,12*(COLUMNS($A:A)-1)+ROWS($1:1))

or,

=INDIRECT(ADDRESS(ROWS($1:$4),ROWS($1:1)+12*(COLUMNS($A:A)-1)+1))

Regards
Bosco

 

[tiong999]

=INDEX($B$4:$AQW$4,1,12*(COLUMNS($A:A)-1)+ROWS($1:1))

or,

=INDIRECT(ADDRESS(ROWS($1:$4),ROWS($1:1)+12*(COLUMNS($A:A)-1)+1))

Regards
Bosco

Thanks Bosco_Yip & R2C2,

Your contribution really awesome for whole user.

Regards,
Tiong

 

[Peter Bartholomew]

Please forgive me for querying this solution but why such a complicated approach? If you calculate the row and column index for each cell in your output table it is easy to determine the index of the value (in the row headed 'Sample') that you wish to reference.

In the attached, I have left the block index as a helper range but there wasn't room for the row (kabel?) index so that is evaluated as a named formula.

 

[tiong999]

Hai,
Your approach interesting me for a reason, but i dont familiar with your formula.
Yet, it will result Static (not flexible). Lets say, i added new column.

Anyhow, thank for your reviewed.

Regards,
Tiong

 

[Peter Bartholomew]

@tiong999
If you added, columns you would, as it stands, need to extend the arrays 'Kabel' and 'sample' (named from text in A3 and A4) manually, as well as adding a column to the output table.
If that were inconvenient, you could define the defined ranges dynamically, determined by the number of non-blank cells in row 3.
It is only you that know the requirement though!