Can you do this in Excel?

[slaughteredlamb]

In worksheet3 Column A lists names of staff and columns B-S are the days of the week split into 3 time blocks per day.

Via a drop down list on Worksheet2 (the list being populated from a named range of worksheet4) a number is entered into cells B4-S18 which denotes a studio that that member of staff has been assigned to.

On Worksheet 1 I've created a row for every studio with columns for each time block and every day of the week so that B6 is for Studio 1 Monday Morning, C6 is Studio 1 Monday Afternoon, D6 is Studio 1 Monday Evening.

What I want is for B6 to show the name of the staff that has been assigned to Studio 1 at that time. In order to do that it needs to look for a value of 2 on worksheet3 within cells B4:B18. If it finds that value then it needs to look at the appropriate row in column A to get the staff members name.

How on Earth do you write that, is that even possible in Excel?

[robwhizz]

I'm not a big excel user, so I don't know if it's achievable using the 'normal' excel functions, but it should be possible using some VB behind the scenes.

But, would this not be easier in a database?

[slaughteredlamb]

I'm sure you are right in that it would be easier in a database but unfortunately:

1. It needs to be able to be sent as an e-mail attachment (although I'm sure you could create a report that would do this).

2. Needs to be accessed by all members of staff thus requiring something along the lines of an SQL server which we don't have

3. Needs a piece of database software and multi user licenses which we don't have and can't afford.

I completely agree that Excel is the wrong tool for the job but when you work for a charity you have to make use of what you have (an endless source of frustration).

As for VB, yeah that's what I feared, I can't write a single line of VB 

[robwhizz]

2. Needs to be accessed by all members of staff thus requiring something along the lines of an SQL server which we don't have

If everybody has a copy of access, you can split the database. Put the backend on a shared resource and pop the front end onto each machine which is run by access and uses the linked table manager to update the backend. You don't need sql server or anything like that, just a copy of access on each machine that needs to run it and access to the shared drive. It won't power


Looking at the excel route though, I'm a little unsure what is on worksheet4 that drives your drop downs, and is that what generates the number that denotes the studio for worksheet 2?
Done right I think vlookup should be able to do what you want...

[slaughteredlamb]

I've actually come up with a bit of a workaround which solves that particular issue by using nested IF statements to check if a cell contains a certain value and if it does to enter a name in another cell. This works but because Excel 2003 limits you to 8 nested IF's and we have 15 staff I've had to create two cells with one checking the 1st 8 members of staff and the 2nd checking the next 7.

I now have B22 which will either say a staff members name or "closed" if none of the 1st 8 members of staff have been selected and B23 which will do the same for the next 7 members of staff.

So if no member of staff has been assigned to the studio then both B22 and B23 will say "Closed". If a member of staff from the 1st 8 members of staff is assigned to the Studio then B22 will say their name and B23 will say "Closed" and if a member of staff from the next 7 is selected then B22 will say "Closed" and B23 will say their name.

I now need to write a formula in another cell on another worksheet that looks at B22 and if it says "closed" it looks to B23 and if that says "closed" as well it enters "Closed" in that field. If B22 shows something other than "Closed" it enters that into the cell and if B22 says "closed" but B23 has a name it enters that name into the field.

Probably because I an shattered I can't think how to write this even though I know it should be simple 

[Tony]

=if(b22="closed",if(b23="closed", b22,b23),b22)

[slaughteredlamb]

=if(b22="closed",if(b23="closed", b22,b23),b22)

of course! I've been getting myself all confused by trying far more complex solutions. Cheers Tony!

[Tony]

no probs SL, it's simple, cos I is simple!