Forum Discussion
Solved
Automated Filtering Transpose Formula
I have been working on this worksheet for construction plant and equipment hired expenses, it contains Dates, Rates and Hours worked for each piece of equipment that has been used. The info i...
As variant that could be
=IFERROR( INDEX($S$6:$BA$6,1, AGGREGATE(15,6, 1/($S$6:$BA$6<>"")* (COLUMN($S$6:$BA$6)-COLUMN($R$6)), ROW()-ROW($B$13)) )*$K$33,0)drag it down till end of the range.
The picture above is what I want to have, but for me to have that I had to remove formulae from the cells and manually take out the blank cells. The columns that have been transposed and where found to have no value in them should not be present in the rows, thats where am failing to solve this. What in is a formula thats should take out (Filter) all the blank cells automatically.
The Black crosses in the picture above indicates the cell(s) that should have been filtered or removed.
Hope i have cleared my question.
As variant that could be
=IFERROR(
INDEX($S$6:$BA$6,1,
AGGREGATE(15,6,
1/($S$6:$BA$6<>"")*
(COLUMN($S$6:$BA$6)-COLUMN($R$6)),
ROW()-ROW($B$13))
)*$K$33,0)drag it down till end of the range.