eBay Spreadsheet... Big Challenge for Someone!

S

SkzDaLimit

Board Regular
Joined
Dec 1, 2002
Messages
54
With some help from the folks here in the forums and some brainstorming of my own, I have come a pretty long way in completing my sales spreadsheet. One final hurdle remains. Integrating 7 final value fee formulas into my sheet.

Here is a portion of the spreadsheet:
Excel Workbook
HOQRS
21Selling PriceeBay FVFPayPal FeeInsertion FeeListing Type
22
23$ 12.99$ 0.59$ 0.20Consumer Electronics
24$ 12.99$ 0.59$ -Car Parts
...

Column S contains a drop down box with the 7 categories my spreadsheet is tracking:

All Other Items
Books & Media
Car Electronics
Car Parts
Clothing
Consumer Electronics
Regular eBay Auction

Each category has its own formula to determine the fee according to what dollar amount is entered into the cells in column H and the category is picked in the drop down in column S. The fee will appear in column O.

The formulas respective to their category are:<!--[if gte mso 9]><xml><w:WordDocument><w:View>Normal</w:View><w:Zoom>0</w:Zoom><w:PunctuationKerning/><w:ValidateAgainstSchemas/><w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid><w:IgnoreMixedContent>false</w:IgnoreMixedContent><w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText><w:Compatibility><w:BreakWrappedTables/><w:SnapToGridInCell/><w:WrapTextWithPunct/><w:UseAsianBreakRules/><w:DontGrowAutofit/></w:Compatibility><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel></w:WordDocument></xml><![endif]--><!--[if gte mso 9]><xml><w:LatentStyles DefLockedState="false" LatentStyleCount="156"></w:LatentStyles></xml><![endif]--><!--[if gte mso 10]><style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;}</style><![endif]--> All Other Items

=IF($H23<=50,$H23*11%,IF($H23<=1000,($H23-50)*6%+6,($H23-1000)*2%+63))

Books and Media

=IF($H23<=50,$H23*13%,IF($H23<=1000,($H23-50)*5%+6,($H23-1000)*2%+63))

Car Electronics

=IF($H23<=50,$A23*7%,IF($H23<=1000,($A23-50)*5%+6,($A23-1000)*2%+63))

Car Parts

=IF($A23<=50,$A23*10%,IF($H23<=1000,($A23-50)*8%+6,($A23-1000)*2%+63))

Clothing

=IF($H23<=50,$H23*10%,IF($H23<=1000,($H23-50)*8%+6,($H23-1000)*2%+63))

Consumer Electronics:

=IF($H23<=50,$H23*7%,IF($H23<=1000,($H23-50)*5%+6,($H23-1000)*2%+63))

Regular eBay Auction

=SUM(H23*9%)

I was thinking a VLOOKUP like I am using to determine another fee in the spreadsheet but this one seems a tab more complicated do the complexity of using multiple formulas so I can't grasp it in my head.

Any ideas? Thanks in advance once again! (This was done in Excel 2003)

Michael
​
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
SkzDaLimit,


You could use the Worksheet_Change Event for range S23 to S1000 (you can change the upper limit S1000 to whatever you would like).


Sample raw data before making selection to the data validation cells in column S, beginning in cell S23:


Excel Workbook
HOQRS
21Selling PriceeBay FVFPayPal FeeInsertion FeeListing Type
22
23$12.99$0.00$0.59$0.20
24$12.99$0.00$0.59$ -
25$12.99$0.00$0.59$0.20
26$12.99$0.00$0.59$ -
27$12.99$0.00$0.59$0.20
28$12.99$0.00$0.59$ -
29$12.99$0.00$0.59$0.20
30$12.99$0.00$0.59$ -
31
Sheet1





Then, after making selections in column S:


