Generate a table with repeating values based on input parameter

Hello,

I'm trying to make a table where you type input dates, and based off the data in some pre-existing tables, a table would populate that would look like this:

template.png

Per day, all the times, and each time repeated for each venue.
I would like this to be formula-based, since the number of venues may update to be more than 3, meaning there would now be 4 rows per date-and-time. Any idea what the formulas should be per column to achieve this?
Thank you for any help.
-Hugh Goblin

test book.xlsx

I'd really recommend using Power Query to do this.

But if you need to use formula...

Method 1:
In some empty range: Note that I converted Venue list to table.

Please Login or Register  to view this content.

Then copy result as values into range.
Then use Text to columns on range and split by "|". Apply format and sort as desired.

Method 2:
First cell of Date column.

Please Login or Register  to view this content.

First cell of Times.

Please Login or Register  to view this content.

First cell of Venue.

Please Login or Register  to view this content.

@Hugh

SEQUENCE is used to generate list of sequential number in array...
In Excel, dates are stored as # of days since 12/31/1899. 1 = 1/1/1900.

So by using SEQUENCE(EndDate - StartDate + 1,,StartDate,1), you'd create list of dates staring from start date to end date. +1 is used to make # of dates inclusive.

Then IF function is used to generate multiple copies of this array.

Please Login or Register  to view this content.

IF will treat any non-zero numbers as TRUE implicitly. And since you are passing array as logical condition... result will be replicated by number of columns you specify in SEQUENCE.

TOCOL is used to transform array into single column.

In Times column, TOCOL is separated into 2 steps, so that you first repeat Time by number of Venue. Then outer TOCOL uses TRUE in 3rd argument to tell it to scan the array by column to preserve column wise sort (otherwise you'll have result that sorts by numeric order of Time).

Hope that explains it.