Auto Fill Cells with Specific Date from a Different Worksheet

[Gsrgofass]

Hello,

I recently took over a position at my work and I am working with a file that was not originally mine.

I have warranty registration cards that we receive from our dealers once the item is sold to the end user. When the end user reports a failure they are supposed to submit a credit form with pertinent info including that original registration date. Sometimes that original date is massaged to keep them within the warranty period. What I would like is the formula that auto populates the 'Original Warranty Registration Date' from column 'B' of the 'Warranty Registration' worksheet into the cells in column 'F' of the '2019 Claims' worksheet based off the serial number.

Example....anytime SN 711531 appears in Column E of the '2019 Claims' worksheet, January 12, 2018 would auto populate in Column F. The formula I have doesn't seem to work 100%. I know there are serial numbers in both worksheets and yet the date is not auto populating 100%. Can you determine and explain why? Can you fix the database/formula?

As well, I know there are some instances where the serial number is not present in the 'Warranty Registration' worksheet since, theoretically, it was never received by our company from the dealer. In these cases...can the cell in Column 'E' of the '2019 Claims' worksheet be automatically colour filled (Yellow) based on those missing serial numbers? That would enable me to contact the dealers and request their original delivery dates.

Once I enter the newly obtained serial numbers & dates in the 'Warranty Registration' worksheet the coloured cells in the '2019 Claims' worksheet then switch to display the corresponding dates like the rest of the worksheet.

I cannot attach my file, although it has been stripped of all important content. Is there anyway to upload my file for you to work from?

The formula I'm using is:=IF(E4="","",IFERROR(INDEX('Warranty Registration'!B:B,AGGREGATE(15,6,ROW($1:$300)/('Warranty Registration'!$E$1:$E$300=E4),1)),""))

Thanks very much!!!

 

[Fluff]

Hi & welcome to MrExcel
How about
=IF(E4="","",IFERROR(INDEX('Warranty Registration'!B:B,MATCH(E4,'Warranty Registration'!$E$1:$E$300,0)),""))

 

[Gsrgofass]

Odd, your formula works better but there are still a few missing the dates.

 

[Fluff]

Are the serial numbers actual numbers or alphanumeric strings?

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...specific-date-from-a-different-worksheet.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Gsrgofass]

The 'Serial Number' columns are formatted as 'General' in both worksheets. Sometimes there are either numbers entered that are not ours or appended entries where there may be a letter added onto the end of the serial number. Example: 605191Z

 

[Fluff]

How about
=IF(E4="","",IFERROR(INDEX(Table1174[Del. Date per Original Warranty Card],MATCH(E4,Table1174[Serial '#],0)),""))

Also please note post#5

 

[Gsrgofass]

Yes, understood re: cross posting. I was merely looking for a second opinion. Duly noted to mention if it occurs again

The new formula returns the error msg:

[FONT=&quot]There's a problem with this formula.[/FONT]
[FONT=&quot]Not trying to type a formula?[/FONT]
[FONT=&quot]When the first character is an equal(=) or minus (-) sign, Excel thinks it's a formula.[/FONT]
[FONT=&quot]you type: =1=1, cell shows : 2[/FONT]
[FONT=&quot]To get around this, type an apostrophe (') first:[/FONT]
[FONT=&quot]you type: '=1=1, cell shows: =1+1[/FONT]

 

[Fluff]

Did you type the formula in, or copy/paste?
It works with the file you supplied on EF.

 

[Gsrgofass]

Correct. It does work. I don't know why copying and pasting causes an error. Thanks for getting that sorted out.

Would you be able to also help out with the second request in my first post re: the colouring of the cells?

As well, I know there are some instances where the serial number is not present in the 'Warranty Registration' worksheet since, theoretically, it was never received by our company from the dealer. In these cases...can the cell in Column 'E' of the '2019 Claims' worksheet be automatically colour filled (Yellow) based on those missing serial numbers? That would enable me to contact the dealers and request their original delivery dates.

Once I enter the newly obtained serial numbers & dates in the 'Warranty Registration' worksheet the coloured cells in the '2019 Claims' worksheet then switch to display the corresponding dates like the rest of the worksheet.