Formula help with tracking warranty

K

KC0713

New Member
Joined
Feb 21, 2019
Messages
4
Hi all,

I'm hoping that I can get some help with a formula on a spreadsheet.
I'm creating an excel that tracks return products base on the serial number.

Here is the layout of the spreadsheet.
Sheet 1: Serial Number
A = Customer Name
B = Brand
C = Products Code
D = Serial Number
E = Date Sold
F = Warranty Term (60 days, 90 days, 1 year)
G= Warranty Ended

Sheet 2: Return Product
A = Customer Name
B = Brand
C = Products Code
D = Serial Number
E = Date Return
F=Warranty Ended
G=Reason

For sheet 1 Is it possible to have a formula automatically calculate the term period in cell F? The term would be base on the brand and customer field.

Conditions for formula need.
Warranty term:
Retail- 60 days
Wholesale-90 days
Brand: XYZ- 1 year

For sheet 2

If serial number match then subtracts return date from the sold day and show if warranty "still valid" or the end date of the warranty. Else, show "Not Valid".


Any Suggestions? Thanks in Advance
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Welcome to the forum.

You say that the term is based on brand, or if it's retail/wholesale. I don't see how you determine that. If you have a table to define those things, you could do something like this:

ABCDEFGHIJKLM
Customer NameBrandProducts CodeSerial NumberDate SoldWarranty TermWaranty EndedBrandWarranty LengthWholesale customers
Mary SmithaaaValidxyzJoe's Plumbing
Mark JonesxyzValidwxy
Joe's PlumbingcccNot validvwx
Amy AdamsdddValiduvw

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]111[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]1/2/2019[/TD]
[TD="align: right"]60[/TD]

[TD="align: right"][/TD]

[TD="align: right"]365[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]222[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]365[/TD]

[TD="align: right"][/TD]

[TD="align: right"]365[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]333[/TD]
[TD="align: right"]333[/TD]
[TD="align: right"]10/1/2018[/TD]
[TD="align: right"]90[/TD]

[TD="align: right"][/TD]

[TD="align: right"]730[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]444[/TD]
[TD="align: right"]444[/TD]
[TD="align: right"]12/24/2018[/TD]
[TD="align: right"]60[/TD]

[TD="align: right"][/TD]

[TD="align: right"]180[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD]=IFERROR(VLOOKUP(B2,$I$2:$J$5,2,0),IF(ISNUMBER(MATCH(A2,$L$2:$L$10,0)),90,60))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IF(TODAY()>E2+F2,"Not valid","Valid")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Update the tables in I:J and L as needed. For Sheet2, you can easily do a VLOOKUP or INDEX/MATCH using the serial number to get the warranty ended field. Notice that I used number of days everywhere instead of "60 days" or "1 year" since it makes things much easier.
 
Upvote 0
Thanks for the help.

Actually, the term would be base on the brand, products code, and customer. There are certain products we don't provide warranty and wholesale customer have a longer warranty period than retail customer.

For example, brand BR warranty is 90 days. The same brand, Product code 245 have no warranty. Retail customer 60 days, wholesale customer 90 days.

So from your example, Joe's plumbing is a wholesale customer. The warranty would be 90 days if he buy any product from brand BR except for product code 245.

Is it possible to add those criteria into F2 formula?
 
Upvote 0
I still don't have a clear picture of how you assign warranties, however, this is my latest take on it:

