I Want the minimum date based on cell value

Jaffabfc

Board Regular
Joined
Jul 5, 2013
Messages
167
Hi,

I am wanting to get the minimum date from sheet "Orders" column "F" based on cell a2 cross referenced with row "Orders" column "A"

So column A in orders is numbers 1 - 10 multiple times
Column F in Orders is multiple different dates (some the same date)
so in another sheet cell I2 i am wanting the earliest date from the value of cell A2 with the above data to bring back earliest date

i currently have a formula that shows if its within 5 days or less of todays date put today +14, but i need the exact information now.

=IF(H9=0,"",IF(IF(COUNTIF(Orders!A:A,Link!A9)>0,MIN(IF(Orders!$A:$A=Link!A9,Orders!$P:$P)),"")<TODAY()+5,TODAY()+14,IF(COUNTIF(Orders!A:A,Link!A9)>0,MIN(IF(Orders!$A:$A=Link!A9,Orders!$P:$P)),"")))
this is the formula ive been using.

Link is the sheet i am doing the formula on

thanks
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
either one should work

Book1
ABCDEFGHI
1520/09/2020
2230/08/202030/08/2020
3628/08/202030/08/2020
4921/09/2020
5604/09/2020
6813/09/2020
7325/08/2020
8707/09/2020
9925/08/2020
10213/09/2020
11219/09/2020
12830/08/2020
13618/09/2020
14208/09/2020
15805/09/2020
16319/09/2020
17513/09/2020
18812/09/2020
19420/09/2020
20115/09/2020
Sheet2
Cell Formulas
RangeFormula
I2I2=MINIFS(F1:F20,A1:A20,A2)
I3I3=MIN(IF(A1:A20=A2,F1:F20))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Jaffabfc

Board Regular
Joined
Jul 5, 2013
Messages
167
Hi,

Thanks for the update above, it seems to work above but it isnt working on mine.
I am getting the error 00/01/1900.
Also what if the array of data changes so a2:10 changes to a2:a500, how do i make it pick this up?
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi,

Thanks for the update above, it seems to work above but it isnt working on mine.
I am getting the error 00/01/1900.

that probably due to the cell formatted as date instead of no

Also what if the array of data changes so a2:10 changes to a2:a500, how do i make it pick this up?

just extend the range
 

Jaffabfc

Board Regular
Joined
Jul 5, 2013
Messages
167

is there no way of the range extending automatically?
i can be updating this approx every hour so changing this hourly is quite a pain.

also the format of the cell i have the formula in is date
the column a and cells a2 etc are all as general format.

:(
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
is there no way of the range extending automatically?
i can be updating this approx every hour so changing this hourly is quite a pain.

also the format of the cell i have the formula in is date
the column a and cells a2 etc are all as general format.

:(
you can set it up to the largest range you're expecting, setting it too large than necessary will slow it down, that's all
 

Forum statistics

Threads
1,140,924
Messages
5,703,182
Members
421,280
Latest member
Jaycee01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top