Excel Workbook
HOQRS
21Selling PriceeBay FVFPayPal FeeInsertion FeeListing Type
22
23$12.99$1.43$0.59$0.20All Other Items
24$12.99$1.69$0.59$ -Books & Media
25$12.99$0.00$0.59$0.20Car Electronics
26$12.99$0.00$0.59$ -Car Parts
27$12.99$1.30$0.59$0.20Clothing
28$12.99$0.91$0.59$ -Consumer Electronics
29$12.99$1.17$0.59$0.20Regular eBay Auction
30$12.99$0.00$0.59$ -Car Parts
31
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel


Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 08/28/2011
' http://www.mrexcel.com/forum/showthread.php?t=575032
If Intersect(Target, Range("S23:S1000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Range("O" & Target.Row) = 0
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Select Case Target.Value
    Case "All Other Items"
      '=IF($H23<=50,$H23*11%,IF($H23<=1000,($H23-50)*6%+6,($H23-1000)*2%+63))
      '=IF(RC8<=50,RC8*11%,IF(RC8<=1000,(RC8-50)*6%+6,(RC8-1000)*2%+63))
      Range("O" & Target.Row).FormulaR1C1 = "=IF(RC8<=50,RC8*11%,IF(RC8<=1000,(RC8-50)*6%+6,(RC8-1000)*2%+63))"
    Case "Books & Media"
      '=IF($H23<=50,$H23*13%,IF($H23<=1000,($H23-50)*5%+6,($H23-1000)*2%+63))
      '=IF(RC8<=50,RC8*13%,IF(RC8<=1000,(RC8-50)*5%+6,(RC8-1000)*2%+63))
      Range("O" & Target.Row).FormulaR1C1 = "=IF(RC8<=50,RC8*13%,IF(RC8<=1000,(RC8-50)*5%+6,(RC8-1000)*2%+63))"
    Case "Car Electronics"
      '=IF($H23<=50,$A23*7%,IF($H23<=1000,($A23-50)*5%+6,($A23-1000)*2%+63))
      '=IF(RC8<=50,RC1*7%,IF(RC8<=1000,(RC1-50)*5%+6,(RC1-1000)*2%+63))
      Range("O" & Target.Row).FormulaR1C1 = "=IF(RC8<=50,RC1*7%,IF(RC8<=1000,(RC1-50)*5%+6,(RC1-1000)*2%+63))"
    Case "Car Parts"
      '=IF($A23<=50,$A23*10%,IF($H23<=1000,($A23-50)*8%+6,($A23-1000)*2%+63))
      '=IF(RC1<=50,RC1*10%,IF(RC8<=1000,(RC1-50)*8%+6,(RC1-1000)*2%+63))
      Range("O" & Target.Row).FormulaR1C1 = "=IF(RC1<=50,RC1*10%,IF(RC8<=1000,(RC1-50)*8%+6,(RC1-1000)*2%+63))"
    Case "Clothing"
      '=IF($H23<=50,$H23*10%,IF($H23<=1000,($H23-50)*8%+6,($H23-1000)*2%+63))
      '=IF(RC8<=50,RC8*10%,IF(RC8<=1000,(RC8-50)*8%+6,(RC8-1000)*2%+63))
      Range("O" & Target.Row).FormulaR1C1 = "=IF(RC8<=50,RC8*10%,IF(RC8<=1000,(RC8-50)*8%+6,(RC8-1000)*2%+63))"
    Case "Consumer Electronics"
      '=IF($H23<=50,$H23*7%,IF($H23<=1000,($H23-50)*5%+6,($H23-1000)*2%+63))
      '=IF(RC8<=50,RC8*7%,IF(RC8<=1000,(RC8-50)*5%+6,(RC8-1000)*2%+63))
      Range("O" & Target.Row).FormulaR1C1 = "=IF(RC8<=50,RC8*7%,IF(RC8<=1000,(RC8-50)*5%+6,(RC8-1000)*2%+63))"
    Case "Regular eBay Auction"
      '=SUM(H23*9%)
      '=SUM(RC[-7]*9%)
      Range("O" & Target.Row).FormulaR1C1 = "=SUM(RC[-7]*9%)"
  End Select
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub


Then make slections in column S.
 
Upvote 0
SkzDaLimit,

In the macro code you will see several lines of code for each validation choice, like:


Code: 
    Case "All Other Items"
      '=IF($H23<=50,$H23*11%,IF($H23<=1000,($H23-50)*6%+6,($H23-1000)*2%+63))
      '=IF(RC8<=50,RC8*11%,IF(RC8<=1000,(RC8-50)*6%+6,(RC8-1000)*2%+63))
      Range("O" & Target.Row).FormulaR1C1 = "=IF(RC8<=50,RC8*11%,IF(RC8<=1000,(RC8-50)*6%+6,(RC8-1000)*2%+63))"


I changed all your formulae to R1C1 notation so that they can be used in any row.



Before you use the Worksheet_Change Event code with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm
 
Upvote 0
WOW! Works great, except....

Car Electronics and Car Parts are returning values of like $3000.00 and more when selected in the drop-down. The rest of the categories are showing the correct fee values.

I didn't see anything unusual in the code to account for that though......
 
Upvote 0
SkzDaLimit said:
WOW! Works great, except....

Car Electronics and Car Parts are returning values of like $3000.00 and more when selected in the drop-down. The rest of the categories are showing the correct fee values.

I didn't see anything unusual in the code to account for that though......
Click to expand...

Found the errors in the VBA code and fixed it. Everything works perfectly now! Thanks again so very much!
 
Upvote 0
SkzDaLimit,

For the rows that are returning the $3,000.00, what value is in column A for those rows?
 
Last edited:
Upvote 0
SkzDaLimit,


The formulae for the following are referring to column A:


Car Electronics

=IF($H23<=50,$A23*7%,IF($H23<=1000,($A23-50)*5%+6,($A23-1000)*2%+63))


Car Parts

=IF($A23<=50,$A23*10%,IF($H23<=1000,($A23-50)*8%+6,($A23-1000)*2%+63))
 
Last edited:
Upvote 0
hiker95 said:
SkzDaLimit,


The formulae for the following are referring to column A:


Car Electronics

=IF($H23<=50,$A23*7%,IF($H23<=1000,($A23-50)*5%+6,($A23-1000)*2%+63))


Car Parts

=IF($A23<=50,$A23*10%,IF($H23<=1000,($A23-50)*8%+6,($A23-1000)*2%+63))
Click to expand...

That was what I found and fixed, all is well!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Type mismatch using date in InputBox
Replies
1
Views
1K
SeniorTom
S
D
  • Question Question
Variable in Ranges
Replies
5
Views
1K
Fluff
Fluff
S
  • Question Question
Macro for DATEVALUE
Replies
3
Views
1K
jasonb75
J
P
  • Question Question
Automatically inserting VBA into a workbook
Replies
1
Views
1K
Phillip2
P
D
  • Question Question
Conditional formating with a formula
2 3
Replies
21
Views
2K
ClaireS
C

Forum statistics

Threads
1,224,537
Messages
6,179,405
Members
452,911
Latest member
a_barila

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