If Date in Column1/Sheet1 is...then enter Customer Name Column1/Sheet2

[ktg0011]

Hello,

I have 12 columns of data on Sheet2 (Lookahead!A3:L30) that need fed from 4 columns of table "Operations" data on Sheet1 ([Start Date] A2:A200, [Customer] B2:B200, [Amount] G2:G200, [Probability] H2:H200).

Lookahead!A3:C30 have the following formulas:
A =IF(AND(Operations!A:A>TODAY(),Operations!A:A<=(TODAY()+30)),Operations!B:B,"")
B =IF(AND(Operations!A:A>=TODAY(),Operations!A:A<(TODAY()+30)),Operations!G:G,"")
C =IF(AND(Operations!$A:A>=TODAY(),Operations!$A:A<(TODAY()+30)),Operations!H:H,"")

Lookahead!D3:F30 have the following formulas:
D =IF(AND(Operations[Start Date]>(TODAY()+60),Operations[Start Date]<=(TODAY()+90)),Operations[Customer],"")
E =IF(AND(Operations!A:A>(TODAY()+60),Operations!A:A<=(TODAY()+90)),Operations!G:G, "")
F =IF(AND(Operations!A:A>(TODAY()+60),Operations!A:A<=(TODAY()+90)),Operations!H:H, "")

Lookahead!G3:I30 have "...": >90<=120 days
Lookahead!J3:L30 have "...": >120 Days.

The formula is returning the correct values; however, there is a step effect across the rows. A:C populate in Rows 3 and 4, D:F populate in Rows 5 and 6, and so on.

How can I modify the formula to search the Operations[Start Date] and provide the correct information without skipping cells?

Thank you!

 

[Mike86]

Any chance you could provide the Excel file that goes with your description? That really helps in sorting things out.

 

[ktg0011]

Any chance you could provide the Excel file that goes with your description? That really helps in sorting things out.

Duh. Would've been a lot easier. -__-

 

[KenU]

I'm afraid your file is a bit too sanitized to be able to offer much help. Based on the formulas listed in your first post, it looks like the behavior you have described is due to relative referencing across the worksheets.

In order to avoid skipping rows, you will need some criteria (such as the customer name) to control what the formulas return from the source data. Otherwise Excel (left to its own devices) will "help" by using the relative reference based on row number.

If you could provide some fake data (even 10-20 rows) to illustrate the problem and an example of the desired outcome (based on the fake data), that would allow for a more complete answer.

Regards,
Ken

 

[ktg0011]

I'm afraid your file is a bit too sanitized to be able to offer much help. Based on the formulas listed in your first post, it looks like the behavior you have described is due to relative referencing across the worksheets.

In order to avoid skipping rows, you will need some criteria (such as the customer name) to control what the formulas return from the source data. Otherwise Excel (left to its own devices) will "help" by using the relative reference based on row number.

If you could provide some fake data (even 10-20 rows) to illustrate the problem and an example of the desired outcome (based on the fake data), that would allow for a more complete answer.

Regards,
Ken

Hi Ken,
See attached. Thank you

 

[Mike86]

The issue appears to be associated with the table "Operations" and how Excel is doing the calculations based on cell row. I can get past this by adding helper columns for each of your date ranges and throwing in a couple of other functions, but I'm not sure if you're interested in that type of change. See the attached file for how this looks for the 30-60day and 60-90day ranges. Extending this idea to the other ranges is pretty straightforward and in-line with what you've done previously.

 

[ktg0011]

Thank you!!

I'm not opposed to that, but would hiding those columns cause an issue with the formulas?

 

[Mike86]

Nope, hiding won't change anything. You could also move them to the end of the table so they weren't obvious.