T
TVGuy29
Hello! So i have this wacky formula that is connecting to a spreadsheet and
returning a result, but it's totally the wrong row of info.
First off, let me introduce you to the infamous formula I am using...
=INDEX([Jasons_calendar.xls]Sheet1!D1999,MATCH(B28+D2,INDEX([Jasons_calendar.xls]Sheet1!A1:A999+B1:B999,,0),0))
Below is a representation of the "Jasons_calendar.xls" spreadsheet I am
trying to pull info from. As you can see, it’s 3 columns of info (A, B & D.
Column 'C' is empty), and 24 hours (24 rows) of shows, with the date and
times they air.
"Jasons_calendar"
A B C D
1 1/31/08 12:00 AM "12amRenato Watches"
2 1/31/08 1:00 AM "1amRenato Watches"
3 1/31/08 2:00 AM "2amTime Zone - Clearance"
4 1/31/08 3:00 AM "3amTime Zone - Clearance"
5 1/31/08 4:00 AM "4amSemi Annual Clearance"
6 1/31/08 5:00 AM "5amSemi Annual Clearance"
7 1/31/08 6:00 AM "6amSemi Annual Clearance"
8 1/31/08 7:00 AM "7amSemi Annual Clearance"
9 1/31/08 8:00 AM "8amPamela McCoy Collection - Clearance"
10 1/31/08 9:00 AM "9amDuet in Gold & Silver - Clearance"
11 1/31/08 10:00 AM "10amLucien Piccard - Clearance"
12 1/31/08 11:00 AM "11amLucien Piccard - Clearance"
13 1/31/08 12:00 PM "12pmHandbag Blowout"
14 1/31/08 1:00 PM "1pmHandbag Blowout"
15 1/31/08 2:00 PM "2pmHandbag Blowout"
16 1/31/08 3:00 PM "3pmPamela McCoy Collection - Clearance"
17 1/31/08 4:00 PM "4pmDuet in Gold & Silver - Clearance"
18 1/31/08 5:00 PM "5pmLucien Piccard - Clearance"
19 1/31/08 6:00 PM "6pmSemi Annual Clearance"
20 1/31/08 7:00 PM "7pmSemi Annual Clearance"
21 1/31/08 8:00 PM "8pmPamela McCoy Collection - Clearance"
22 1/31/08 9:00 PM "9pmTreasures D' Italia - Clearance"
23 1/31/08 10:00 PM "10pmTreasures D' Italia - Clearance"
24 1/31/08 11:00 PM "11pmSemi Annual Clearance"
Below is the spreadsheet that I’ve created. The formula example listed at
the top was taken from the ‘1:00 PM’ (B28) row (which returns "#N/A"). The
formula basically looks at date (always at D2) and time in 4 row incriments
(B4, B8, B12....B28, etc.).
So, the formula is exactly the same for each cell, with the exception of row
number (the time) that the formula is referencing in column B (time).
Here are the results of the formula...
"1st Shift Shows"
A(empty) B(time) C (show name) D (date = D2)
2 1/31/2008
4 7:00 AM "3amTime Zone - Clearance"
8 8:00 AM "7amSemi Annual Clearance"
12 9:00 AM "11amLucien Piccard - Clearance"
16 10:00 AM "3pmPamela McCoy Collection - Clearance"
20 11:00 AM "7pmSemi Annual Clearance"
24 12:00 PM "11pmSemi Annual Clearance"
28 1:00 PM #N/A
32 2:00 PM #N/A
As you can see, the formula returns the totally wrong shows.
Example: 7:00 AM (B4) + 1/31/2008 (D2) = "3amTime Zone - Clearance"
(wha?!). Baffling to me, hehe.
And on all three of my spreadsheets (1st shift, 2nd shift & 3rd shift), the
last 2 time slots consistently return “#N/Aâ€.
Should I be using a VLOOKUP command instead of a MATCH command? Or do i
need a -1 or 1 instead of a 0 for the MATCH command? Maybe i need to go in a
different direction?? Any advice you have will be helpful!
Thanks in advance!
Jason Biwer
returning a result, but it's totally the wrong row of info.
First off, let me introduce you to the infamous formula I am using...
=INDEX([Jasons_calendar.xls]Sheet1!D1999,MATCH(B28+D2,INDEX([Jasons_calendar.xls]Sheet1!A1:A999+B1:B999,,0),0))
Below is a representation of the "Jasons_calendar.xls" spreadsheet I am
trying to pull info from. As you can see, it’s 3 columns of info (A, B & D.
Column 'C' is empty), and 24 hours (24 rows) of shows, with the date and
times they air.
"Jasons_calendar"
A B C D
1 1/31/08 12:00 AM "12amRenato Watches"
2 1/31/08 1:00 AM "1amRenato Watches"
3 1/31/08 2:00 AM "2amTime Zone - Clearance"
4 1/31/08 3:00 AM "3amTime Zone - Clearance"
5 1/31/08 4:00 AM "4amSemi Annual Clearance"
6 1/31/08 5:00 AM "5amSemi Annual Clearance"
7 1/31/08 6:00 AM "6amSemi Annual Clearance"
8 1/31/08 7:00 AM "7amSemi Annual Clearance"
9 1/31/08 8:00 AM "8amPamela McCoy Collection - Clearance"
10 1/31/08 9:00 AM "9amDuet in Gold & Silver - Clearance"
11 1/31/08 10:00 AM "10amLucien Piccard - Clearance"
12 1/31/08 11:00 AM "11amLucien Piccard - Clearance"
13 1/31/08 12:00 PM "12pmHandbag Blowout"
14 1/31/08 1:00 PM "1pmHandbag Blowout"
15 1/31/08 2:00 PM "2pmHandbag Blowout"
16 1/31/08 3:00 PM "3pmPamela McCoy Collection - Clearance"
17 1/31/08 4:00 PM "4pmDuet in Gold & Silver - Clearance"
18 1/31/08 5:00 PM "5pmLucien Piccard - Clearance"
19 1/31/08 6:00 PM "6pmSemi Annual Clearance"
20 1/31/08 7:00 PM "7pmSemi Annual Clearance"
21 1/31/08 8:00 PM "8pmPamela McCoy Collection - Clearance"
22 1/31/08 9:00 PM "9pmTreasures D' Italia - Clearance"
23 1/31/08 10:00 PM "10pmTreasures D' Italia - Clearance"
24 1/31/08 11:00 PM "11pmSemi Annual Clearance"
Below is the spreadsheet that I’ve created. The formula example listed at
the top was taken from the ‘1:00 PM’ (B28) row (which returns "#N/A"). The
formula basically looks at date (always at D2) and time in 4 row incriments
(B4, B8, B12....B28, etc.).
So, the formula is exactly the same for each cell, with the exception of row
number (the time) that the formula is referencing in column B (time).
Here are the results of the formula...
"1st Shift Shows"
A(empty) B(time) C (show name) D (date = D2)
2 1/31/2008
4 7:00 AM "3amTime Zone - Clearance"
8 8:00 AM "7amSemi Annual Clearance"
12 9:00 AM "11amLucien Piccard - Clearance"
16 10:00 AM "3pmPamela McCoy Collection - Clearance"
20 11:00 AM "7pmSemi Annual Clearance"
24 12:00 PM "11pmSemi Annual Clearance"
28 1:00 PM #N/A
32 2:00 PM #N/A
As you can see, the formula returns the totally wrong shows.
Example: 7:00 AM (B4) + 1/31/2008 (D2) = "3amTime Zone - Clearance"
(wha?!). Baffling to me, hehe.
And on all three of my spreadsheets (1st shift, 2nd shift & 3rd shift), the
last 2 time slots consistently return “#N/Aâ€.
Should I be using a VLOOKUP command instead of a MATCH command? Or do i
need a -1 or 1 instead of a 0 for the MATCH command? Maybe i need to go in a
different direction?? Any advice you have will be helpful!
Thanks in advance!
Jason Biwer