=INDEX(Sheet1!$A$1:$A$5,COLUMN()-COLUMN($B$1)+1)
will transpose as desired. The COLUMN($B$1) bit refers to the formula cell where it is first put.
a fab forula but... How do I do this but avoid copying over 0 for blank cells
the issue I have is that it is copying over the ) but then when I do a counta it records them as if something is in that cell!![
any ideas?
cheers
XAD | XAD | q | w | fad | jad | lad | 25 | ||
q | |||||||||
w | |||||||||
fad | |||||||||
jad | |||||||||
lad | |||||||||
25 |
=IFERROR(INDEX($A$1:$A$10,SMALL(IF(1-($A$1:$A$10=""),
ROW($A$1:$A$10)-ROW($A$1)+1),COLUMN()-COLUMN($B$1)+1)),"")
=IF(COLUMNS($B$1:B1)<=SUM(IF(1-($A$1:$A$10=""),1)),
INDEX($A$1:$A$10,SMALL(IF(1-($A$1:$A$10=""),
ROW($A$1:$A$10)-ROW($A$1)+1),COLUMNS($B$1:B1))),"")
How do I do it the other way... i gave a try, but could not achieve it....
Transpose and link from A1:B1 to A1:A3
Thanks!!
Any idea guys for my above question??
Thanks!!
JAD | NAD | KAD |
NAD | ||
KAD | ||