Forum Discussion
Formula to pick up location data within multiple worksheets
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.
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.
https://1drv.ms/x/s!AlpeZbby5IYVqWm4kBtyI15dOamq?e=2nLNZJ
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
- Extremely grateful - thank you. I will have a look at this now and come back to you if I have any issues.