Apr 27 2022 10:38 PM - edited Apr 28 2022 04:43 PM
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 Here is a usable link to the file.
Thanking you in advance,
Apr 28 2022 07:39 AM
Apr 28 2022 04:26 PM
@mathetes my apologies for that.
Here is a usable link to the file.
Apr 28 2022 06:27 PM
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.
Apr 28 2022 06:34 PM
Apr 28 2022 07:01 PM
@mathetes thank you very much. When I used your formula in sheet 1 (National) in my master file (link below), it worked perfectly.
My master file, sheet 1 (National) is pulling data from another workbook. For sheets 2-5, I was initially thinking I would have a sheet per State as our objective is to have a user friendly By State document to upload. Could I use a FILTER function here? If so, I would need assistance please.
Apr 29 2022 05:40 AM
Your formula in column I of the master sheet is yielding #VALUE errors in some cases because of the inconsistency in what you have in column H. And that inconsistency is due to a mixing of kinds of information: "Online" is not a location, rather it's a means of delivery or a medium (however you want to characterize it), but not location per se. Nevertheless, if you want to keep it that way, revise the formula to read thus:
=IFERROR(LEFT(H6,FIND("-",H6)-2),H6)
That said, I can't see any reason why FILTER wouldn't work. Here's a video that explains it (it's how I learned it a year or so ago). I'd recommend you watch it, then come back if you still need additional help.
https://www.youtube.com/watch?v=9I9DtFOVPIg
Here, too, is a good website for learning new functions: https://www.youtube.com/watch?v=9I9DtFOVPIg