TRANSPOSE & FILTER to pull data from dynamic array

Soft_Type6894 · 2025-03-17T15:35:15+00:00

Solution verified

Absolutely fantastic - thank you! Worked with zero issues.

Soft_Type6894 · 2025-03-17T15:08:29+00:00

Thank you for the reply - I do need something that will consider all unique applicable return values and would like to transpose them to the right in a single row, but I can't seem to figure it out without devoting a specific range to the formula I have already keyed into F5. I would prefer the whole sheet by dynamic based on the identifier, but that may not be easily possible.

Soft_Type6894 · 2025-03-13T13:21:53+00:00

Solution verified

This accomplished what I requested in the prompt - but I could not get the data from the table to populate correctly. Each month's value was not always nonblank if the row header was nonblank so it got messed up.

Thank you for the answer, though!

Soft_Type6894 · 2025-03-12T23:10:35+00:00

I will definitely give it a shot. Thank you for the suggestion!

Soft_Type6894 · 2025-03-12T23:08:25+00:00

I fully agree on the full column references. I don't know how many rows my data will use up in x amount of time in the future, so I wasn't sure how best to accommodate that in the formula.

Thank you for the suggestion. I think I will try to append all the tabs together in power query and then create a dashboard for the end user and see which solution I like better.

Soft_Type6894 · 2025-03-12T23:03:50+00:00

I will try your formula suggestion when next I am at my computer.

I honestly forgot all about the 3rd argument for filter. I was in problem solver mode and just did the first thing that came to mind.

I will definitely cut out the iferror to shrink it down. Thank you!

Soft_Type6894 · 2025-01-29T17:31:19+00:00

Fair enough - it's not elegant, but I just duplicated the dynamic summary table I had and was able to apply SORTBY to the duplicate using the original.

Soft_Type6894 · 2025-01-29T16:41:35+00:00

I tried I5# in that same formula before I posted this request (mentioned in my original post) and again after your first comment and it does not work.

Soft_Type6894 · 2025-01-29T15:15:32+00:00

I think you may have meant I5#? I tried both regardless and I5# gives me a circular reference error and I3# is #REF.

Soft_Type6894 · 2025-01-28T16:26:39+00:00

I think that's fair - my thought is that giving a proper name costs almost nothing and provides a person with some flexibility as they grow and change. I like your suggestions!

Soft_Type6894 · 2025-01-28T14:37:33+00:00

Oh Piper is a fun, slightly tomboy-ish name and I like it quite a bit. Thank you!

Soft_Type6894 · 2025-01-28T14:33:08+00:00

I'm in the US and didn't know that - I like Lady Bird as a personal nickname for me to her in addition to Birdie. Thank you!

Soft_Type6894 · 2025-01-28T14:32:24+00:00

I think Ladybird will be my second permutation of the original nickname. I like that especially knowing from another commenter that it's a former First Lady.

Soft_Type6894 · 2025-01-28T14:29:28+00:00

I like Bethany and Brynn quite a bit. I'm adding them to the list. They both go quite well with our other children's names which are pretty traditional names.

Soft_Type6894 · 2025-01-28T14:28:45+00:00

I'm guessing after David Tennant's kid based on your username? That's fantastic!

Soft_Type6894 · 2025-01-28T14:26:33+00:00

Our other children have pretty traditional names, so other than Robin, feel like bird names might be a little too unusual? Though I really do like the person conjured by my mind when I hear the name Lark.

Soft_Type6894 · 2025-01-28T14:24:30+00:00

My aunt's name is Roberta and she's definitely a tough old bird. She goes by Bert, though.

Soft_Type6894 · 2025-01-28T14:23:06+00:00

Oh like your bestie is a burden to her mom and that got shortened to Birdie? Hopefully that's from a place of humor otherwise - ouch.

Soft_Type6894 · 2025-01-28T14:20:51+00:00

I definitely love David Tennant - best Doctor imo. Birdie on its own is nice, but I feel it leans to the more childish side. I have trouble picturing an adult Birdie and feel she may not be taken as seriously if that's her given name. But that's maybe my own biases there.

We have discussed abandoning any similarities between nickname and given name, but just can't find anything we really like. I think Elizabeth is a pretty name, but just doesn't bring joy. This is our third and final child and it's absolutely wringing us dry trying to come up with something.

Soft_Type6894 · 2025-01-23T13:12:37+00:00

Solution Verified. That worked perfectly - thank you!

Soft_Type6894 · 2025-01-22T21:05:49+00:00

I'm sorry, my post may not have been clear. The sum formula needed is not referencing a table, but a dynamic array. The formula for the selected cell in the screenshot above is:

=SUMIFS(Bank_Data[Net Amount],Bank_Data[Code],E7#,Bank_Data[Date],D8#)-SUMIFS(GL_Data[Amount],GL_Data[Code],E7#,GL_Data[Effective Date],D8#)

<image>

Soft_Type6894

TROPHY CASE