For me a complicated formula; for you no-brainer?

[Shelly]

Hello, I'm with a small non profit and we are managing 160 volunteers for an upcoming conference. My volunteer volunteer leader created a multi-sheet excel book to assign the volunteers. I'm trying to create a formula that looks at a sheet and if the person's name is there, it returns the text from the column header into the

Sheet named Volunteers is where I'd make the formulas so there is one sheet that has each person's assignment.
Column A has volunteer name
Column E, F, G, H, etc are all named after one of the other sheets in this book (Packet Stuffing, Reg Desk, Room Monitors, etc.). Likely someone knows how to say if that = sheet name then do this below, but I'd be happy to use words instead of cell references.

Getting the information from the other sheets will require two different formulas.

The easy ones (2 sheets named Packet Stuffing and Reg Desk).
I'd write this formula in the volunteer sheet. It would look at the other sheets to match a name, then put the text found in row 2 of the column where the text is into the volunteer sheet.

Logic:
If column AD (or if it helps to be specific cell AD2) of sheet named volunteers appears in the sheet named Packet stuffing - specifically in column B, C, E, etc -- then insert the text from row 2 of that column of the packet stuffing sheet .

The harder one, is the room monitor sheet. The inserted value would need to be both the column header (shift time) and the cell with the Room name.

My colleagues think I'm an excel wiz but I realize I know only about 10%. I'd be grateful if you can help me get to 12% with this answer.

thanks!
Shelly

 

[Luke M]

Hello, and welcome to the forum Shelly!

First, I would suggest that in the future, you try to get leader/boss/giver of info to create a friendlier-to-data-mining spreadsheet. If they had created a single sheet with headers:
Name : Job : Shift : Room (if needed)
we could easily use PivotTables or a plethora of formulas to slice and dice the information as needed.

But, sometimes, we have what we have.
So, I took your file, and created a couple of custom functions in VBA. There's ways to accomplish goal w/o using VB, but formulas get long, and for me, it was easier to write a custom one.

Anyways, check out the attached. If you want to check out the UDFs (user defined functions), you can access the VBE by pressing Alt+F11. As always, if you have questions, feel free to ask.

 

[Shelly]

Thank Luke. A volunteer actually designed the sheet so all I can be is thankful for their contributions.

I'm afraid I need more help - or at least more perspective.

For the packet stuffing, the value returned should have been 1 - the header/label/top cell of the column in the other sheet where the volunteer name was found. (Instead it inserted the number in the column to the left.)

I tried this
=PacketFind(AD48,'PACKET STUFFING'!$B$2:$L$15,'PACKET STUFFING'!$B$1)
and got
#VALUE! - can I tell excel to give me text, not a value?

Second,
Is "RoomMonFind" the name of the UDF?
I hit alt+f11 and had an OMG moment. Wow - so this is the part of excel that is like Oz!
Can I copy that formula to another column? I don't think so. Or do I need to learn visual basic?

Alternate approach if what I desired is an impossible dream given my level of knowledge: Would it be easy to just do columns in Volunteers that returned a true false if the name appeared in the corresponding sheet? That would be helpful but not as wonderful as my quest.

Thanks.
Thanks again.
Seriously, I'm appreciative.

 

[Luke M]

No problem, Shelly.

For the Packet stuffing, partly my fault. I thought we needed to look at first column, not first row. So, need to amend my formula, and then change the ranges.

For the RoomMonFind:
Yes, that's the name of the UDF.
Yep, VB is the secret under-workings. Looks scary, but can be quite powerful. But not to worry, I'll try to keep things simple.

In the worksheet, if you select a cell with the RoomMonFind formula, and hit the function button (looks like Fx near formula bar), you'll see this:

I tried to give each argument a good name. How the formula works:
Looks for 'volunteer' within 'searchRange'. If not found, return "".
If it is found, it will return the cell value in Room range the lines up in same row as volunteer, and the cell value in Shift Range that lines up in same column as volunteer.
So, remember, RoomRange = Vertical, ShiftRange = Horizontal. The one condition is that the RoomRange should be same height as SearchRange, and ShiftRange is same width as SearchRange.

That said, YES! we can use the formula in other cells. Looking at the REG DESK column of Volunteers sheet, you could put this formula:
=RoomMonFind(AD2,'REG DESK'!$B$3:$N$9,'REG DESK'!$A$3:$A$9,'REG DESK'!$B$2:$N$2)

Hopefully that gives you a better idea of how it works, and can adjust to other columns.

If this is all too much, and you just want the simple True/False, you can put this in AE2:
=COUNTIF(INDIRECT("'"&AE$1&"'!A:Z"),$AD2)>1
Copy down and to the right. This will generate a bunch of True/False.