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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
1K
awoohaw
awoohaw
C
  • Solved
index match excel formula to vba code
Replies
9
Views
289
DanteAmor
DanteAmor
L
  • Question Question
Share information between sheets to show item age
Replies
6
Views
373
Peter_SSs
Peter_SSs
JenniferMurphy
  • Solved
Why is this one CountIfs expression getting a Value error?
Replies
4
Views
750
JenniferMurphy
JenniferMurphy
T
  • Question Question
VBA to populate cells on one worksheet with cells from another worksheet based on date help!!!
Replies
2
Views
350
Taekwon
T

Forum statistics

Threads
1,222,900
Messages
6,168,926
Members
452,227
Latest member
sam1121

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