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!!!
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!!!
