Auto Fill Cells with Specific Date from a Different Worksheet

[Gsrgofass]

Hello,

I read a similar post and need a bit of fine tuning for my request.

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' (in Yellow) from 'Warranty Registration' worksheet into the cells in the '2019 Claims' worksheet based off the serial number (in Red). So....anytime SN 97301 appears in Column E, July 21, 2017 would auto populate in Column F. In this example, July 21, 2017 should auto populate to cells F4 & F15.

Thanks very much!!!

Steve

[maras_mak]

In 'F4' formula and then drag down.

[Gsrgofass]

Hi Maras,

The formula you wrote results in all cells displaying 'TRUE' rather than the desired 'Original Warranty Registration' date.
'Date' and 'Serial' number info in the 'Warranty Registration' worksheet to be referenced to auto populate column 'F' in the '2019 Claims' worksheet.

The dates I would like displayed in column 'F' of the '2019 Claims' worksheet should cross reference based off the corresponding serial number.
Sorry if I'm not explaining this clearly

Desired result in the '2019 Claims' worksheet:

E4 = 97301, F4 = July 21, 2017
E5 = 43821, F5 = June 15, 2019
E6 = 49421, F6 = April 14, 2017
E7 = 49724, F7 = October 8, 2019
E8 = 49782, F8 = September 21, 2018
Etc.

[cbizus]

Please Login or Register  to view this content.

put that in F and drag down.

[maras_mak]

You had double formulas in every cell in col. F and hence TRUE - I don't know how you did it !!!
See to the attachment.

[Gsrgofass]

Oops. Yeah, I'm that good
All is right in my world again. Much thanks, Maras!!!!

[Gsrgofass]

As we should never share sensitive information online, I had made a dummy file which we have been viewing on this site in order to solve my issue/query. This file is where you placed the formula which proves it's functionality and all was good....or so I thought.

I copied and pasted the formula you wrote into my actual spreadsheet and the date appears for an instant then disappears downwards in the cell. I ensured the format within the cell was set as 'date' and the colour of the text within the cell was black.
Then, I copied columns 'A' thru 'E' from my original file 'Warranty Registration' tab and and pasted those columns into the file we have been sharing on this site. Boom....it populates the date perfectly in my original file

Why is that? Why was merely copying and pasting the formula from this site into my original spreadsheet not working?
Weird, no?
Anyways, I cleared the column and typed your formula into my original file and all is good but it's not filling the date in all cells. Even though the serial numbers are present in the referenced worksheet. Why are some cells not working?

[Gsrgofass]

The left portion of the screenshot shows the serial numbers are in the worksheet and the right portion of the screenshot shows the serial numbers are in the other worksheet but the date column is not populating.

[Gsrgofass]

Here's the file with the formula applied. As you can see, serial number 814841 is present in both worksheets but the date is not populating.
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.

Thanks again!!!

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new here I have done it for you this time.)

https://www.mrexcel.com/forum/excel-...worksheet.html