Forum Discussion
Fill in cells based on data from other sheet
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
- 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) - 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.
I need to achieve the similar result displayed on the first three sheets based on the data on sheets P, K, H, D.
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", .... )))
