t0ny84
Board Regular
- Joined
- Jul 6, 2020
- Messages
- 205
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
- Mobile
- Web
Hi All,
I am hoping someone can point me in the right direction before I pull my hair out. I currently have a workbook with 2 sheets.
Sheet 1 - Home has blank cells except for A1 which is where a user enters the date they want to use.
Sheet 2 - Has a Table (named Table1) with 3 columns - Date, Event, Frequency
I am trying to figure out the INDEX\MATCH formula to have the values from Sheet 2 - Table 1 show on Sheet 1 based on the date entered into A1.
On another spreadsheet which was setup similar I used the formula below but whenever I modify it (change Table1[Title] to Table1[Event] it errors.
Used before - =IFERROR(INDEX(Table1[Title],SMALL(IF(Calendar!$K$3=INT(Table1[Date]),MATCH(ROW(Table1[Date]),ROW(Table1[Date])),""),ROW(A1))),"")
Modified - =IFERROR(INDEX(Table1[Event],SMALL(IF(A1=INT(Table1[Date]),MATCH(ROW(Table1[Date]),ROW(Table1[Date])),""),ROW(A1))),"")
If a copy of the spreadsheet or screenshots would make figuring this out easier let me know and I can upload when I am at work tomorrow (Australian Time)
t0ny84
I am hoping someone can point me in the right direction before I pull my hair out. I currently have a workbook with 2 sheets.
Sheet 1 - Home has blank cells except for A1 which is where a user enters the date they want to use.
Sheet 2 - Has a Table (named Table1) with 3 columns - Date, Event, Frequency
I am trying to figure out the INDEX\MATCH formula to have the values from Sheet 2 - Table 1 show on Sheet 1 based on the date entered into A1.
On another spreadsheet which was setup similar I used the formula below but whenever I modify it (change Table1[Title] to Table1[Event] it errors.
Used before - =IFERROR(INDEX(Table1[Title],SMALL(IF(Calendar!$K$3=INT(Table1[Date]),MATCH(ROW(Table1[Date]),ROW(Table1[Date])),""),ROW(A1))),"")
Modified - =IFERROR(INDEX(Table1[Event],SMALL(IF(A1=INT(Table1[Date]),MATCH(ROW(Table1[Date]),ROW(Table1[Date])),""),ROW(A1))),"")
If a copy of the spreadsheet or screenshots would make figuring this out easier let me know and I can upload when I am at work tomorrow (Australian Time)
t0ny84