Work Schedule sign-in sheet formula

Hi all,
I have not posted in a while but I would just like to say thank you to everyone who has helped me on this forum in the past. You guys are great!

Now on to my question. I am trying to write a formula involving 2 separate spreadsheets.

Spreadsheet A is a schedule with names going down column and a list of days going across a, b, c etc. If a person works on the corresponding day I have an X under the corresponding date column.

I need to tie in a column on my sign-in sheet that will only display the names of the people with an x for that day into a name cell. Each sign in sheet I make is for a different day of the week. So I would have a sign in sheet for the 1st of the month with a listing of everyone who has an x under the 1st date column from spreadsheet A.

I was thinking something along the lines of

='[spreadsheet a.xls] “(name cell) if (date column)=”x” elseif (then go on to the next person and check if they have an x on that day)

I am not sure if I am explaining this correctly. Is this even possible?

Any help would be great. Thank you in advance.

I think this is possible.

To start with, let's take the simplest case. Instead of two workbooks, you have one and instead of a sheet for every day of the week, you have only one sheet. How are you going to create a list of only those people with an "x" next to their names?

You have 2 options:
1. use VBA
2. use formulas

You put the question in the "Programming" section, so I'm thinking you want VBA, and this can pretty easily be done using VBA, I think. (Famous last words?)

But, the example you posted is a formula. I think it can also be done with array formulas and Indirect cell references. Strating with the simplest case, the following array formulas will return only row numbers (which can be combined with the INDEX function to bring back cell contents) for those rows that have an x in column A. The first cell would have this formula:

Please Login or Register  to view this content.

each subsequent row will have a formula like this:

Please Login or Register  to view this content.

You can avoid using the Ctrl+Shift+Enter routine if you use the SUMPRODUCT function:

Please Login or Register  to view this content.

Anyway ... let us know if you want to use formulas or VBA.

Lets try VB

Simply amazing, Thank you so much. Works Great!