Auto Fill Cells with Specific Date from a Different Worksheet

Gsrgofass

New Member
Joined
Nov 8, 2019
Messages
6
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,008
Office Version
365
Platform
Windows
Hi & welcome to MrExcel
How about
=IF(E4="","",IFERROR(INDEX('Warranty Registration'!B:B,MATCH(E4,'Warranty Registration'!$E$1:$E$300,0)),""))
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,008
Office Version
365
Platform
Windows
Are the serial numbers actual numbers or alphanumeric strings?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,008
Office Version
365
Platform
Windows
Cross posted https://www.excelforum.com/excel-formulas-and-functions/1295496-auto-fill-cells-with-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

New Member
Joined
Nov 8, 2019
Messages
6
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,008
Office Version
365
Platform
Windows
How about
=IF(E4="","",IFERROR(INDEX(Table1174[Del. Date per Original Warranty Card],MATCH(E4,Table1174[Serial '#],0)),""))

Also please note post#5
 

Gsrgofass

New Member
Joined
Nov 8, 2019
Messages
6
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,008
Office Version
365
Platform
Windows
Did you type the formula in, or copy/paste?
It works with the file you supplied on EF.
 

Gsrgofass

New Member
Joined
Nov 8, 2019
Messages
6
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.
 

Forum statistics

Threads
1,081,578
Messages
5,359,740
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top