trying to display results by date

M

mugwump

New Member
Joined
Feb 3, 2019
Messages
6
I have a workbook that looks at the data page matches the date on the event page and posts the matching date information to the event page from the data page.

(see example below) data list below example. The problem I have is that if the data in the date range is more than 2 events the data does not enter on the events page. As a newbie I have gone as far as I can go. Could some one help me please?

Formula used
=VLOOKUP(B3,root_Event_Search.xlsx!rootevent,2,0)
[TABLE="width: 700"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Event page output
Week commencing [/TD]
[TD]Week ending[/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD]4/02/2019[/TD]
[TD]10/02/2019[/TD]
[TD]​​Pacific Jewel (T) Station Pier, Port Melbourne [/TD]
[TD]HAL Rd 18 - Melbourne Victory v Perth Glory AAMI Park [/TD]
[/TR]
[TR]
[TD]11/02/2019[/TD]
[TD]17/02/2019[/TD]
[TD]University exams Caulfield Racecourse [/TD]
[TD]BBL Final - TBC v TBC MCG [/TD]
[/TR]
</tbody>[/TABLE]
Data page information
[TABLE="width: 1098"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Column1[/TD]
[/TR]
[TR]
[TD="align: right"]15/11/2020[/TD]
[TD]ICC Men's World Twenty20 Final MCG [/TD]
[/TR]
[TR]
[TD="align: right"]8/03/2020[/TD]
[TD]ICC Women's World Twenty20 Final MCG [/TD]
[/TR]
[TR]
[TD="align: right"]24/11/2019[/TD]
[TD]International Convention of Jehovah's Witnesses Marvel Stadium [/TD]
[/TR]
[TR]
[TD="align: right"]23/11/2019[/TD]
[TD]International Convention of Jehovah's Witnesses Marvel Stadium [/TD]
[/TR]
[TR]
[TD="align: right"]22/11/2019[/TD]
[TD]International Convention of Jehovah's Witnesses Marvel Stadium [/TD]
[/TR]
[TR]
[TD="align: right"]22/11/2019[/TD]
[TD]Kiss - End of the Road World Tour Rod Laver Arena [/TD]
[/TR]
[TR]
[TD="align: right"]21/11/2019[/TD]
[TD]Kiss - End of the Road World Tour Rod Laver Arena [/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2019[/TD]
[TD]Shawn Mendes in concert Rod Laver Arena [/TD]
[/TR]
[TR]
[TD="align: right"]30/10/2019[/TD]
[TD]Shawn Mendes in concert Rod Laver Arena [/TD]
[/TR]
[TR]
[TD="align: right"]29/10/2019[/TD]
[TD]Shawn Mendes in concert Rod Laver Arena [/TD]
[/TR]
[TR]
[TD="align: right"]29/10/2019[/TD]
[TD]Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre [/TD]
[/TR]
[TR]
[TD="align: right"]28/10/2019[/TD]
[TD]Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre [/TD]
[/TR]
[TR]
[TD="align: right"]27/10/2019[/TD]
[TD]Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre [/TD]
[/TR]
[TR]
[TD="align: right"]26/10/2019[/TD]
[TD]Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre [/TD]
[/TR]
[TR]
[TD="align: right"]25/10/2019[/TD]
[TD]Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre [/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
is that what you want? (with ALL distinct events)

exaple based on your example

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Min[/td][td=bgcolor:#70AD47]Max[/td][td=bgcolor:#70AD47]Event.1[/td][td=bgcolor:#70AD47]Event.2[/td][td=bgcolor:#70AD47]Event.3[/td][td=bgcolor:#70AD47]Event.4[/td][td=bgcolor:#70AD47]Event.5[/td][td=bgcolor:#70AD47]Event.6[/td][td=bgcolor:#70AD47]Event.7[/td][td=bgcolor:#70AD47]Event.8[/td][td=bgcolor:#70AD47]Event.9[/td][td=bgcolor:#70AD47]Event.10[/td][td=bgcolor:#70AD47]Event.11[/td][td=bgcolor:#70AD47]Event.12[/td][td=bgcolor:#70AD47]Event.13[/td][td=bgcolor:#70AD47]Event.14[/td][td=bgcolor:#70AD47]Event.15[/td][td=bgcolor:#70AD47]Event.16[/td][td=bgcolor:#70AD47]Event.17[/td][td=bgcolor:#70AD47]Event.18[/td][td=bgcolor:#70AD47]Event.19[/td][td=bgcolor:#70AD47]Event.20[/td][td=bgcolor:#70AD47]Event.21[/td][td=bgcolor:#70AD47]Event.22[/td][td=bgcolor:#70AD47]Event.23[/td][td=bgcolor:#70AD47]Event.24[/td][td=bgcolor:#70AD47]Event.25[/td][td=bgcolor:#70AD47]Event.26[/td][td=bgcolor:#70AD47]Event.27[/td][td=bgcolor:#70AD47]Event.28[/td][td=bgcolor:#70AD47]Event.29[/td][td=bgcolor:#70AD47]Event.30[/td][td=bgcolor:#70AD47]Event.31[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
25/02/2019​
[/td][td=bgcolor:#E2EFDA]
03/03/2019​
[/td][td=bgcolor:#E2EFDA]?Golden Princess (T)[/td][td=bgcolor:#E2EFDA]University exams[/td][td=bgcolor:#E2EFDA]Caravan & Camping Super Show 2019[/td][td=bgcolor:#E2EFDA]Bryan Ferry in concert[/td][td=bgcolor:#E2EFDA]Arctic Monkeys - Live 2019[/td][td=bgcolor:#E2EFDA]2019 Australian International Airshow & Aerospace & Defence Expo[/td][td=bgcolor:#E2EFDA]?Noordam[/td][td=bgcolor:#E2EFDA]Europa[/td][td=bgcolor:#E2EFDA]??Carnival Legend (T)[/td][td=bgcolor:#E2EFDA]Summer Night Market[/td][td=bgcolor:#E2EFDA]Europa (T)[/td][td=bgcolor:#E2EFDA]?Crystal Serenity[/td][td=bgcolor:#E2EFDA]2019 JLT Community Series Week 1 - Carlton v Essendon[/td][td=bgcolor:#E2EFDA]SOuth Melbourne Night Market[/td][td=bgcolor:#E2EFDA]?Pacific Eden[/td][td=bgcolor:#E2EFDA]Night Gallops[/td][td=bgcolor:#E2EFDA]Mordialloc Food, Wine & Music Festival[/td][td=bgcolor:#E2EFDA]SANZAAR Super Rugby Union Round 3 - Melbourne Rebels v Highlanders (New Zealand)[/td][td=bgcolor:#E2EFDA]??Pacific Jewel (T)[/td][td=bgcolor:#E2EFDA]Beer Incider 2019[/td][td=bgcolor:#E2EFDA]Australian Guineas Day presented by 3AW[/td][td=bgcolor:#E2EFDA]HAL Rd 21 - Melbourne Victory v Newcastle Jets[/td][td=bgcolor:#E2EFDA]2019 NAB AFLW Round 5 - Carlton v Collingwood[/td][td=bgcolor:#E2EFDA]Ulysses Club National Rally - Grand Parade[/td][td=bgcolor:#E2EFDA]2019 JLT Community Series Week 1 - Nth Melbourne v St Kilda[/td][td=bgcolor:#E2EFDA]2019 JLT Community Series - Week 1 - Richmond v Melbourne[/td][td=bgcolor:#E2EFDA]HAL Rd 21 - Melbourne City v Perth Glory[/td][td=bgcolor:#E2EFDA]Glenferrie Festival 2019[/td][td=bgcolor:#E2EFDA]Commonwealth Bank Women's ODI Series -Australia v New Zealand[/td][td=bgcolor:#E2EFDA]Sheffield Shield Victoria v Tasmania Day 1[/td][td=bgcolor:#E2EFDA]Carnival Legend (T)[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
04/03/2019​
[/td][td]
10/03/2019​
[/td][td]Sheffield Shield Victoria v Tasmania Day 2[/td][td]??Pacific Jewel (T)[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Last edited:
Upvote 0
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Min[/td][td=bgcolor:#70AD47]Max[/td][td=bgcolor:#70AD47]Event.1[/td][td=bgcolor:#70AD47]Event.2[/td][td=bgcolor:#70AD47]Event.3[/td][td=bgcolor:#70AD47]Event.4[/td][td=bgcolor:#70AD47]Event.5[/td][td=bgcolor:#70AD47]Event.6[/td][td=bgcolor:#70AD47]Event.7[/td][td=bgcolor:#70AD47]Event.8[/td][td=bgcolor:#70AD47]Event.9[/td][td=bgcolor:#70AD47]Event.10[/td][td=bgcolor:#70AD47]Event.11[/td][td=bgcolor:#70AD47]Event.12[/td][td=bgcolor:#70AD47]Event.13[/td][td=bgcolor:#70AD47]Event.14[/td][td=bgcolor:#70AD47]Event.15[/td][td=bgcolor:#70AD47]Event.16[/td][td=bgcolor:#70AD47]Event.17[/td][td=bgcolor:#70AD47]Event.18[/td][td=bgcolor:#70AD47]Event.19[/td][td=bgcolor:#70AD47]Event.20[/td][td=bgcolor:#70AD47]Event.21[/td][td=bgcolor:#70AD47]Event.22[/td][td=bgcolor:#70AD47]Event.23[/td][td=bgcolor:#70AD47]Event.24[/td][td=bgcolor:#70AD47]Event.25[/td][td=bgcolor:#70AD47]Event.26[/td][td=bgcolor:#70AD47]Event.27[/td][td=bgcolor:#70AD47]Event.28[/td][td=bgcolor:#70AD47]Event.29[/td][td=bgcolor:#70AD47]Event.30[/td][td=bgcolor:#70AD47]Event.31[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
25/02/2019​
[/td][td=bgcolor:#E2EFDA]
03/03/2019​
[/td][td=bgcolor:#E2EFDA]Golden Princess (T)[/td][td=bgcolor:#E2EFDA]University exams[/td][td=bgcolor:#E2EFDA]Caravan & Camping Super Show 2019[/td][td=bgcolor:#E2EFDA]Bryan Ferry in concert[/td][td=bgcolor:#E2EFDA]Arctic Monkeys - Live 2019[/td][td=bgcolor:#E2EFDA]2019 Australian International Airshow & Aerospace & Defence Expo[/td][td=bgcolor:#E2EFDA]Noordam[/td][td=bgcolor:#E2EFDA]Europa[/td][td=bgcolor:#E2EFDA]Carnival Legend (T)[/td][td=bgcolor:#E2EFDA]Summer Night Market[/td][td=bgcolor:#E2EFDA]Europa (T)[/td][td=bgcolor:#E2EFDA]Crystal Serenity[/td][td=bgcolor:#E2EFDA]2019 JLT Community Series Week 1 - Carlton v Essendon[/td][td=bgcolor:#E2EFDA]SOuth Melbourne Night Market[/td][td=bgcolor:#E2EFDA]Pacific Eden[/td][td=bgcolor:#E2EFDA]Night Gallops[/td][td=bgcolor:#E2EFDA]Mordialloc Food, Wine & Music Festival[/td][td=bgcolor:#E2EFDA]SANZAAR Super Rugby Union Round 3 - Melbourne Rebels v Highlanders (New Zealand)[/td][td=bgcolor:#E2EFDA]Pacific Jewel (T)[/td][td=bgcolor:#E2EFDA]Beer Incider 2019[/td][td=bgcolor:#E2EFDA]Australian Guineas Day presented by 3AW[/td][td=bgcolor:#E2EFDA]HAL Rd 21 - Melbourne Victory v Newcastle Jets[/td][td=bgcolor:#E2EFDA]2019 NAB AFLW Round 5 - Carlton v Collingwood[/td][td=bgcolor:#E2EFDA]Ulysses Club National Rally - Grand Parade[/td][td=bgcolor:#E2EFDA]2019 JLT Community Series Week 1 - Nth Melbourne v St Kilda[/td][td=bgcolor:#E2EFDA]2019 JLT Community Series - Week 1 - Richmond v Melbourne[/td][td=bgcolor:#E2EFDA]HAL Rd 21 - Melbourne City v Perth Glory[/td][td=bgcolor:#E2EFDA]Glenferrie Festival 2019[/td][td=bgcolor:#E2EFDA]Commonwealth Bank Women's ODI Series -Australia v New Zealand[/td][td=bgcolor:#E2EFDA]Sheffield Shield Victoria v Tasmania Day 1[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
04/03/2019​
[/td][td]
10/03/2019​
[/td][td]Sheffield Shield Victoria v Tasmania Day 2[/td][td]Pacific Jewel (T)[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

here is version without question marks

in your source you have hidden characters code 63 or unicode 8203 so clean your data first
 
Upvote 0
[TABLE="width: 578"]
<tbody>[TR]
[TD="width: 145, bgcolor: transparent"]A1
[/TD]
[TD="width: 309, bgcolor: transparent"]B1
[/TD]
[TD="width: 88, bgcolor: transparent"]C1
[/TD]
[TD="width: 88, bgcolor: transparent"]D1
[/TD]
[TD="width: 64, bgcolor: transparent"]E1
[/TD]
[TD="width: 76, bgcolor: transparent"]F1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Data sheet
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Table Name:
[/TD]
[TD="bgcolor: transparent"]tblData
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Data
[/TD]
[TD="bgcolor: transparent"]Data
[/TD]
[TD="bgcolor: transparent"]Formula
[/TD]
[TD="bgcolor: transparent"]Formula
[/TD]
[TD="bgcolor: transparent"]Formula
[/TD]
[TD="bgcolor: transparent"]Formula
[/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD]Event
[/TD]
[TD="width: 88"]Week Beginning
[/TD]
[TD="width: 88"]Week Ending
[/TD]
[TD="width: 64"]Event Index
[/TD]
[TD="width: 76"]Lookup Key
[/TD]
[/TR]
[TR]
[TD]Mon 25-02-19
[/TD]
[TD]University exams
[/TD]
[TD]25-02-19
[/TD]
[TD]03-03-19
[/TD]
[TD]1
[/TD]
[TD]435211
[/TD]
[/TR]
[TR]
[TD]Mon 25-02-19
[/TD]
[TD]​Golden Princess (T)
[/TD]
[TD]25-02-19
[/TD]
[TD]03-03-19
[/TD]
[TD]2
[/TD]
[TD]435212
[/TD]
[/TR]
</tbody>[/TABLE]

NOTE: Cell references (A1, B1, etc.) represent the actual position of those values but are not included on the actual worksheet - only shown here for demonstration purposes to indicate the posiion of rows/columns.

The values in A6, C6 & D6 (and corresponding rows below) are date numbers formatted as dates.
The values in E6 & F6 (and corresponding rows below) are integers.

Formulas: (copy down as required)
C6 = tblData[[#This Row],[Date]] - WEEKDAY( tblData[[#This Row],[Date]], 2) + 1
D6 = tblData[[#This Row],[Week Beginning]] + 6
E6 = COUNTIFS( $C$5:tblData[[#This Row],[Week Beginning]], tblData[[#This Row],[Week Beginning]] )
F6 = ( tblData[[#This Row],[Week Beginning]] & tblData[[#This Row],[Event Index]] ) *1

Event Summary sheet

[TABLE="width: 769"]
<tbody>[TR]
[TD="width: 96, bgcolor: transparent"]A1

[/TD]
[TD="width: 82, bgcolor: transparent"]B1
[/TD]
[TD="width: 82, bgcolor: transparent"]C1
[/TD]
[TD="width: 62, bgcolor: transparent"]D1
[/TD]
[TD="width: 62, bgcolor: transparent"]E1
[/TD]
[TD="width: 92, bgcolor: transparent"]F1
[/TD]
[TD="width: 92, bgcolor: transparent"]G1
[/TD]
[TD="width: 92, bgcolor: transparent"]H1
[/TD]
[TD="width: 92, bgcolor: transparent"]I1
[/TD]
[TD="width: 92, bgcolor: transparent"]J1
[/TD]
[TD="width: 92, bgcolor: transparent"]K1
[/TD]
[TD="width: 92, bgcolor: transparent"]L1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Start Date:
[/TD]
[TD="bgcolor: #FFFFCC"]25-02-19
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Table Name:
[/TD]
[TD="bgcolor: transparent"]tblEvents
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] , colspan: 3"]Events scheduled for each week
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "][/TD]
[/TR]
[TR]
[TD="width: 96"]Week No.
[/TD]
[TD="width: 82"]Week Beginning
[/TD]
[TD="width: 82"]Week Ending
[/TD]
[TD="width: 62"]No. of Events
[/TD]
[TD="width: 62"]Check Calc.
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]25-02-19
[/TD]
[TD]03-03-19
[/TD]
[TD]88
[/TD]
[TD]Error!
[/TD]
[TD="width: 92"]University exams
[/TD]
[TD="width: 92"]​Golden Princess (T)
[/TD]
[TD="width: 92"]Caravan & Camping Super Show 2019
[/TD]
[TD="width: 92"]University exams
[/TD]
[TD="width: 92"]​Golden Princess (T)
[/TD]
[TD="width: 92"]Caravan & Camping Super Show 2019
[/TD]
[TD="width: 92"]University exams
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]04-03-19
[/TD]
[TD]10-03-19
[/TD]
[TD]4
[/TD]
[TD]Ok
[/TD]
[TD="width: 92"]Sheffield Shield Victoria v Tasmania Day 2
[/TD]
[TD="width: 92"]​​Pacific Jewel (T)
[/TD]
[TD="width: 92"]Sheffield Shield Victoria v Tasmania Day 2
[/TD]
[TD="width: 92"]​​Pacific Jewel (T)
[/TD]
[TD="width: 92"]-
[/TD]
[TD="width: 92"]-
[/TD]
[TD="width: 92"]-
[/TD]
[/TR]
</tbody>[/TABLE]

Formulas: (copy down & across as required)
A8 = ROW( tblEvents[[#This Row],[Week Beginning]]) - ROW( tblEvents[[#Headers],[Week No.]])
B8 = IF( tblEvents[[#This Row],[Week No.]] = 1, $B$3, OFFSET( tblEvents[[#This Row],[Week Beginning]], -1,0) + 7 )
C8 = tblEvents[[#This Row],[Week Beginning]] + 6
D8 = COUNTIFS( tblData[Week Beginning], tblEvents[[#This Row],[Week Beginning]] )
E8 = IF( COUNTIFS( tblEvents[[#This Row],[1 ]:[10 ]], "<>" & "-") = tblEvents[[#This Row],[No. of Events]], "Ok", "Error!" )
F8 = IFERROR( INDEX( tblData[Event], MATCH( ( tblEvents[[#This Row],[Week Beginning]] & tblEvents[[#Headers],[1 ]] )*1, tblData[Lookup Key], 0) ), "-" )

The values in F7:L7 are text.
The formulas in F8:L?? (expand as required) need to refer relatively to the field header in row 7 (so be careful when copying within the table)
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top