Let's say there's a worksheet with 31 columns, with the first row of this worksheet containing the dates for the month of August, i.e. 8/1-8/31, and the second row containing a travel location for each of those dates.
For example, let's say the columns for 8/1-8/8 contain "New York", the columns for 8/9-8/16 contain "London", the columns for 8/17-8/24 contain "New York" again, and the columns for 8/25-8/31 contain "Madrid".
What formulas would you use to create a second worksheet that summarizes each trip found in the first worksheet? Specifically, for the example illustrated above, the second worksheet would automatically generate four columns, i.e. one column for each trip, with the first row showing "8/1-8/8", "8/9-8/16", "8/17-8/24", "8/25-8/31" and the row beneath this showing "New York", "London", "New York", "Madrid".
The key thing is that this summary worksheet would need to automatically update, so if a fifth or sixth trip were added, additional columns would automatically appear to reflect this.
To add another layer of complexity to this, let's say that the first worksheet also contains a row, or perhaps multiple rows, with costs for each day at each location. How would you also show the sum of the costs for each trip in the summary worksheet?
For example, let's say the columns for 8/1-8/8 contain "New York", the columns for 8/9-8/16 contain "London", the columns for 8/17-8/24 contain "New York" again, and the columns for 8/25-8/31 contain "Madrid".
What formulas would you use to create a second worksheet that summarizes each trip found in the first worksheet? Specifically, for the example illustrated above, the second worksheet would automatically generate four columns, i.e. one column for each trip, with the first row showing "8/1-8/8", "8/9-8/16", "8/17-8/24", "8/25-8/31" and the row beneath this showing "New York", "London", "New York", "Madrid".
The key thing is that this summary worksheet would need to automatically update, so if a fifth or sixth trip were added, additional columns would automatically appear to reflect this.
To add another layer of complexity to this, let's say that the first worksheet also contains a row, or perhaps multiple rows, with costs for each day at each location. How would you also show the sum of the costs for each trip in the summary worksheet?
Last edited: