Auto Fill Cells with Specific Date from a Different Worksheet

Gsrgofass

New Member
Joined
Nov 8, 2019
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
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!!!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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:
Upvote 0
Odd, your formula works better but there are still a few missing the dates.
 
Upvote 0
Are the serial numbers actual numbers or alphanumeric strings?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
How about
=IF(E4="","",IFERROR(INDEX(Table1174[Del. Date per Original Warranty Card],MATCH(E4,Table1174[Serial '#],0)),""))

Also please note post#5
 
Upvote 0
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]
 
Upvote 0
Did you type the formula in, or copy/paste?
It works with the file you supplied on EF.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top