Hi,
I am working with Excel 2010 and need to create a summary sheet that returns data from the other tabs in the worksheet based on certain criteria.
The spread sheet I am working on has a separate tab for each holiday package that we sell. Each tab has a table that details (amongst other things) the cost of the holiday, the number of nights and board basis according to a date band of travel (see below for an example).
Tab 1
<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Travel From[/TD]
[TD="align: center"]Travel To[/TD]
[TD="bgcolor: #ffffcc, align: center"]Selling Price PP[/TD]
[TD="align: center"]No of Nts[/TD]
[TD="align: center"]Board Basis[/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]27-Apr-13[/TD]
[TD="align: center"]09-May-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£1,145[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]BB[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]10-May-13[/TD]
[TD="align: center"]31-May-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£1,041[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]HB[/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]01-Jun-13[/TD]
[TD="align: center"]15-Jul-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£864[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]BB[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
Tab 2
<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Travel From[/TD]
[TD="align: center"]Travel To[/TD]
[TD="bgcolor: #ffffcc, align: center"]Selling Price PP[/TD]
[TD="align: center"]No of Nts[/TD]
[TD="align: center"]Board Basis[/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]30-Apr-13[/TD]
[TD="align: center"]08-Jul-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£901[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]HB[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]16-Jul-13[/TD]
[TD="align: center"]07-Aug-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£1,216[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]BB[/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]00-Jan-00[/TD]
[TD="align: center"]00-Jan-00[/TD]
[TD="bgcolor: #ffffcc, align: center"]£0[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
Tab 3
<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Travel From[/TD]
[TD="align: center"]Travel To[/TD]
[TD="bgcolor: #ffffcc, align: center"]Selling Price PP[/TD]
[TD="align: center"]No of Nts[/TD]
[TD="align: center"]Board Basis[/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]30-Apr-13[/TD]
[TD="align: center"]15-May-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£901[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]HB[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]16-May-13[/TD]
[TD="align: center"]30-May-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£850[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]HB[/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]01-Jun-13[/TD]
[TD="align: center"]17-Jun-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£716[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]BB[/TD]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]18-Jun-13[/TD]
[TD="align: center"]30-Jun-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£867[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]HB[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
I want to be able to create a summary sheet that looks at a date range (e.g. 01 May to 30 June) and goes to each tab and finds the cheapest selling price within the specified date band. There may be several date bands that fit but it needs to only return the data for the cheapest selling price. I also want to return the associated information (number of nights and board basis). This is how the summary should look using the examples provided above for a search based on 01 May to 30 June.
<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]Date Range[/TD]
[TD="align: center"]01-May-13[/TD]
[TD="align: center"]30-Jun-13[/TD]
[TD="bgcolor: #ffff00"]<--- Input Date Range[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]Holiday[/TD]
[TD="align: center"]Travel From[/TD]
[TD="align: center"]Travel To[/TD]
[TD="bgcolor: #ffffcc, align: center"]Selling Price PP[/TD]
[TD="align: center"]No of Nts[/TD]
[TD="align: center"]Board Basis[/TD]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]01-Jun-13[/TD]
[TD="align: center"]15-Jul-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£864[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]BB[/TD]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]30-Apr-13[/TD]
[TD="align: center"]08-Jul-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£901[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]HB[/TD]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]01-Jun-13[/TD]
[TD="align: center"]17-Jun-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£716[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]BB[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
I have tried using an array formula with vlookup and match but it doesn’t always work and so I think it might need something more complex.
Other points to consider:
1. I want to be able to amend the date range criteria at any point and for the summary sheet to then update.
2. If there isn’t a date band on the tab that fits exactly within the date range criteria, it needs to return the cheapest price for a date band that partly fits the date range criteria (see “Holiday 1” as an example).
3. Similarly, if the date range on the tab is larger than the specified date range it must also recognise and return this (see “Holiday 2” as an example).
4. Sometimes, there may be more than one row with the same selling price and so ideally, it would return the earliest (chronologically) date band but somehow flag that other dates are available at this price. It doesn't need to return the other date bands - I would go and find these manually.
5. It should ignore any rows with £0 as the price or 00-Jan-00 as the date.
(6. The tables on the tabs are actually the end result of other formulas which, for simplicity, I have not included on this example.)
Any help that you can give would be greatly appreciated as I have been trying to work on this for ages but to no avail.
Thanks in advance!
I am working with Excel 2010 and need to create a summary sheet that returns data from the other tabs in the worksheet based on certain criteria.
The spread sheet I am working on has a separate tab for each holiday package that we sell. Each tab has a table that details (amongst other things) the cost of the holiday, the number of nights and board basis according to a date band of travel (see below for an example).
Tab 1
| * | A | B | C | D | E |
| Holiday 1 | * | * | * | * | |
<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Travel From[/TD]
[TD="align: center"]Travel To[/TD]
[TD="bgcolor: #ffffcc, align: center"]Selling Price PP[/TD]
[TD="align: center"]No of Nts[/TD]
[TD="align: center"]Board Basis[/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]27-Apr-13[/TD]
[TD="align: center"]09-May-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£1,145[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]BB[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]10-May-13[/TD]
[TD="align: center"]31-May-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£1,041[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]HB[/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]01-Jun-13[/TD]
[TD="align: center"]15-Jul-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£864[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]BB[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
Tab 2
| * | A | B | C | D | E |
| Holiday 2 | * | * | * | * | |
| * | * |
<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Travel From[/TD]
[TD="align: center"]Travel To[/TD]
[TD="bgcolor: #ffffcc, align: center"]Selling Price PP[/TD]
[TD="align: center"]No of Nts[/TD]
[TD="align: center"]Board Basis[/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]30-Apr-13[/TD]
[TD="align: center"]08-Jul-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£901[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]HB[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]16-Jul-13[/TD]
[TD="align: center"]07-Aug-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£1,216[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]BB[/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]00-Jan-00[/TD]
[TD="align: center"]00-Jan-00[/TD]
[TD="bgcolor: #ffffcc, align: center"]£0[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
Tab 3
| * | A | B | C | D | E |
| Holiday 3 | * | * | * | * | |
<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Travel From[/TD]
[TD="align: center"]Travel To[/TD]
[TD="bgcolor: #ffffcc, align: center"]Selling Price PP[/TD]
[TD="align: center"]No of Nts[/TD]
[TD="align: center"]Board Basis[/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]30-Apr-13[/TD]
[TD="align: center"]15-May-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£901[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]HB[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]16-May-13[/TD]
[TD="align: center"]30-May-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£850[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]HB[/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]01-Jun-13[/TD]
[TD="align: center"]17-Jun-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£716[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]BB[/TD]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]18-Jun-13[/TD]
[TD="align: center"]30-Jun-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£867[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]HB[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
I want to be able to create a summary sheet that looks at a date range (e.g. 01 May to 30 June) and goes to each tab and finds the cheapest selling price within the specified date band. There may be several date bands that fit but it needs to only return the data for the cheapest selling price. I also want to return the associated information (number of nights and board basis). This is how the summary should look using the examples provided above for a search based on 01 May to 30 June.
| * | A | B | C | D | E | F |
| Summary | * | * | * | * | * | |
| * | From | To | * | * | * | |
| * | * | |||||
| Results: | * | * | * | * | * | |
| Holiday 1 | ||||||
| Holiday 2 | ||||||
| Holiday 3 |
<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]Date Range[/TD]
[TD="align: center"]01-May-13[/TD]
[TD="align: center"]30-Jun-13[/TD]
[TD="bgcolor: #ffff00"]<--- Input Date Range[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]Holiday[/TD]
[TD="align: center"]Travel From[/TD]
[TD="align: center"]Travel To[/TD]
[TD="bgcolor: #ffffcc, align: center"]Selling Price PP[/TD]
[TD="align: center"]No of Nts[/TD]
[TD="align: center"]Board Basis[/TD]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]01-Jun-13[/TD]
[TD="align: center"]15-Jul-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£864[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]BB[/TD]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]30-Apr-13[/TD]
[TD="align: center"]08-Jul-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£901[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]HB[/TD]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]01-Jun-13[/TD]
[TD="align: center"]17-Jun-13[/TD]
[TD="bgcolor: #ffffcc, align: center"]£716[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]BB[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
I have tried using an array formula with vlookup and match but it doesn’t always work and so I think it might need something more complex.
Other points to consider:
1. I want to be able to amend the date range criteria at any point and for the summary sheet to then update.
2. If there isn’t a date band on the tab that fits exactly within the date range criteria, it needs to return the cheapest price for a date band that partly fits the date range criteria (see “Holiday 1” as an example).
3. Similarly, if the date range on the tab is larger than the specified date range it must also recognise and return this (see “Holiday 2” as an example).
4. Sometimes, there may be more than one row with the same selling price and so ideally, it would return the earliest (chronologically) date band but somehow flag that other dates are available at this price. It doesn't need to return the other date bands - I would go and find these manually.
5. It should ignore any rows with £0 as the price or 00-Jan-00 as the date.
(6. The tables on the tabs are actually the end result of other formulas which, for simplicity, I have not included on this example.)
Any help that you can give would be greatly appreciated as I have been trying to work on this for ages but to no avail.
Thanks in advance!
Last edited:
