Error with Drop Down/Pulling Data

[Yvonne Love]

I'm not exactly sure what's happening here, but attached is a file I've been working on today. I am trying to create dynamic graphs on the Office Summary tab based on the data validation/list in Cell R1. I am pulling from a few different tabs to get the graphs (I have named ranges and the like). On the TK_Loc tab, I have my data starting in cell Z2 (pulling from table on same tab). It's causing an error when I select "Albuquerque" from the Office Summary tab, and I can't figure out what is causing it to error. It doesn't cause an error when selecting any other office (although when immediately selecting another office after Albuquerque, it does error, but then it will work).

I'm not sure at all what is causing that error. Any ideas? I'm using all the tricks in my book for this report, so I want this one to work too!

I deleted some rows not used for this specific report since my original file was too big to attach. some of the formulas may now have portions that are not relevant anymore.

Thanks!
YL

 

[Hui]

YL
Your original formula had a spelling error
=IF('Office Summary'!$R$1=Z2,TRANSPOSE(TK_Loc!Z2:Z7),IF('Office Summary'!$R$1=Z3,TRANSPOSE(TK_Loc!Z9:Z14),IF('Office Summary'!$R$1=Z4,TRANSPOSE(TK_Loc!Z16:Z21),IF('Office Summary'!$R$1=Z5,TRANSPOSE(TK_Loc!Z23:Z28),IF('Office Summary'!$R$1=Z6,TRANSPOSE(TK_Loc!Z30:Z35),TRANSPOSE(TK_Loc!Z37:Z42))))))

That S was missing

You may prefer this more succinct formula

In TK_Loc!AD2 array enter

=TRANSPOSE(CHOOSE(MATCH('Office Summary'!R1,Z2:Z7,0), Z2:Z7, Z9:Z14, Z16:Z21, Z23:Z28, Z30:Z35, Z37:Z42))
and array enter it into the range

 

[Yvonne Love]

Thank you Hui. I just needed a fresh set of eyes. Also, I figured I wasn't using the most efficient formula there. thanks for the alternative!

YL