Quick Formula Help - Display Cell 'A' Value If cell 'B' Within Date Range

[TextMonkey]

Hi all,

I'm struggling to achieve something.

I have a list of items with specific dates (food products) on Sheet A. On sheet "B", I want to display only those goods whose expiry dates fall within a certain range.

I'm sort of playing with:

Please Login or Register  to view this content.

What I want to do is IF Sheet1 Cell A1 is within 30 days of today either way, then display the cell value 2 cells to the right of the A1.

I then want to fill the cells below so I end up with a list of all products within that range without blanks.

I realise my formula above is way out, but I hope it givers you the general gist!

Thanks in advance. :-)

[rylo]

Hi

This will have to be adjusted according to your real situation, but assuming your raw data is on sheet1 in the range A1:C3 then try

=IFERROR(INDEX(Sheet1!C:C,SMALL(IF((Sheet1!$A$1:$A$3<TODAY()-30),ROW(Sheet1!$A$1:$A$3),IF((Sheet1!$A$1:$A$3>TODAY()+30),ROW(Sheet1!$A$1:$A$3),"")),ROW())),"")

This has to be array entered (CTRL, SHIFT, ENTER).

HTH

rylo

[TextMonkey]

Hi Rylo,

Thanks for replying!

Okay, so I amended your formula and array entered, but it doesn't seem to work:

Please Login or Register  to view this content.

What I'm trying to do is:

1. Find all dates in column "D" in sheet "Ambient" that are between 9 days and 20 days time.
2. Once the first product is found that qualifies, return the value in column "F".
3. I will then fill the formula down so I end up with a list of all products within the specified range with no spaces.

When I enter the above formula, it simply returns the values in column F irrespective of date in column "D"

I'm sure It's me that has gone wrong, but I'm struggling to see where!

Thanks in advance of your help again,

[rylo]

Hi

Can you attach an example file that reflects your real situation (Sheet names, data types and positions etc) for review.

rylo

[TextMonkey]

Hi again Rylo,

Dummy sheet below:

http://www.filedropper.com/sample_5

Essentially I need the Tab "Short Dated" to only display those producs that fall within 9 and 20 days from today with no spaces.

Obviously the live file is much larger than that, but hopefully that will enable you to see what I am looking to do!.

Thanks again.

[rylo]

Hi

can you attach the file directly to the post, not via some external process.

rylo

[TextMonkey]

I couldn't see that option first time around...

Sorry!

[rylo]

Hi

Based on your example file

Short Dated!A3: =IFERROR(INDEX(Ambient!A:A,SMALL(IF((Ambient!$D$3:$D$13>=TODAY()+9)*(Ambient!$D$3:$D$13<=TODAY()+20),ROW(Ambient!$D$3:$D$13),""),1)),"") Array entered. Note you will have to change the indexed column to bring back the right results (D, F, G and H).

Note also, that you don't have any example data that will match the relevant criteria. I changed one of the data entries to be 12/11/2012 and it was returned.

HTH

rylo

[TextMonkey]

Are you able to attach the file?

[TextMonkey]

Scratch that, I think I'm okay..

I have it working on one cell - which when filled accross changes the indexed cells okay and fills the correct data, but when I fill vertically, I just get repeats of the same results rather than the next product that fulfils the criteria...

How would I change the formula so the next row picks up the next qualifying product?

[rylo]

Hi

Sorry, I forgot to expand the formula again when I was testing before I realised you didn't have any data that would be returned.


=IFERROR(INDEX(Ambient!A:A,SMALL(IF((Ambient!$D$3:$D$13>=TODAY()+9)*(Ambient!$D$3:$D$13<=TODAY()+20),ROW(Ambient!$D$3:$D$13),""),row()-2)),"")

rylo

[TextMonkey]

Hi

Sorry, I forgot to expand the formula again when I was testing before I realised you didn't have any data that would be returned.


=IFERROR(INDEX(Ambient!A:A,SMALL(IF((Ambient!$D$3:$D$13>=TODAY()+9)*(Ambient!$D$3:$D$13<=TODAY()+20),ROW(Ambient!$D$3:$D$13),""),row()-2)),"")

rylo

Thanks Rylo, but for some reason I'm getting erratic results in my sheet.

I've attached a copy if you wish to see if there's an obvious problem, but not all qualifying products are showing. I've used Conditional Formatting to highlight all the rows that should display on Sheet 1 and for some reason, only certain rows are displaying - and if you change the date above the "Green" section, it returns the wrong row value.

I appreciate all you have done thus far - and hope you won't mind having a quick look to see if I've missed something obvious.

Thanks in advance!

[rylo]

Hi

The example file doesn't reflect the structure of your real file, and this is what has caused the problem. Given the structure of the file in #12, the formula in Ambient!A9 should be
=IFERROR(INDEX(Ambient!A:A,SMALL(IF((Ambient!$D$3:$D$24>=TODAY()+9)*(Ambient!$D$3:$D$24<=TODAY()+20),ROW(Ambient!$D$3:$D$24),""),ROW()-8)),"")

Note that the row()-2 has been changed to row()-8.

HTH

rylo

[TextMonkey]

Hi again Rylo,

A perfect solution in the end - it took me a little while to figure out the Index function and understand your last response, but I now have a solution that works and an understanding of what it does and why.

I appreciate your patience and effort throughout.