ABCDEFGHIJKLMNO
Customer NameBrandProducts CodeSerial NumberDate SoldWarranty TermWaranty EndedBrandProductWholesale Warranty LengthRetail Warranty LengthWholesale customers
Mary SmithaaaValidxyzJoe's Plumbing
Mark JonesxyzNot validxyz
Joe's PlumbingxyzNot validvwxdefault
Amy AdamsdddValiduvwdefault
Joe's PlumbingxyzValidxyzdefault
Ed SmithqqqNot foundNoneaaadefault
ddddefault

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]111[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]1/20/2019[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"][/TD]

[TD="align: right"]123[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]222[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]60[/TD]

[TD="align: right"][/TD]

[TD="align: right"]245[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]245[/TD]
[TD="align: right"]333[/TD]
[TD="align: right"]10/1/2018[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]

[TD="align: right"]365[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]444[/TD]
[TD="align: right"]444[/TD]
[TD="align: right"]12/24/2018[/TD]
[TD="align: right"]90[/TD]

[TD="align: right"][/TD]

[TD="align: right"]180[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]555[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]90[/TD]

[TD="align: right"][/TD]

[TD="align: right"]90[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]666[/TD]
[TD="align: right"]666[/TD]
[TD="align: right"]10/1/2018[/TD]

[TD="align: right"][/TD]

[TD="align: right"]90[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IFERROR(IF(TODAY()>E2+F2,"Not valid","Valid"),"None")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=IFERROR(INDEX($K$2:$L$10,IFERROR(MATCH(B2&"|"&C2,$I$2:$I$20&"|"&$J$2:$J$20,0),MATCH(B2&"|default",$I$2:$I$20&"|"&$J$2:$J$20,0)),2-COUNTIF($N$2:$N$10,A2)),"Not found")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



In this version, you still have 2 tables. The table in N is a list of your wholesale customers. The table in I:L has the warranties. Brand and product go in I:J, and the wholesale period for wholesale/retail goes in K:L. If the actual product number is found, it takes that (like xyz/245). If not found, it next looks for the brand and the word "default" and takes that.
 
Upvote 0
If I add a row in before F for Exchange. If the row for Exchange is "yes" warranty: Expire. If "no" continue with the next criteria. Should I add an If Function in the F2 formula? Or how would that work?

Thanks
 
Upvote 0
I'd do something like this:

Excel 2012
ABCDEFGHIJKLMNO
Customer NameBrandProducts CodeSerial NumberDate SoldExchangeWarranty TermWaranty EndedBrandProductWholesale Warranty LengthRetail Warranty LengthWholesale customers
Mary SmithaaaNot validxyzJoe's Plumbing
Mark JonesxyzNot validxyz
Joe's PlumbingxyzNot validvwxdefault
Amy AdamsdddValiduvwdefault
Joe's PlumbingxyzNot validxyzdefault
Ed SmithqqqNoneaaadefault
ddddefault

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]111[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]1/20/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]45[/TD]

[TD="align: right"][/TD]

[TD="align: right"]123[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]222[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]60[/TD]

[TD="align: right"][/TD]

[TD="align: right"]245[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]245[/TD]
[TD="align: right"]333[/TD]
[TD="align: right"]10/1/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]

[TD="align: right"]365[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]444[/TD]
[TD="align: right"]444[/TD]
[TD="align: right"]12/24/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]90[/TD]

[TD="align: right"][/TD]

[TD="align: right"]180[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]555[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]yes[/TD]
[TD="align: right"]90[/TD]

[TD="align: right"][/TD]

[TD="align: right"]90[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]666[/TD]
[TD="align: right"]666[/TD]
[TD="align: right"]10/1/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Not found[/TD]

[TD="align: right"][/TD]

[TD="align: right"]90[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=IFERROR(IF(OR(TODAY()>E2+G2,F2="yes"),"Not valid","Valid"),"None")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]{=IFERROR(INDEX($L$2:$M$10,IFERROR(MATCH(B2&"|"&C2,$J$2:$J$20&"|"&$K$2:$K$20,0),MATCH(B2&"|default",$J$2:$J$20&"|"&$K$2:$K$20,0)),2-COUNTIF($O$2:$O$10,A2)),"Not found")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


You'd just have to add the Exchange column, then change the (now) H2 formula.
 
Last edited:
Upvote 0
Thanks for all the help.

But is it possible to change the warranty term to "0" if Exchange row is "yes"?
 
Upvote 0
Sure, just change the G2 formula (based on the layout in post 6) to:

=IF(F2="yes",0,IFERROR(INDEX($L$2:$M$10,IFERROR(MATCH(B2&"|"&C2,$J$2:$J$20&"|"&$K$2:$K$20,0),MATCH(B2&"|default",$J$2:$J$20&"|"&$K$2:$K$20,0)),2-COUNTIF($O$2:$O$10,A2)),"Not found"))

still with Control+Shift+Enter.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Formatting to highlight item warranty <6 months <12 Months and in warranty
Replies
4
Views
2K
awoohaw
awoohaw
V
  • Question Question
Tracking individual codes by date and quantity
Replies
1
Views
37
Vorkosigan
V
C
  • Solved
index match excel formula to vba code
Replies
9
Views
332
DanteAmor
DanteAmor
L
  • Question Question
Share information between sheets to show item age
Replies
6
Views
376
Peter_SSs
Peter_SSs
I
  • Solved
Edit working code to look for value after a date & before a date
Replies
6
Views
115
Alex Blakenburg
A

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

Share this page

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