R
Rad131304
I'm having a problem with array functions and transpose.
I currently have a column of data that is unknown in length (it will be
different every time it is used), and I would like to auto-populate column
headers in a different sheet with this data. ATM I am using the following:
=TRANSPOSE(OFFSET(<first_column_element>,0,0,COUNTA(<column>)-1,1))
My problem is that, since I have no idea how many items there will be, I
have to apply this array formula to the entire span of columns which makes
for a lot of #N/A columns. I've *fixed* this by conditionally formatting the
font color to be the same as the cell background so the #N/A is not visible.
I'd really like to make this array have a dynamic size based upon COUNTA()
so I don't have to use my font color workaround, but I have no idea how to do
this (or if it is even possible) without macros. I need to avoid macros if
possible since I don't know if my users have the ability to allow macros to
run.
I currently have a column of data that is unknown in length (it will be
different every time it is used), and I would like to auto-populate column
headers in a different sheet with this data. ATM I am using the following:
=TRANSPOSE(OFFSET(<first_column_element>,0,0,COUNTA(<column>)-1,1))
My problem is that, since I have no idea how many items there will be, I
have to apply this array formula to the entire span of columns which makes
for a lot of #N/A columns. I've *fixed* this by conditionally formatting the
font color to be the same as the cell background so the #N/A is not visible.
I'd really like to make this array have a dynamic size based upon COUNTA()
so I don't have to use my font color workaround, but I have no idea how to do
this (or if it is even possible) without macros. I need to avoid macros if
possible since I don't know if my users have the ability to allow macros to
run.