Formula for adding a date to a specific number

[Riggs18]

I need help figuring out how to get a date to show up in a column when a specific number is entered into a spreadsheet.
Attached is a example of the spreadsheet I created for work. In column E labeled Color 1, I have PN's that I created. To the left of the PN's I have dates when these colors will need to run. I am trying to come up with a formula for a cell that will look up a specific number in column E and bring back the earliest date from column D. Since these PN's may run several times I am looking for a way to figure out when I have to order material. I thought by trying to grab the earliest date that comes up that would help.
Also, I am not sure if it is possible but I would like to be able to look across the other 5 "Press" spreadsheets to find the earliest date for a specific number. Same question as above just on a larger scale.
Sorry if this sounds confusing, I am a rookie here.

Thanks,

[RS15]

Are the dates always in order from earliest to latest? and which tab did you want this on, press 1?

[RS15]

Here you go, place this in any cell:

Please Login or Register  to view this content.

You then enter the color number that you want into cell D1.
It is an array formula so you have to press CTRL+SHIFT+ENTER when you enter it into the cell or it wont work.

If you don't have a date next to a color code it won't work properly.







If this helped, feel free to express thanks with the star in the bottom left.

[Riggs18]

Thanks RS15 that worked.
One more thing I was looking to do: I have all the PN's listed on the "production" tab. Is there a way to get the date to sholw up on the correct line for each PN?
As you can see on each tab (Press 1, Press2) I have 6 different areas where colors could be (Column E, K, Q,etc) would I also be able to add those columns into a formula to so they bring in those dates as well? All dates are in accending order on each sheet.

Thanks,

Here you go, place this in any cell:

Please Login or Register  to view this content.

You then enter the color number that you want into cell D1.
It is an array formula so you have to press CTRL+SHIFT+ENTER when you enter it into the cell or it wont work.

If you don't have a date next to a color code it won't work properly.







If this helped, feel free to express thanks with the star in the bottom left.

[RS15]

I am not following what you are looking for. Can you explain it a little better? You want the earliest date corresponding to each PN to show up on the production tab next to each PN?

[Riggs18]

Basiclly,yes that is what I would like the formula to do.
I would like to have the earliest date for that specific PN show up on the production tab in column J of the number specified. Since this PN may run on more then 1 press I would like to have the formula search both press work books.
I am sorry this is a little confusing. I am trying to describe this the best I can.

[RS15]

Sorry still confused. Where is the PN on the production tab? It seems to me like clumn A (MC#) is what you want me to show the earliest date for. However, there are only about 5 numbers on the Press 1 and Press 2 tab that have any of these numbers.

[Riggs18]

Sorry about that. Yes, it is the MC#..I am use to having it be the PN but we switched not too long ago. If you are on the production tab the first row would be MC#1000079, I would like to have a formula that would look up this number is the press 1 & press 2 tabs (not the columns in production) and return the earliest date to column J in the production sheet that this color would be hitting press.
I have attached the sheet again highlighting an example.
I am sorry I am trying to explain it the best I can.