Forum Discussion

Ange2010's avatar
Ange2010
Copper Contributor
Apr 27, 2022

Formula to pick up location data within multiple worksheets

Good afternoon,

I am trying to create a formula where a cell contains location details for example, NSW - Crows Nest, and VIC - Flinders Lane - and have NSW or VIC populated in another cell. So the State (NSW or VIC) is the specific text I want the formula to pick up. I have multiple locations and my table will grow to more than 300 rows.

 

In addition to this, when the Location cell has been populated with the State, I need to develop a tab per State to read the data from the master tab (where the abovementioned cells are created). There are seven columns/data I need to pull across for that specific State.

 

**Updated file link https://1drv.ms/x/s!AlpeZbby5IYVqWef7qfirpsxbREw?e=qjhqD7

 

Thanking you in advance,

excel
Formulas and Functions
office 365

6 Replies

    • Ange2010's avatar
      Ange2010
      Copper Contributor
      Apr 28, 2022

      mathetes my apologies for that.
      https://1drv.ms/x/s!AlpeZbby5IYVqWef7qfirpsxbREw?e=qjhqD7 

      • mathetes's avatar
        mathetes
        Gold Contributor
        Apr 28, 2022

        Ange2010 

         

        It was a usable link, for which I'm grateful. Still was "read-only" however, so I copied some of the data to the attached and think I've done what you were wanting to do. Write back if not. The second sheet makes use of the FILTER function , which requires the most recent version of Excel.

Resources