Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
array transpose for dynamic data without macros
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,COUNT A(<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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose array sum | Excel Worksheet Functions | |||
transpose - array problem | Excel Discussion (Misc queries) | |||
Transpose Array | Excel Discussion (Misc queries) | |||
dynamic transpose | Excel Discussion (Misc queries) | |||
Conditional transpose to Array | Excel Discussion (Misc queries) |