Forum Discussion

lstynes1999's avatar
lstynes1999
Copper Contributor
Dec 03, 2023

Formula Help for importing data IF

Hi,

 

I have two spreadsheets, one is a master planner which I want to feed into individual planners. Master planner is in calender format with a different tab for months January to June. There are 5 columns in each day these are support worker, client, time, desc, car (Y/N). The individual planners days have the same headings except they don't have the support worker column. I need a formula which will automtaically populate the individual planners with client, time, desc, car (Y/N) if the individuals name whos planner it is, is in the support work column. I've attached screenshots of roughly how the sheets will look.

 

Thanks in advance!

 

 

 

 

Excel
Excel on Mac
Formulas and Functions
office 365
  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Dec 04, 2023
    I need a formula which will automtaically populate the individual planners with client,

    I do not think a formular available to accomplish this automate task.

    If you upload a test workbook,maybe more talents are interested to join the discussion.
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Dec 04, 2023

    lstynes1999 

    To achieve this, you can use the IF function combined with other functions such as VLOOKUP or INDEX and MATCH. Assuming you have a master planner sheet and individual planner sheets, and you want to populate the client, time, desc, and car (Y/N) columns in the individual planner based on the support worker's name, you can use a formula like the following.

    Let us say your master planner sheet has columns like A (Date), B (Support Worker), C (Client), D (Time), E (Description), and F (Car Y/N).

    Here is an example formula for the Client column in the individual planner:

    =IFERROR(IF(VLOOKUP($A2, 'Master Planner'!$A$2:$F$100, 2, FALSE)=$B$1, VLOOKUP($A2, 'Master Planner'!$A$2:$F$100, 3, FALSE), ""), "")

    In this formula:

    • $A2 refers to the date in the individual planner.
    • 'Master Planner'!$A$2:$F$100 is the range of your master planner data.
    • 2 in VLOOKUP refers to the column index for the support worker name in the master planner.
    • $B$1 is the name of the individual support worker in the individual planner.
    • 3 in VLOOKUP refers to the column index for the client name in the master planner.

    You can adapt this formula for the other columns (Time, Desc, Car Y/N) by changing the column indexes accordingly.

    Copy this formula across the individual planner columns as needed. Adjust the cell references and ranges based on your actual spreadsheet layout and data range.

    This formula checks if the support worker's name in the master planner matches the name in the individual planner, and if so, it populates the corresponding data. If there is no match, it returns an empty string. The IFERROR function is used to handle cases where there might be errors, such as when the date is not found in the master planner. The text and steps were edited with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

    • lstynes1999's avatar
      lstynes1999
      Copper Contributor
      Dec 04, 2023

      NikolinoDE Thanks for your response, I have tried to change the formulas to suit what I have already but can't get it to work. The Master Planner and Individual planners are two different worksheets. I have added the support work name for the individual planner on the 'Legend' sheet and also added a date column as referenced in your formula. This is what my formula looks like =IFERROR(IF(VLOOKUP(A3, '[MASTER PLANNER.xlsx]JAN'!$A$3:$E$8, 1, FALSE)=LEGEND!$C$1, VLOOKUP(A3, '[MASTER PLANNER.xlsx]JAN'!$A$3:$E$8, 1, FALSE), ""), "") but the cells are not populating properly. Can you see why this formula does not work?