Forum Discussion
Conditional text move row to sheet
Okay. So I formulated this workbook, and of all things I've accomplished with it, this is the one I'm struggling with. I have Information from the Access Database coming in to the "Access Query" tab. The "Rooming List" tab then takes the needed information from that tab and makes the list that I need and puts it into a form that I distribute to the necessary folks around the building. Occasionally, I have someone who is not projected and I have to mark them as a "Walk On". This is done in Access, and moved from Access to Excel, to the Access Query tab in the Notes column, then into the concatenated Notes column on the Rooming List tab, which concatenates Notes and ID Type.
Essentially, what I need, is whenever there is a walk on, I need it to see there is a walk on, and move the name, branch, and room number to the proper column on the "728 Walk On List" tab. The branch needs to be moved over as just the first three digits(shown on the left side of the "/"), and the APP/ENL box needs to be marked appropriately with an "X" in the box(displayed on the right side of the "/". Any thoughts?
5 Replies
I'm not exactly sure what is the logic behind, but perhaps you may generate the list for this table using Power Query, you already use it to take information from Access.
- Here is the issue. The form it is going into is a form that I can not change, and is required by the company we are contracted with. I'm not really sure how to pull this information into the template
Maybe, the new FILTER-function can be of help:
=FILTER(RoomingList[NAME];IFERROR(SEARCH("Walk";RoomingList[NOTES];1);"")<>"")If you place this function in cell A5 of the 728 Walk On-sheet it spills the names from the Rooming List-sheet where the NOTES-cell contains "Walk" as supplied by the Access Query-sheet. The outcome looks somethings like this:
Note that you have to unmerge the cells A5 till B29, otherwise the FILTER-function can't spill the result. You can use the same formula-structure for Branch and Room number.
I hope this puts you in the right direction. And for your convenience I attached a modified version of your workbook.
- So, how would I go about pulling this information from the Power Query? Something that I'm struggling with at the moment is the Rooming List tab Concatenates some information while the tab I'm putting this formula into needs it individually. ie...m/f...Military Service...and that sort. The name pulling needs to be concatenated, Last, First, Middle Initial, but of course, I can worry about all of that.
That worked fantastically!
