Hello!
I have a spreadsheet that I'm using with three different sheets (Sheet1, Sheet2, Sheet3). In Sheet 1 I have a dropdown list (C4) that contains 20 types of licenses. When a license is clicked, cells c2:c4 will autopopulate with what type of test the person needs for the license (there can be as little as 1 test and as many as 4). Cells d4:F4 contain recommended test-by dates. Those recommended test by dates are housed in Sheet3. A2:16 are filled with the different tests and as of now B:I are filled with test dates. In Sheet 3 cell A1= today's date.
Obviously what happens with any testing dates is at some point the date is past. Right now my first testing date is 5/21, but after 5/21 I don't want that data to be filled in. What I'm trying to figure out is how to either use a VLOOKUP or IF statement based on today's date, so, for example, if today's date is 6/2/2016, then I want cell D2 (in sheet 1) to pull from D2 (sheet 3) and for E2 (in sheet 1) to pull from E2 (sheet 3).
I feel confused typing this out so I'll try and give more visual information:
Sheet 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]2: Test selection[/TD]
[TD]Test Dates[/TD]
[TD]Test Dates[/TD]
[TD]Test Dates[/TD]
[/TR]
[TR]
[TD]3: Test 1[/TD]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Date 3[/TD]
[/TR]
[TR]
[TD]4: Test 2[/TD]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Date 3[/TD]
[/TR]
[TR]
[TD]5: Test 3[/TD]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Date 3[/TD]
[/TR]
[TR]
[TD]6: Test 4[/TD]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Date 3[/TD]
[/TR]
</tbody>[/TABLE]
--------------------------------------------------------------------------------------------------------------------
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]...[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]A1: Today's Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2: Test Name[/TD]
[TD]5/21/16[/TD]
[TD]6/4/16[/TD]
[TD]6/18/16[/TD]
[TD]7/2/16[/TD]
[TD]7/16/16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A3: Test Name[/TD]
[TD]5/21/16[/TD]
[TD]6/18/16[/TD]
[TD]7/2/16[/TD]
[TD]7/16/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]....[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A15: Test Name[/TD]
[TD]6/18/16[/TD]
[TD]7/2/16[/TD]
[TD]7/16/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I've used two different formulas for pulling the data from Sheet 3 to Sheet 1:
1. =IFERROR(VLOOKUP($C$5,Sheet3!$A:$I,2,FALSE)," ")
2. =IF(Sheet3!$A$1<sheet3!b2,sheet3!b2,sheet3!c2)
The problem I have with both is that I can't figure out how to format it so that if it's past a certain date, then it automatically pulls from the next column over (and thus E2 & F2 would also pull from the next next column over).
Any help would be GREATLY appreciated!
</sheet3!b2,sheet3!b2,sheet3!c2)
I have a spreadsheet that I'm using with three different sheets (Sheet1, Sheet2, Sheet3). In Sheet 1 I have a dropdown list (C4) that contains 20 types of licenses. When a license is clicked, cells c2:c4 will autopopulate with what type of test the person needs for the license (there can be as little as 1 test and as many as 4). Cells d4:F4 contain recommended test-by dates. Those recommended test by dates are housed in Sheet3. A2:16 are filled with the different tests and as of now B:I are filled with test dates. In Sheet 3 cell A1= today's date.
Obviously what happens with any testing dates is at some point the date is past. Right now my first testing date is 5/21, but after 5/21 I don't want that data to be filled in. What I'm trying to figure out is how to either use a VLOOKUP or IF statement based on today's date, so, for example, if today's date is 6/2/2016, then I want cell D2 (in sheet 1) to pull from D2 (sheet 3) and for E2 (in sheet 1) to pull from E2 (sheet 3).
I feel confused typing this out so I'll try and give more visual information:
Sheet 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]2: Test selection[/TD]
[TD]Test Dates[/TD]
[TD]Test Dates[/TD]
[TD]Test Dates[/TD]
[/TR]
[TR]
[TD]3: Test 1[/TD]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Date 3[/TD]
[/TR]
[TR]
[TD]4: Test 2[/TD]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Date 3[/TD]
[/TR]
[TR]
[TD]5: Test 3[/TD]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Date 3[/TD]
[/TR]
[TR]
[TD]6: Test 4[/TD]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Date 3[/TD]
[/TR]
</tbody>[/TABLE]
--------------------------------------------------------------------------------------------------------------------
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]...[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]A1: Today's Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2: Test Name[/TD]
[TD]5/21/16[/TD]
[TD]6/4/16[/TD]
[TD]6/18/16[/TD]
[TD]7/2/16[/TD]
[TD]7/16/16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A3: Test Name[/TD]
[TD]5/21/16[/TD]
[TD]6/18/16[/TD]
[TD]7/2/16[/TD]
[TD]7/16/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]....[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A15: Test Name[/TD]
[TD]6/18/16[/TD]
[TD]7/2/16[/TD]
[TD]7/16/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I've used two different formulas for pulling the data from Sheet 3 to Sheet 1:
1. =IFERROR(VLOOKUP($C$5,Sheet3!$A:$I,2,FALSE)," ")
2. =IF(Sheet3!$A$1<sheet3!b2,sheet3!b2,sheet3!c2)
The problem I have with both is that I can't figure out how to format it so that if it's past a certain date, then it automatically pulls from the next column over (and thus E2 & F2 would also pull from the next next column over).
Any help would be GREATLY appreciated!
</sheet3!b2,sheet3!b2,sheet3!c2)
