6 replies to this topic

[Valcrist]

I hope this is where I should put this.

I was asked to help with a project at work involving moving some data from one excel spreadsheet to another, and I could use a little help figuring out how the formula would work or if a macro would be involved. I'll try my best to explain the situation.

There are two different spreadsheets, the first one contains a list of employees, the agents they are assigned to, the agents number, a column listing how many hours of work type A they are required to do and another column listing for work type B. The second spreadsheet also has a list of these employees as well as a column showing an employee assigned to them to help with work type B and the agent with his/her number and how much of work type B.

What I need is a formula/macro that will automatically take the relevant information from the first worksheet and post it on the second. The problem is that some of the agents only have work type A and some only B. The second spreadsheet needs to only have either all work B or a mix of work A and B. It can not have any of the agents that have only work type A.

I know this may sound confusing, but I have tried everything from =if formulas to =hlook statments, and tried a mix of both. Maybe there just isn't a way to do this and I"m fighting a lost cause. Anyone here with mad excel skills that can help out?

Rough mock up.

Worksheet 1

| employee | agent | agent # | work A # | work B # | agent2 | agent2 # | work A # | work B # | agent3 | agent3 # | work A # | work B # | etc....
| employee2 | agent | agent # | work A # | work B # | agent2 | agent2 # | work A # | work B # | agent3 | agent3 # | work A # | work B # | etc....
| employee3 | agent | agent # | work A # | work B # | agent2 | agent2 # | work A # | work B # | agent3 | agent3 # | work A # | work B # | etc....

Worksheet 2

| employee | helper | agent | agent # | work B # | agent2 | agent 2# | work B # | agent3 | agent 3# | work B # | etc...
| employee2 | helper2 | agent | agent # | work B # | agent2 | agent 2# | work B # | agent3 | agent 3# | work B # | etc...
| employee3 | helper3 | agent | agent # | work B # | agent2 | agent 2# | work B # | agent3 | agent 3# | work B # | etc...

[astropheed]

this looks like a job for some logical vlookups!

[laycat]

What data needs copying across? I've just tested an offset/match combination that works when both workbooks are open:

=OFFSET([Book1.xlsx]Sheet1!$A$1,MATCH(A1,[Book1.xlsx]Sheet1!$A:A,0)-1,1)

[Valcrist]

The info that is in sheet two needs to match the same thing in sheet 1, thing is the information is not static. every couple of months the agent changes and so does the work A and B amounts. So right now agent 1 may have no A and only B but next month it might be the opposite. The second sheet needs to reflect the data from the first ONLY if the agent has some type of work B wheter that be both A and B or just B.

[laycat]

Ah, let me get this straight:

• The only static columns in worksheet 2 are employee and helper, the rest of the columns are populated from worksheet 1, yes?
  
• Is there a set amount of agents in worksheet 1, or is it just a list of relevant agents for that employee and their assigned work?
  
• I assume you can check for work B by checking that if the number in the work B # cell is zero or not?

[Valcrist]

No, there are anywhere from 10-15 agents in worksheet 1, and yes you can check for work B by check if the number is 0.

[Valcrist]

Ok, so we figured it out, it works out like this.

In the agent code field in the second sheet we used this code:

=IF(VLOOKUP(A8,Sheet1!$A$1:Sheet1!$E:$E,5,FALSE)=0,"NO RETENTION",VLOOKUP(A8,Sheet1!$A$1:Sheet1!$E:$E,3,FALSE))

In the Work B field we used this code:

=IF(C8="NO RETENTION",0,VLOOKUP(A8,Sheet1!$A$1:Sheet1!$E:$E,5,FALSE))

..glad to be done with all that mess.