Hello! First time Poster, long time lurker.
I have been tasked with working on a solution to generate content based on a data set contained within multiple tables in different worksheets.
To make it simpler I'll name two tables and two worksheets.
Tables containing data will be named Cosmetic, Ortho and Pediatric
Cosmetic Worksheet Contains Table named WS_Cosmetic
Ortho Worksheet contains table name WS_Ortho
Pediatric Worksheet Contains table name WS_Pediatric
Each table has multiple columns. For easier communication, I'll name them Topic, Sub Topic and Stock Caption.
They are set like this because I know the data set is bound to change and be added on to and because there is A LOT of data spread into multiple categories and sub categories.
These Topics and Sub Topics are then available to select from a drop down menu in a Worksheet named INPUT
This list is basically a Multi Level Dependent drop down list.
I.E: I select Cosmetic from the topic drop down menu, Sub Topic will only allow me to choose what has been marked as a Cosmetic Sub Topic (in another worksheet as well). (Using =INDIRECT(SUBSTITUTE(a4," ","_")) formula) and so on.
My mission:
I need to be able to select 12 sub topics from the drop down menus, and generate in a separate worksheet (in my case named Calendar) non recurring results based on the sub topics selected from the drop down menu (Cells B4:B16)
I.E:
I've found the =INDEX(data,RANDBETWEEN(1,ROWS(data)),1) formula, but am struggling on a couple of things:
Firstly, how do I make it look at the selection of topics, and how do I make it search in all of my worksheets?
Minisheets:
Drop down:
Cosmetics Sheet:
Ortho Sheet:
Pediatric Sheet:
Topics List:
SubTopics List:
Sorry in advance if I'm bad at explaining, I'm not quite fluent in english :P
Cheers!
I have been tasked with working on a solution to generate content based on a data set contained within multiple tables in different worksheets.
To make it simpler I'll name two tables and two worksheets.
Tables containing data will be named Cosmetic, Ortho and Pediatric
Cosmetic Worksheet Contains Table named WS_Cosmetic
Ortho Worksheet contains table name WS_Ortho
Pediatric Worksheet Contains table name WS_Pediatric
Each table has multiple columns. For easier communication, I'll name them Topic, Sub Topic and Stock Caption.
They are set like this because I know the data set is bound to change and be added on to and because there is A LOT of data spread into multiple categories and sub categories.
These Topics and Sub Topics are then available to select from a drop down menu in a Worksheet named INPUT
This list is basically a Multi Level Dependent drop down list.
I.E: I select Cosmetic from the topic drop down menu, Sub Topic will only allow me to choose what has been marked as a Cosmetic Sub Topic (in another worksheet as well). (Using =INDIRECT(SUBSTITUTE(a4," ","_")) formula) and so on.
My mission:
I need to be able to select 12 sub topics from the drop down menus, and generate in a separate worksheet (in my case named Calendar) non recurring results based on the sub topics selected from the drop down menu (Cells B4:B16)
I.E:
I've found the =INDEX(data,RANDBETWEEN(1,ROWS(data)),1) formula, but am struggling on a couple of things:
Firstly, how do I make it look at the selection of topics, and how do I make it search in all of my worksheets?
Minisheets:
| Master-Social-Media-Library-Sheet-Separation.xlsx | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 2 | Month 1 | 09-01-2022 | #VALUE! | |||
| 3 | 09-08-2022 | #VALUE! | ||||
| 4 | 09-15-2022 | #VALUE! | ||||
| 5 | 09-22-2022 | #VALUE! | ||||
| 6 | Month 2 | 09-29-2022 | #VALUE! | |||
| 7 | 10-06-2022 | #VALUE! | ||||
| 8 | 10-13-2022 | #VALUE! | ||||
| 9 | 10-20-2022 | #VALUE! | ||||
| 10 | Month 3 | 10-27-2022 | #VALUE! | |||
| 11 | 11-03-2022 | #VALUE! | ||||
| 12 | 11-10-2022 | #VALUE! | ||||
| 13 | 11-17-2022 | #VALUE! | ||||
| 14 | Month 4 | 11-24-2022 | #VALUE! | |||
| 15 | 12-01-2022 | #VALUE! | ||||
| 16 | 12-08-2022 | #VALUE! | ||||
| 17 | 12-15-2022 | #VALUE! | ||||
| 18 | Month 5 | 12-22-2022 | #REF! | |||
| 19 | 12-29-2022 | #REF! | ||||
| 20 | 01-05-2023 | #REF! | ||||
| 21 | 01-12-2023 | #REF! | ||||
| 22 | Month 6 | 01-19-2023 | #REF! | |||
| 23 | 01-26-2023 | #REF! | ||||
| 24 | 02-02-2023 | #REF! | ||||
| 25 | 02-09-2023 | #REF! | ||||
| 26 | Month 7 | 02-16-2023 | #REF! | |||
| 27 | 02-23-2023 | #REF! | ||||
| 28 | 03-02-2023 | #REF! | ||||
| 29 | 03-09-2023 | #REF! | ||||
| 30 | Month 8 | 03-16-2023 | #REF! | |||
| 31 | 03-23-2023 | #REF! | ||||
| 32 | 03-30-2023 | #REF! | ||||
| 33 | 04-06-2023 | #REF! | ||||
| 34 | Month 9 | 04-13-2023 | #REF! | |||
| 35 | 04-20-2023 | #REF! | ||||
| 36 | 04-27-2023 | #REF! | ||||
| 37 | 05-04-2023 | #REF! | ||||
| 38 | Month 10 | 05-11-2023 | #REF! | |||
| 39 | 05-18-2023 | #REF! | ||||
| 40 | 05-25-2023 | #REF! | ||||
| 41 | 06-01-2023 | |||||
| 42 | Month 11 | 06-08-2023 | #REF! | |||
| 43 | 06-15-2023 | |||||
| 44 | 06-22-2023 | |||||
| 45 | 06-29-2023 | |||||
| 46 | Month 12 | 07-06-2023 | ||||
| 47 | 07-13-2023 | |||||
| 48 | 07-20-2023 | |||||
| 49 | 07-27-2023 | |||||
Calendar | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2:D17 | D2 | =INDEX(,RANDBETWEEN(1,ROWS(data)),1) |
| D18:D40 | D18 | =INDEX(Table24[Sub Topic],RANDBETWEEN(1,ROWS(Table1[Final Caption])),1) |
| D42 | D42 | =TRANSPOSE(#REF!) |
| C2 | C2 | =INPUT!H1 |
| C3:C49 | C3 | =C2+7 |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
Drop down:
| Master-Social-Media-Library-Sheet-Separation.xlsx | ||||
|---|---|---|---|---|
| A | B | |||
| 3 | Topic | Sub Topic | ||
| 4 | Cosmetic | Smile Makeover | ||
| 5 | Ortho | Clear Aligners | ||
| 6 | Pediatric | Pediatric Dentistry (General) | ||
| 7 | Cosmetic | Smile Makeover | ||
| 8 | Ortho | Clear Aligners | ||
| 9 | Pediatric | Pediatric Dentistry (General) | ||
| 10 | Cosmetic | Smile Makeover | ||
| 11 | Ortho | Clear Aligners | ||
| 12 | Pediatric | Pediatric Dentistry (General) | ||
| 13 | Cosmetic | Smile Makeover | ||
| 14 | Ortho | Clear Aligners | ||
| 15 | Pediatric | Pediatric Dentistry (General) | ||
| 16 | Cosmetic | Smile Makeover | ||
INPUT | ||||
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| A4:A16 | List | =TB_Topics |
| B4:B16 | List | =INDIRECT(SUBSTITUTE(A4," ","_")) |
Cosmetics Sheet:
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2:D55 | D2 | =IMAGE((SUBSTITUTE(E2,"?dl=0","?dl=1")), 1) |
| F2:F55 | F2 | =SUBSTITUTE(SUBSTITUTE(C2, "[PracticeName]",INPUT!$B$1),"[PhoneNumber]",INPUT!$E$1) |
Ortho Sheet:
| Master-Social-Media-Library-Sheet-Separation.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Topic | Sub Topic | Stock Caption | Image Preview | Image Link | Final Caption | NB Topic | NB Sub Topic | ||
| 2 | Ortho | Braces | At [PracticeName], we often recommend braces to teenagers who have issues with crooked teeth, crowding, and more. We will work with you to create a treatment plan that works for your child. Give our office a call today at [PhoneNumber] to schedule a consultation appointment. | #NAME? | https://www.dropbox.com/s/m9bdix6vj155a5r/1063175_Social%20Media%20Posts%20-%20May%202021_08b_051321.jpg?dl=0 | At Julias and Joes Teeth Pull Place, we often recommend braces to teenagers who have issues with crooked teeth, crowding, and more. We will work with you to create a treatment plan that works for your child. Give our office a call today at (905) 616-6316 to schedule a consultation appointment. | Ortho 1 | Braces 1 | ||
| 3 | Ortho | Clear Aligners | Smile like you mean it! Give our office a call at [PhoneNumber] to ask about our clear aligner treatments, and get a straight, dazzling smile you are proud to show off! | #NAME? | https://www.dropbox.com/s/zichecn7yo347ou/Invisalign_April.jpg?dl=0 | Smile like you mean it! Give our office a call at (905) 616-6316 to ask about our clear aligner treatments, and get a straight, dazzling smile you are proud to show off! | Ortho 2 | Clear Aligners 1 | ||
| 4 | Ortho | Clear Aligners | With clear aligners from [PracticeName], you can get a straight, dazzling smile with a series of clear aligners that will move your teeth into place while looking natural and discreet. Give our office a call today at [PhoneNumber] to learn more! | #NAME? | https://www.dropbox.com/s/seon320jgz9h746/2.jpg?dl=0 | With clear aligners from Julias and Joes Teeth Pull Place, you can get a straight, dazzling smile with a series of clear aligners that will move your teeth into place while looking natural and discreet. Give our office a call today at (905) 616-6316 to learn more! | Ortho 3 | Clear Aligners 2 | ||
| 5 | Ortho | Clear Aligners | Clear aligners are a great option for orthodontic treatment, allowing you to improve your smile without the hassle of traditional braces. If you are interested in treatment with clear aligners, give our office a call today at [PhoneNumber] to schedule a consultation. | #NAME? | https://www.dropbox.com/s/1soekusqeb8axyn/8.png?dl=0 | Clear aligners are a great option for orthodontic treatment, allowing you to improve your smile without the hassle of traditional braces. If you are interested in treatment with clear aligners, give our office a call today at (905) 616-6316 to schedule a consultation. | Ortho 4 | Clear Aligners 3 | ||
| 6 | Ortho | Clear Aligners | With clear aligners, you can straighten your teeth without the unsightly appearance and hassle of traditional metal braces. Call our office today at [PhoneNumber] to schedule a consultation! | #NAME? | https://www.dropbox.com/s/3fogu478brzobhe/12-.jpg?dl=0 | With clear aligners, you can straighten your teeth without the unsightly appearance and hassle of traditional metal braces. Call our office today at (905) 616-6316 to schedule a consultation! | Ortho 5 | Clear Aligners 4 | ||
| 7 | Ortho | Clear Aligners | With clear aligners, you can get the orthodontic treatment you want without the discomfort and hassle that comes with traditional braces. If you are looking to get a straighter smile, give our office a call today at [PhoneNumber] to schedule your consultation! | #NAME? | https://www.dropbox.com/s/rs9mdub4xm3ef73/3.jpg?dl=0 | With clear aligners, you can get the orthodontic treatment you want without the discomfort and hassle that comes with traditional braces. If you are looking to get a straighter smile, give our office a call today at (905) 616-6316 to schedule your consultation! | Ortho 6 | Clear Aligners 5 | ||
| 8 | Ortho | Clear Aligners | Interested in clear aligners? At [PracticeName], we offer orthodontic treatment with clear aligners, allowing you to improve your smile without the need for traditional braces. Give our office a call today at [PhoneNumber] to schedule a consultation. | #NAME? | https://www.dropbox.com/s/6rxxk6776jgl5xu/Clear-Aligners-1.png?dl=0 | Interested in clear aligners? At Julias and Joes Teeth Pull Place, we offer orthodontic treatment with clear aligners, allowing you to improve your smile without the need for traditional braces. Give our office a call today at (905) 616-6316 to schedule a consultation. | Ortho 7 | Clear Aligners 6 | ||
| 9 | Ortho | Clear Aligners | Straight teeth without wires and brackets? Clear aligner treatment from [PracticeName] can help you get a dazzling, straight smile with a series of clear trays work throughout the day and night. If you would like to learn more about clear aligners, contact us today at [PhoneNumber] and we will gladly answer any questions you have. | #NAME? | https://www.dropbox.com/s/kxaees3k28rzfix/Clear-Aligners-2.png?dl=0 | Straight teeth without wires and brackets? Clear aligner treatment from Julias and Joes Teeth Pull Place can help you get a dazzling, straight smile with a series of clear trays work throughout the day and night. If you would like to learn more about clear aligners, contact us today at (905) 616-6316 and we will gladly answer any questions you have. | Ortho 8 | Clear Aligners 7 | ||
| 10 | Ortho | Clear Aligners | Discreetly straighten your teeth with clear aligners from [PracticeName]. If you are interested in learning more about how clear aligners can improve your smile, give us a call today at [PhoneNumber]. | #NAME? | https://www.dropbox.com/s/a3xc35ubwnd8fer/Clear-Aligners-3.png?dl=0 | Discreetly straighten your teeth with clear aligners from Julias and Joes Teeth Pull Place. If you are interested in learning more about how clear aligners can improve your smile, give us a call today at (905) 616-6316. | Ortho 9 | Clear Aligners 8 | ||
| 11 | Ortho | Clear Aligners | Invisible braces?! Clear aligners from [PracticeName] move your teeth with a series of clear trays, custom made to fit your smile and get the results you want. Call us today at [PhoneNumber] to schedule your consultation and start your journey towards a straighter smile! | #NAME? | https://www.dropbox.com/s/tuaqgx83hhhetru/Clear-Aligners-5.png?dl=0 | Invisible braces?! Clear aligners from Julias and Joes Teeth Pull Place move your teeth with a series of clear trays, custom made to fit your smile and get the results you want. Call us today at (905) 616-6316 to schedule your consultation and start your journey towards a straighter smile! | Ortho 10 | Clear Aligners 9 | ||
| 12 | Ortho | Clear Aligners | Clear aligners from [PracticeName] are a great alternative to traditional braces. They move the teeth into position using a series of clear trays, helping you achieve the orthodontic results you desire without the hassle of wires and brackets. If you are interested in clear aligner treatment, give our team a call today at [PhoneNumber] to schedule a consultation. | #NAME? | https://www.dropbox.com/s/y1yhljxtvhsvgjf/Clear-Aligners-6.png?dl=0 | Clear aligners from Julias and Joes Teeth Pull Place are a great alternative to traditional braces. They move the teeth into position using a series of clear trays, helping you achieve the orthodontic results you desire without the hassle of wires and brackets. If you are interested in clear aligner treatment, give our team a call today at (905) 616-6316 to schedule a consultation. | Ortho 11 | Clear Aligners 10 | ||
| 13 | Ortho | Clear Aligners | Clear aligners make orthodontic treatment a breeze! With clear aligners from [PracticeName], you solve a variety of orthodontic issues, giving you a smile you feel proud to show off. If you are interested in learning how clear aligners could improve your smile, contact us today at [PhoneNumber] to schedule an appointment. | #NAME? | https://www.dropbox.com/s/kxt8te4t151sx6v/Smile-Makeover-9.png?dl=0 | Clear aligners make orthodontic treatment a breeze! With clear aligners from Julias and Joes Teeth Pull Place, you solve a variety of orthodontic issues, giving you a smile you feel proud to show off. If you are interested in learning how clear aligners could improve your smile, contact us today at (905) 616-6316 to schedule an appointment. | Ortho 12 | Clear Aligners 11 | ||
| 14 | Ortho | Clear Aligners | Clear aligners are a clear alternative to traditional orthodontic treatment with braces. If you are interested in clear aligner treatment at [PracticeName], give us a call today at [PhoneNumber] to schedule an appointment. | #NAME? | https://www.dropbox.com/s/le1x8nl0z7606el/Smile-Makeover-11.png?dl=0 | Clear aligners are a clear alternative to traditional orthodontic treatment with braces. If you are interested in clear aligner treatment at Julias and Joes Teeth Pull Place, give us a call today at (905) 616-6316 to schedule an appointment. | Ortho 13 | Clear Aligners 12 | ||
| 15 | Ortho | Clear Aligners | If you are interested in learning more about the clear aligner treatments here at [PracticeName], contact our office today at [PhoneNumber] to schedule a consultation. | #NAME? | https://www.dropbox.com/s/ykb6v4g9n1zl6b7/Clear-Aligners-4.png?dl=0 | If you are interested in learning more about the clear aligner treatments here at Julias and Joes Teeth Pull Place, contact our office today at (905) 616-6316 to schedule a consultation. | Ortho 14 | Clear Aligners 13 | ||
| 16 | Ortho | Invisalign | Fall in love with your smile! Call our office today at [PhoneNumber] to ask about Invisalign, and get a straight, dazzling smile you are proud to show off! | #NAME? | https://www.dropbox.com/s/ista8s4zsdwaysh/Invisalign_March-01.png?dl=0 | Fall in love with your smile! Call our office today at (905) 616-6316 to ask about Invisalign, and get a straight, dazzling smile you are proud to show off! | Ortho 15 | Invisalign 1 | ||
Ortho | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2:D16 | D2 | =IMAGE((SUBSTITUTE(E2,"?dl=0","?dl=1")), 1) |
| F2:F16 | F2 | =SUBSTITUTE(SUBSTITUTE(C2, "[PracticeName]",INPUT!$B$1),"[PhoneNumber]",INPUT!$E$1) |
Pediatric Sheet:
Topics List:
| Master-Social-Media-Library-Sheet-Separation.xlsx | |||
|---|---|---|---|
| A | |||
| 2 | Topics | ||
| 3 | Cosmetic | ||
| 4 | Ortho | ||
| 5 | Endodontics | ||
| 6 | General Dentistry | ||
| 7 | Periodontics | ||
| 8 | Oral Surgery | ||
| 9 | Restorative | ||
| 10 | Pediatric | ||
| 11 | MedSpa | ||
| 12 | Holiday | ||
Topics | |||
SubTopics List:
| Master-Social-Media-Library-Sheet-Separation.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 16 | Cosmetic | Ortho | Endodontics | General Dentistry | Periodontics | Oral Surgery | Restorative | Pediatric | MedSpa | Holiday | ||
| 17 | Cosmetic Dentistry (General) | Braces | CBCT | Cleaning | Deep Cleaning | All-On-4 | Crowns | Cleaning & Exam | Anti-Aging | December Holidays | ||
| 18 | Smile Makeover | Clear Aligners | Endodontic Surgery | Cleaning & Exam | Laser Dentistry | Dental Implants | Dental Bridge | Dental Facts | Body and Facial Contouring | New Year | ||
| 19 | Teeth Whitening | Invisalign | Endodontics | Dental Care (General) | Gummy Smile Treatment | Bone Grafting | Dental Implants | Fillings | Botox | |||
| 20 | Veneers | Retreatments | Emergency Dentistry | Periodontal Disease | Wisdom Teeth Removal | Fluoride Treatment | Cellulite Reduction | |||||
| 21 | Root Canals | Exam | Periodontics (General) | Impacted Teeth | Pediatric Dentistry (General) | |||||||
| 22 | Fluoride Treatment | Extractions | Sealants | |||||||||
| 23 | Oral Cancer Screening | X-Ray | ||||||||||
| 24 | Sealants | |||||||||||
| 25 | Sedation Dentistry | |||||||||||
| 26 | Sleep Apnea | |||||||||||
| 27 | X-Rays | |||||||||||
Topics | ||||||||||||
Sorry in advance if I'm bad at explaining, I'm not quite fluent in english :P
Cheers!
