Formula Help for importing data IF

Copper Contributor

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!

 

 

 

 

Screenshot 2023-12-04 at 09.20.46.pngScreenshot 2023-12-04 at 09.21.23.png

5 Replies
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.

@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.

@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?

Screenshot 2023-12-05 at 09.09.01.png

Would xlookup be useful for this

@lstynes1999 

Thank you for providing more details. It seems there is a small mistake in the formula you provided. Let us correct it.

Assuming your Legend sheet has the Support Worker names in column C and you want to populate data in columns CL, Time, Desc, and Car (Y/N) in the Individual Planner sheet, let's adjust the formula:

=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, 3, FALSE), ""), "")

Here are the changes:

  • I replaced the column index 1 with 3 in the second VLOOKUP function. This change means that the formula will retrieve data from the third column (CL) in the Master Planner when there is a match.

Copy this adjusted formula to the Client (CL) column in your Individual Planner sheet and make similar adjustments for the other columns (Time, Desc, Car (Y/N)). Ensure that the ranges and cell references match your actual spreadsheet layout.

 

If your Excel version supports XLOOKUP, you can use it to achieve the same result.

Here is how you might modify the formula:

=IFERROR(XLOOKUP($A3, '[MASTER PLANNER.xlsx]JAN'!$A$3:$A$8, '[MASTER PLANNER.xlsx]JAN'!$C$3:$C$8, "", 0), "")

In this XLOOKUP formula:

  • $A3 is the date in the Individual Planner.
  • '[MASTER PLANNER.xlsx]JAN'!$A$3:$A$8 is the date column in the Master Planner.
  • '[MASTER PLANNER.xlsx]JAN'!$C$3:$C$8 is the column containing the data you want to retrieve (CL).

This formula checks if the date in the Individual Planner matches any date in the Master Planner and, if so, retrieves the corresponding data from the CL column. If there's no match, it returns an empty string.

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

 

Hope this will help you.