Problem with Formulas based on Data Validation Selection from multiple tabs

[warrior2411]

I have two questions, and both require (I think) the same formula.

Just a note, I attached the document at the bottom, but had to remove 12 location tabs because the document was too large. I think I'll be able to revise this once I get my answer. If you need the full document, please message me and I'll be happy to email it!

- - -

QUESTION #1:

I have 15 locations listed on their individual tabs. Each tab (HPU 3-3 through HPU 10-2) has the same format but different data (except the dates column, they’re the same on each tabs). On each tab, I have a growing inventory list.

- Dates are listed under Column Y. The first date is Y5.
- Items are listed under Column Z. The first item is Z5.
- Action Notes are listed under Column AA. The first action item is AA5.

On a separate report tab, I want to create a spreadsheet that will toggle back and forth between the selected area, then selected locations. For selection, I use a multi-level data validation.

On the report tab, once a location is selected,
- Dates will fill in at T12
- Items will fill in at U12
- Action Notes will fill in at V12

The problem appears because the test seperators on the Quarterly Plant Oxygen tab have the same format, but instead of being listed on their own tabs like the individual locations are...they are all on one page. How do I create a formula (addressed in QUESTION #1) but have different data format.

I am trying to filter as follows:
Field: (EX) HPU 3-3
Plant: (EX) HPU 3-3 Test Seperator

QUESTION #2:

If QUESTION #1 is not possible, I created a function just based off the individual locations (HPU 3-3 through HPU 10-2), but my function keeps returning “#REF!”?

Please Login or Register  to view this content.

The document is attached for reference. All help is much appreciated! Thanks!

Example(5a).xlsm

[Haseeb Avarakkan]

Hello Warrior,

Try these formulas.

in T2,

=INDIRECT("'"&TRIM(LEFT($C$21,FIND("-",$C$21)+2))&"'!"&ADDRESS(ROWS(T$12:T12)+3,COLUMNS($T12:T12)+24))

U2, then copy to V2

=IF($T12,INDIRECT("'"&TRIM(LEFT($C$21,FIND("-",$C$21)+2))&"'!"&ADDRESS(ROWS(U$12:U12)+3,COLUMNS($U12:U12)+25)),"")

Then copy T2:V2 & paste down.