Help! Formula to show a value depending on the value of two other lists...

[claraharper]

Hello!

First post, although I've used the site a lot for help before, but I can't find anything for this!

I'm pulling together a rota for my team, and there's a whole load of background information that is going to be displayed in an 'Overview' section of the sheet. When we're filling in the background information, people will either be "online", "offline", "holiday", "quotes" or "mail". There's several people "online", so for the overview all I need is a count, and I've managed that.

It's the "quotes" and "mail" part that I'm struggling with - in the overview cells I want to show who is on this, without having to type it in. So there are two lists showing everyone's names (e.g. Bill, James, Mary, Anna) and what they're doing (e.g. Online, Quotes, Online, Mail) and I want the cells in the overview to know that when 'Quotes' is next to someone's name, to display their name in the overview. If that makes sense? I tried a 'If' function but it got very complex, very quickly, and wouldn't work.

I've attached an example of what I mean - I know the overview seems a bit superfluous but there's a whole lot of other information in the rota and the overview not shown here, and it's really useful to have.

Any help would be massively appreciated!!

Thanks,

Clara

Example.xls

[BeachRock]

Hi Clara,

I'm not sure I understand what you need. Do you want all of the people's names in A2:A10 who have "Quotes" next to their name in B2:B10 to all show up in the Quotes cell B15 all at the same time if there are more than one?

[FDibbins]

not sure if this is something you can work with, but i created a pivot table. take a look and let me know?

[claraharper]

Hi there,

Thanks for getting back to me! There will only be one person on quotes or mail each day, so I only need one person to show in each of the boxes in the overview. I know it seems like a bit of a faff - would be simpler just to type the name in once the rota is complete - but believe it or not the rota I'm working on now is a far simplified version of what we have at the moment and the idea is to have as little work required as possible!

I'll have a look at the pivot table - thanks for this - I don't have a lot of experience with them but I've been putting myself through a wee training module on them so between that and the one you've given I should be able to work it out!

Thanks again,

Clara

[claraharper]

Hi FDibbins, just had a look at the pivot table and it seems to do exactly what I need (I think) so I'm going to have a play around with it and see if I can get it to display the info the way I need it to - thanks so much for this, I would never have thought of it!

Clara