Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-06-2023, 10:13 AM
Deichgraf Deichgraf is offline Auto-populating tables with 3 different conditions Windows 11 Auto-populating tables with 3 different conditions Office 2021
Novice
Auto-populating tables with 3 different conditions
 
Join Date: Sep 2022
Posts: 10
Deichgraf is on a distinguished road
Default Auto-populating tables with 3 different conditions

hm...

Moin again,

I need your help once more:

I want 3 sheets to be filled with data from one table, if certain conditions are met (all Yes or No).
The conditions are "Success", "Kids", "Missed" or "Gone". Only "Missed" and "Gone" are to be separated.
To achieve this I created a helping condition "Work" which checks if either "Missed" or "Gone" are true. So if both are "No" the data entered should be visible in the sheet "Work".
If either of the others is "Yes", the data should be seen in the corresponding sheet, so either "Missed" or "Gone".



I did this once in the past, but I'm drawing blanks for 4 days straight now.

See the attached file for more info.

Edit: If I do a simple =IF(tData[@Work]="No","",INDEX(tData Process],[@DataRow])) I get the right data into the first row in "Work", but I also get empty rows, which I don't want.

Kind regards,
Jack
Attached Files
File Type: xlsx 3Ifs.xlsx (15.2 KB, 4 views)

Last edited by Deichgraf; 02-07-2023 at 09:00 AM.
Reply With Quote
  #2  
Old 02-07-2023, 04:10 AM
p45cal's Avatar
p45cal p45cal is offline Auto-populating tables with 3 different conditions Windows 10 Auto-populating tables with 3 different conditions Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

In the attached is what I think you're looking for.
In the Data sheet I've removed all but the raw data, that is no calculated columns.

In the sheet Missed I've put a formula in cell A2. This is all you need. The result spills down into the rows below. There is no need to copy this formula down:
Code:
=CHOOSECOLS(FILTER(tData,tData[Missed]="Yes","None found"),1,2,6,5)
In cell I2, is the same formula but it doesn't pick and choose the columns returned, it returns all the columns from the Data sheet. Delete this formula (and the headers in row 1), it's only there for your information and to let you check the filtering is correct.

I've done the same for the Gone and Work sheets.
Attached Files
File Type: xlsx msofficeforums503803Ifs.xlsx (16.6 KB, 2 views)
Reply With Quote
  #3  
Old 02-07-2023, 04:36 AM
Deichgraf Deichgraf is offline Auto-populating tables with 3 different conditions Windows 11 Auto-populating tables with 3 different conditions Office 2021
Novice
Auto-populating tables with 3 different conditions
 
Join Date: Sep 2022
Posts: 10
Deichgraf is on a distinguished road
Default

Thank you.

While this does work, I would have to format the sheets as tables manually (without using format as table).
If there is no option to do it within the framework I created I understand and will do so, but I'd prefer to have it using my tables.

In the future, this might contain up to 10000 rows.
Reply With Quote
  #4  
Old 02-07-2023, 07:55 AM
p45cal's Avatar
p45cal p45cal is offline Auto-populating tables with 3 different conditions Windows 10 Auto-populating tables with 3 different conditions Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Hard work, but see attached.
Attached Files
File Type: xlsx msofficeforums50380_3Ifs_v2.xlsx (21.2 KB, 4 views)
Reply With Quote
  #5  
Old 02-07-2023, 08:58 AM
Deichgraf Deichgraf is offline Auto-populating tables with 3 different conditions Windows 11 Auto-populating tables with 3 different conditions Office 2021
Novice
Auto-populating tables with 3 different conditions
 
Join Date: Sep 2022
Posts: 10
Deichgraf is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
Hard work, but see attached.
Fantastic! Thank you very much!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
automatically fill in the details DIMI Excel Programming 12 11-22-2019 04:18 AM
Automatically fill in names in a weekschedule Adile Excel 4 11-16-2016 08:08 AM
Auto-populating tables with 3 different conditions Automatically Fill Cells in Other Worksheets MatthewR Excel 4 07-09-2015 07:13 AM
Word Template: Fill out user E-Mail automatically MS_Maverick Office 0 05-09-2014 12:50 AM
Auto-populating tables with 3 different conditions Automatically fill in bcc prroots Outlook 1 06-29-2010 10:56 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft