Updating worksheet data into another worksheet

Hi All,

I have an requirement to update the data from worksheet2 into worksheet1 for specific name, date from and to in Name, From To columns selected by user in worksheet2 and highlight in date range. Please find attached sample file for this.

Thanks & Best Regards,
Shail
RMT.xlsx
LVL 3
Shailesh ShindeLocalization Engineering & AutomationAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Updated formula, start in E4 and then drag right and down:

=COUNTIFS(Sheet2!$I:$I,$C4,Sheet2!$J:$J,"<="&E$3,Sheet2!$K:$K,">="&E$3)

This will put the count of occurrences that match the dates. You can then use different conditional formatting to highlight where you have overlapping allocations.
0
 
Rob HensonFinance AnalystCommented:
Do you want the cells just highlighted or do you want a value populated in the fields in the relevant days?

If just a value, what value? The following will put "X" where relevant:

=IF(AND(VLOOKUP($C4,Sheet2!$I:$K,2,FALSE)<=E$3,VLOOKUP($C4,Sheet2!$I:$K,3,FALSE)>=E$3),"X","")

If you just need highlighting then you can use this formula in conditional formatting:

=AND(VLOOKUP($C4,Sheet2!$I:$K,2,FALSE)<=E$3,VLOOKUP($C4,Sheet2!$I:$K,3,FALSE)>=E$3)

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
Hold fire, doesn't work with Resource 2 because of multiple lines. I will take another look.

With another look, Resource 2 is being allocated to two jobs, Job 2 from 14 to 16 Nov and Job 3 from 11 to 16 Nov; is this a clash of Resource allocation? Would this happen in reality or is it just sample data.

I assume however, if Resource 2 were then reallocated to lets say 18 to 23 Nov for Job 3 you would expect the chart to update to the relevant days.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Shailesh ShindeLocalization Engineering & AutomationAuthor Commented:
Hi Rob H,
Basically needs to be highlighted.
However, the count of occurrences will be an very good idea which further can be done with highlighter.
""=COUNTIFS(Sheet2!$I:$I,$C4,Sheet2!$J:$J,"<="&E$3,Sheet2!$K:$K,">="&E$3)""

Thanks,
Shail
0
 
Rob HensonFinance AnalystCommented:
Glad to help, see your file uploaded below with conditional formatting applied; green highlight shows 1 allocation, red highlight shows more than 1 allocation.

The formula can be deleted from the cells if so required.
RMT.xlsx
0
 
Shailesh ShindeLocalization Engineering & AutomationAuthor Commented:
Thanks for the updated file with highlighted options.

Thanks,
Shail
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.