Vlookup, Match, Index. Help

[welshman010]

Hoping to save myself sometime in work. Is there a way I can achieve the example attached. Any staff member with 1 in coloumn A will result in shifts being listed on other sheet. I tried n failed but sure it can be done. All I need is name date and shift from rota. Tried understanding Vlookup, Match, Index.

Examples I came across did not have data set out like it is on mine, which is something I cant change.

Any help much appreciated

Example2a.xlsm

[k64]

Here, try this:

Please Login or Register  to view this content.

P.S. If anyone does know how to do this with formulas, I'm curious.

[welshman010]

Thanks for trying. Formula would be better for me. My actual file is 12 tabs of rota's. Transfer In sheet is in different workbooks. One for each month. I sure I could tweak a formula to suit.

[k64]

A macro is better for what you're trying to do. It's not difficult to tell a macro to go through 12 sheets or go to another workbook. The way you have your sheet set up, it would take a crazy formula to do what you want.

[welshman010]

Hi K64

I given it a try works great on example. Tried it on actual file I cant get it going. Range on actual file is column AD row 6 downwards. I think its because I had to merge to rows per person.

[k64]

Yes, merged cells create problems. They are almost always avoidable and should be avoided. Without seeing it, I can't say much, except that you could try making it say "For x = 6 to r Step 2" or something similar