Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-04-2019, 09:31 PM
FreakMonster FreakMonster is offline Generate Excel data list from complex matrix Windows 10 Generate Excel data list from complex matrix Office 2016
Novice
Generate Excel data list from complex matrix
 
Join Date: Dec 2019
Posts: 3
FreakMonster is on a distinguished road
Default Generate Excel data list from complex matrix

I have a matrix which shows various courses by major and levels at a university. Tagging is by Required and Optional courses.

Need help to show the list view of required and optional courses based on your selection of major and and level - I have already created a data validation list for that.



Excel file attached.
Attached Files
File Type: xlsx course selection.xlsx (13.3 KB, 9 views)
Reply With Quote
  #2  
Old 12-05-2019, 05:19 AM
ArviLaanemets ArviLaanemets is offline Generate Excel data list from complex matrix Windows 8 Generate Excel data list from complex matrix Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Design your app as database. This way you don't need to design it anew whenever new courses, majors, or major levels are added. And you also will have the possibility to add other reports without any base design changes in future.


Attached is an example. There are some hidden columns on sheets, and a hidden sheet needed to get Tables on report sheet without row gaps.


When you need a table like in your example, then this will be another report.
Attached Files
File Type: xlsx CourseSelection.xlsx (44.4 KB, 9 views)
Reply With Quote
  #3  
Old 12-05-2019, 12:12 PM
FreakMonster FreakMonster is offline Generate Excel data list from complex matrix Windows 10 Generate Excel data list from complex matrix Office 2016
Novice
Generate Excel data list from complex matrix
 
Join Date: Dec 2019
Posts: 3
FreakMonster is on a distinguished road
Default

Thank you soo much!! This is a terrific way - have not used it before!!
Reply With Quote
  #4  
Old 12-06-2019, 08:51 AM
p45cal's Avatar
p45cal p45cal is offline Generate Excel data list from complex matrix Windows 10 Generate Excel data list from complex matrix 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

Another way in the attached.

Not especially easy-to-maintain array-entered formulae in blue shaded cells.
I altered your data validation because yours didn't seem quite right.
Attached Files
File Type: xlsx msofficeforums43988course selection.xlsx (12.8 KB, 6 views)
Reply With Quote
  #5  
Old 12-06-2019, 12:08 PM
FreakMonster FreakMonster is offline Generate Excel data list from complex matrix Windows 10 Generate Excel data list from complex matrix Office 2016
Novice
Generate Excel data list from complex matrix
 
Join Date: Dec 2019
Posts: 3
FreakMonster is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
Another way in the attached.

Not especially easy-to-maintain array-entered formulae in blue shaded cells.
I altered your data validation because yours didn't seem quite right.
I was trying to do an array method before but it did not work. Thanks for correcting it.
What was the logic of using Small function? I have only used it in the past to get the lowest value in the dataset.
Reply With Quote
  #6  
Old 12-06-2019, 12:22 PM
p45cal's Avatar
p45cal p45cal is offline Generate Excel data list from complex matrix Windows 10 Generate Excel data list from complex matrix 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

That's exactly what I'm using it for.
Use Evaluate Formula on the Formulas ribbon:
Here's a link to the image: Box ]
Here the 2nd smallest value is being used.
Reply With Quote
Reply

Tags
list view, matrix

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto populate form (data from excel) in Word based on drop down list selection (data from excel) wvlls Word VBA 1 03-22-2019 02:29 PM
Generate Excel data list from complex matrix Extract a matrix of another matrix defined by selected rows marked with an x Tommy7571 Excel 19 03-20-2019 08:39 AM
VBA or Macros to generate List asante_za Excel Programming 1 09-29-2017 09:51 AM
Generate Excel data list from complex matrix 2nd matrix gives a resut that doesnīt match numbers in the 1st matrix Diver Excel 25 02-22-2017 04:39 PM
Generate Excel data list from complex matrix trasforming word list in Excel matrix abury Word VBA 1 02-15-2016 03:35 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:49 PM.


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