Fill in cells based on data from other sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1439372%22%20slang%3D%22en-US%22%3EFill%20in%20cells%20based%20on%20data%20from%20other%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1439372%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20struggling%20with%20a%20template%20that%20I%20use%20(%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftemplates.office.com%2Fen-us%2Femployee-absence-schedule-tm03987167%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ftemplates.office.com%2Fen-us%2Femployee-absence-schedule-tm03987167%3C%2FA%3E).%20I%20added%20some%20sheets%20that%20are%20the%20basis%20for%20the%20information%20displayed%20in%20the%20month%20sheets.%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20I%20could%20use%20a%20formula%20to%20fill%20in%20the%20necessary%20fields%3F%20As%20we%20have%20over%2070%20workers%2C%20inserting%20all%20that%20data%20manually%20seems%20quite%20primitive%20(as%20I%20have%20done%20so%20far).%20I%20have%20tried%20to%20invent%20some%20kind%20of%20a%20formula%20myself%20but%20failed.%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20illustrate%20the%20situation%2C%20I%20have%20added%20a%20compact%20version%20of%20that%20fail%20that%20I%20use%20(only%20necessary%20cells%20translated).%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1439372%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1439434%22%20slang%3D%22en-US%22%3ERe%3A%20Fill%20in%20cells%20based%20on%20data%20from%20other%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1439434%22%20slang%3D%22en-US%22%3EYou%20can%20use%20power%20query%20to%20get%20data%20from%20individual%20workbooks%20and%20then%20use%20some%20formulas%20on%20the%20data%20to%20find%20what%20you%20need.%20The%20Excel%20file%20you%20provide%20do%20not%20contaion%20much%20info%20about%20what%20you%20need%20to%20achieve.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1439451%22%20slang%3D%22en-US%22%3ERe%3A%20Fill%20in%20cells%20based%20on%20data%20from%20other%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1439451%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232473%22%20target%3D%22_blank%22%3E%40erol%20sinan%20zorlu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20achieve%20the%20similar%20result%20displayed%20on%20the%20first%20three%20sheets%20based%20on%20the%20data%20on%20sheets%20P%2C%20K%2C%20H%2C%20D.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1439574%22%20slang%3D%22en-US%22%3ERe%3A%20Fill%20in%20cells%20based%20on%20data%20from%20other%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1439574%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F687572%22%20target%3D%22_blank%22%3E%40KylliK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%2C%20I'd%20do%20tables%20for%20all%20types%20of%20off-days%20and%20in%20exactly%20the%20same%20format.%20For%20example%2C%20A%20is%20missed.%3C%2FP%3E%0A%3CP%3ESecond%2C%20you%20need%20actual%20dates%2C%20not%20only%20weekday%20names%20or%20days%20numbers.%20As%20variant%2C%20instead%20of%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTEXT(WEEKDAY(DATE(CalendarYear%2C1%2C1)%2C1)%2C%22aaa%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Euse%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DDATE(CalendarYear%2C1%2Ccolumn()-column(%24B%241))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20in%20above%20perhaps%20for%20month%20number%20will%20work%20%3DMONTH(1%26amp%3B%24B%244)%3C%2FP%3E%0A%3CP%3EWith%20that%20you%20may%20use%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(COUNTIFS(tblA%2CtblAnames%2Cname%2C%20tblAstart%2C%20%22%26lt%3B%3D%22%20%24%20day%2C%20tblAend%2C%20%22%26gt%3B%3D%22%20%24%20day)%2C%22A%22%2C%0A%20IF(COUNTIFS(tblB%2CtblAnames%2Cname%2C%20tblBstart%2C%20%22%26lt%3B%3D%22%20%24%20day%2C%20tblBend%2C%20%22%26gt%3B%3D%22%20%24%20day)%2C%22B%22%2C%0A%20....%0A)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1442040%22%20slang%3D%22en-US%22%3ERe%3A%20Fill%20in%20cells%20based%20on%20data%20from%20other%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442040%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20advice.%20I'll%20try%20to%20get%20the%20file%20reorganized%20and%20formulas%20adjusted.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1535564%22%20slang%3D%22en-US%22%3ERe%3A%20Fill%20in%20cells%20based%20on%20data%20from%20other%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1535564%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40Sergei%20Baklan%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20have%20reorganized%20the%20file%20but%20can't%20get%20the%20formula%20correct.%20Can%20you%20please%20help%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1535615%22%20slang%3D%22de-DE%22%3ESubject%3A%20Fill%20in%20cells%20based%20on%20data%20from%20other%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1535615%22%20slang%3D%22de-DE%22%3EExcel%20%3CBR%20%2F%3E%20Start%20Search%20and%20select%20%3CBR%20%2F%3E%20(top%20right%2C%20with%20binoculars%20icon)%20%3CBR%20%2F%3E%20Replace%20Search%20%3CBR%20%2F%3E%20for%3A%20Replace%20%3CBR%20%2F%3E%20by%3A%20Regulate%20options%20as%20desired%20and%20%3CBR%20%2F%3E%20execute.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Thus%20all%20formulas%20or%20links%20from%20example%20copied%20January%20are%20converted%20to%20February.%20%3CBR%20%2F%3E%20Example%3A%20%3D%20OR%20(AND%20(F'%208%26gt%3B%20%3D%20(January!%20'%20I%20'%209%3B))%20replace%20January%20with%20February%20and%20the%20formula%20in%20the%20newly%20copied%20sheet%20then%20becomes%20%3D%20OR%20(AND%20(F'%208%26gt%3B%20%3D%20(February!%20'%209%3B)%20)%20...%20so%20you%20can%20quickly%20change%20all%20the%20formulas%20in%20a%20copied%20sheet.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20If%20my%20answer%20has%20led%20to%20a%20solution%2C%20I%20would%20appreciate%20feedback%2C%20thumbs%20up.%20%3CBR%20%2F%3E%20If%20this%20is%20not%20the%20solution%20you%20search%20please%20ignore%20or%20inform.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1536082%22%20slang%3D%22en-US%22%3ERe%3A%20Fill%20in%20cells%20based%20on%20data%20from%20other%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536082%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F687572%22%20target%3D%22_blank%22%3E%40KylliK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20initial%20formula%20is%3C%2FP%3E%0A%3CP%3E%3DIF(COUNTIFS(P!%24C%243%3A%24C%2414%2C%24D9%2CP!%24E%243%3A%24E%2414%2C%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%22%26gt%3B%3DJanuary!F%244%22%3C%2FSTRONG%3E%3C%2FFONT%3E%2CP!%24F%243%3A%24F%2414%2C%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%22%26lt%3B%3DJanuary!F%244%22%3C%2FSTRONG%3E%3C%2FFONT%3E)%2C%22P%22)%3C%2FP%3E%0A%3CP%3Eis%20to%20be%20changed%20on%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(COUNTIFS(P!%24C%243%3A%24C%2414%2C%24D9%2CP!%24E%243%3A%24E%2414%2C%22%26lt%3B%3D%22%26amp%3BJanuary!F%244%2CP!%24F%243%3A%24F%2414%2C%22%26gt%3B%3D%22%26amp%3BJanuary!F%244)%2C%22P%22%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3BIn%20criteria%20it%20shall%20be%20like%20%22%3D%22%26amp%3BA1%2C%20not%20%22%3DA1%22%3C%2FP%3E%0A%3CP%3EPlus%2C%20you%20concatenate%20names%2C%20please%20be%20sure%20you%20compare%20correct%20column(s)%20%2F%20cell(s)%20with%20names.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20attached%20file%20formulas%20for%20January%20and%20Katrin%20are%20corrected.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am struggling with a template that I use ( https://templates.office.com/en-us/employee-absence-schedule-tm03987167). I added some sheets that are the basis for the information displayed in the month sheets. 

Is there a way I could use a formula to fill in the necessary fields? As we have over 70 workers, inserting all that data manually seems quite primitive (as I have done so far). I have tried to invent some kind of a formula myself but failed. 

To illustrate the situation, I have added a compact version of that fail that I use (only necessary cells translated). 

7 Replies
Highlighted
You can use power query to get data from individual workbooks and then use some formulas on the data to find what you need. The Excel file you provide do not contaion much info about what you need to achieve.
Highlighted

@erol sinan zorlu 

 

I need to achieve the similar result displayed on the first three sheets based on the data on sheets P, K, H, D.

 

Highlighted

@KylliK 

First, I'd do tables for all types of off-days and in exactly the same format. For example, A is missed.

Second, you need actual dates, not only weekday names or days numbers. As variant, instead of

=TEXT(WEEKDAY(DATE(CalendarYear,1,1),1),"aaa")

use

=DATE(CalendarYear,1,column()-column($B$1))

and in above perhaps for month number will work =MONTH(1&$B$4)

With that you may use something like

=IF(COUNTIFS(tblA,tblAnames,name, tblAstart, "<=" $ day, tblAend, ">=" $ day),"A",
 IF(COUNTIFS(tblB,tblAnames,name, tblBstart, "<=" $ day, tblBend, ">=" $ day),"B",
 ....
)))
Highlighted

@Sergei Baklan 

Thanks for the advice. I'll try to get the file reorganized and formulas adjusted. 

Highlighted

@Sergei Baklan 

I have reorganized the file but can't get the formula correct. Can you please help me?

 

 

Highlighted
Excel
Start
Search and select (top right, with binoculars icon)
Replace
Search for:
Replace by:
Regulate options as desired and execute.

Thus all formulas or links from example copied January are converted to February.
Example: = OR (AND (F $ 8> = (January! $ I $ 9;)) replace January with February and the formula in the newly copied sheet then becomes = OR (AND (F $ 8> = (February! $ I $ 9;) ) ... so you can quickly change all the formulas in a copied sheet.

If my answer has led to a solution, I would appreciate feedback, thumbs up.
If this not the solution you search please ignore or inform.

Nikolino
I know I don't know anything (Socrates)
Highlighted

@KylliK 

You initial formula is

=IF(COUNTIFS(P!$C$3:$C$14,$D9,P!$E$3:$E$14,">=January!F$4",P!$F$3:$F$14,"<=January!F$4"),"P")

is to be changed on

=IF(COUNTIFS(P!$C$3:$C$14,$D9,P!$E$3:$E$14,"<="&January!F$4,P!$F$3:$F$14,">="&January!F$4),"P","")

 In criteria it shall be like "="&A1, not "=A1"

Plus, you concatenate names, please be sure you compare correct column(s) / cell(s) with names.

 

In attached file formulas for January and Katrin are corrected.