Messages

1

General Discussion / Re: Formula help to separate SKU into separate fields

« on: April 04, 2019, 08:24:13 pm »

Winding down for the night and don't want to mess with it until I'm not so darn tired, but had to try the first one. Success... woo hoo!! Many thanks!

2

General Discussion / Formula help to separate SKU into separate fields

« on: April 04, 2019, 10:20:44 am »

Hi Scott,

I need some help with a few formulas. I am trying to extract data from the SKU column into 3 other columns:

Item Purchase Price
Inventory Source
Purchase Date

Here are 2 examples of my SKU's. As you can see the number of characters in each section varies depending on the item, so I can't use the position in the formula. (I need the item type (ie: CD, TXBK) in the beginning of the SKU for my repricing templates).

CD-2.00-BKCAFE-01.05.2019-01
TXTBK-13.00-STVIN-01.05.2019-101

On page 1 of this original topic I was using this formula to pull the Item Purchase Price from the SKU (see below),

2.00-BKCAFE-01.05.2017
=VALUE(LEFT(G3,FIND("-",G3)-1))

but now I need to add the item type to the beginning of the sku and also pull out Inventory Source and Purchase Date. Spent the better part of last night and this morning googling and testing with no luck.

Can you help? Thanks!
Sue

3

Technical Support / Re: VBA Password Error When Closing the Spreadsheet

« on: July 16, 2017, 08:41:04 am »

I saw this solution for one user. Maybe it will help. Post #3

https://www.mrexcel.com/forum/excel-questions/889849-prompt-visual-basic-applications-project-password-after-excel-closes-dropbox-badge.html

or this:

https://stackoverflow.com/questions/23571760/vba-password-prompt-when-closing-excel

I did test this and it has worked to get rid of the password box. In my dropbox settings preferences I switched the Badge setting from Always Show to Never Show. I haven't had the problem since.

4

General Discussion / Re: Cost of Goods

« on: July 16, 2017, 08:34:39 am »

My guess is the formula is pulling the price out as a "string" value (alpha-numeric).   Use "value()" in the formula to make sure it stores it as a number.

example:
=VALUE(LEFT(G3,FIND("-",G3)-1))

I think this will solve the issue and the ROI and profit should calculate automatically...

Worked perfect! Thanks Scott...

5

General Discussion / Re: Cost of Goods

« on: July 15, 2017, 02:45:21 pm »

Hey Scott,

I just noticed that when I used the above method to pull in cost, the ROI field doesn't get filled in. I'm assuming because I added a formula in the cost field now not an actual static number. Thought I'd bring it up while you were working on adding the other two columns. No biggie if it's a problem but thought I'd mention it in case it was a simple OR statement that could be added or something.

6

Technical Support / Re: VBA Password Error When Closing the Spreadsheet

« on: July 15, 2017, 12:03:04 pm »

I saw this solution for one user. Maybe it will help. Post #3

https://www.mrexcel.com/forum/excel-questions/889849-prompt-visual-basic-applications-project-password-after-excel-closes-dropbox-badge.html

or this:

https://stackoverflow.com/questions/23571760/vba-password-prompt-when-closing-excel

7

Technical Support / Re: VBA Password Error When Closing the Spreadsheet

« on: July 15, 2017, 10:07:34 am »

Edit below: Spoke too soon. Still happening. Might work for some so I'll leave it up. I did notice that it only happens after a save. If I don't save the file before exit the password box does not come up.

SOLUTION: I read that this issue can happen if you have Acrobat PDFMaker COM addin enabled. I checked, and I did (from an old Acrobat 8 installed with my Adobe Suite). I disabled the addin and the problem goes away.

Steps to disable:
Open Excel in Administrator mode (right click - Select Run as Administrator)
Click the Office Button in top left corner and select Excel Options
Go to Add-Ins
At the bottom next to Manage select COM Add-ins and click Go
If you see that Acrobat PDFMaker Office COM Add-in is checked, uncheck.
Exit Excel and restart.

8

Technical Support / Re: VBA Password Error When Closing the Spreadsheet

« on: July 14, 2017, 09:59:22 pm »

This just perplexes me, because no matter what I do I cant duplicate it.  We will continue to investigate...

Don't you hate that! If you're like me, the solution will pop in your head in the middle of the night Another idea just to eliminate people closing the workbook without using the BIG X or having to leave Full Screen to save - I would create 2 macro buttons on every page - SAVE and SAVE and EXIT. I don't use Excel much and when I first started testing the file I couldn't figure out how to save, so I clicked the BIG Red X so it would prompt me to save.

Although I love (and need) the full screen toggle, I would make it available maybe only on a separate options page. That button on every page invites you to click and makes it waaay too easy to mess things up. That way people that need it will find it, and those that don't won't click it.

Thanks again Scott and Hunter for this little gem of software. Have a great weekend!

9

Technical Support / Re: VBA Password Error When Closing the Spreadsheet

« on: July 14, 2017, 02:30:36 pm »

I just downloaded from Microsoft and re-installed last month as I got new computer. I find that if I close the workbook and wait a minute or so before closing Excel it doesn't crash but the MyCostProVBA Password box still comes up and takes me anywhere between 4-10 clicks to finally close it.

10

General Discussion / Re: Cost of Goods

« on: July 14, 2017, 02:24:52 pm »

Thanks Scott,

This is how I am getting it to work (so far I'll try to state details in case someone else in the group wants to do the same. My main objectives are to be able to input my cost of goods when listing so I don't have to do it later, and get a total Cost of Goods Sold and Current Inventory Value at year end.

If you have an eBay store, the listing form has an area where you can add your Custom Label (SKU). Unlike Amazon, with eBay your SKU's don't have to be different for every item so if I bought 5 books at the Goodwill on July 4th for $2.00 each, all of those books can have a SKU named 2.00-GW-07.04.2017

So I created a new sheet at the end of the MyCostPro workbook and named it Inventory on Hand. I updated my Active Item List, copied the blue Listing section (which includes the SKU I entered in eBay), and did a Paste Special into the new Inventory on Hand sheet. (The paste special just pastes the static data without formulas). I added 2 columns, one for supplier and one for purchase date.

After doing a bit googling I found if I entered this in the COGS column (with G3 being my SKU column):
=LEFT(G3,FIND("-",G3)-1)

it would grab anything before the dash from my SKU (which is my cost) and then all I had to do was drag the formula down and all of my costs were pulled from my SKU and entered instantly. It can do the same for supplier (using MID=) and purchase date (using RIGHT=) which is why I put dashes in my SKU to separate the sections. Now on Dec. 31st or Jan 1st all I have to do is do what I did above, SUM it up and I have a total value of Inventory at year end for Uncle Sam. Sweet!

I also tested this on the Sold Items List for Cost of Goods and it worked there also. If you could add a Supplier and Purchase Date column to the sold items list then that would be complete and you'd be able to get a good overview of your best suppliers and how long items take to sell.

Obviously, this wouldn't work for everyone if they already had different SKU systems in place. But my bet is most small sellers haven't even used the SKU option in eBay yet.

11

Feature Requests / eBay Monthly Store Fee

« on: July 13, 2017, 03:46:51 pm »

I'm thinking that the software doesn't capture the monthly store fee if you have an eBay store (correct me if I'm wrong). If not, a feature in the expenses area could be to have a column for recurring expenses, with maybe a dropdown to add it automatically monthly/quarterly, etc. In that section we could maybe add our own custom recurring expenses (ie rent, subscriptions, etc.) and we wouldn't have to add them manually every month.

12

Technical Support / Re: VBA Password Error When Closing the Spreadsheet

« on: July 13, 2017, 02:26:55 pm »

I also get the MyCostProVBA Password box when exiting and it crashes when I hit cancel on the password box. It does it when I get out of full screen mode and click the x in the upper right and it also does it if I Save first on the left and then click Exit Excel.

I'm using Excel 2007 with Windows 10. Could the issue be because I'm using such an old version of Excel? If so, what is the most stable version tested with mycostpro?

Thanks!

13

General Discussion / Re: Cost of Goods

« on: July 12, 2017, 04:41:30 pm »

Thank you Hunter! I am a member in the FB Group 'Accounting We Will Go' and if you read through some of the posts there you realize how many struggle with this. Some are Amazon only, ebay only and some multi-channel. A lot of people use Inventory Lab (which is only for Amazon) just for accounting purposes only and pay over $35/monthly (I use it both for listing and accounting).

Very promising product you have here. I don't know how I didn't hear about it sooner, but as soon as understand it fully I will definitely spread the word. Maybe in the future you can add Amazon.

Sue

14

General Discussion / Re: Cost of Goods

« on: July 12, 2017, 08:59:11 am »

Thanks guys for the quick response.

I noticed in one of your videos you used the *CD* example to pull in Cost of Goods on all items with CD in the title. Could this wildcard be used in the SKU field instead, something like:

GW-07-12-2017-1.99-1234

or do you have to have something in the title that connects it to the sold item.

These are the types of SKUs I use for Amazon listings (with an automated random 4-digit suffix) using Inventory Lab, so if I'd ever need to produce a receipt I can look in the July receipt folder for a Goodwill receipt from July 12, 2017.

Also, any plans to have a Cost of Goods column in the Active Listings sheet, for end of year inventory value and more automation? Unless I'm missing something in how the Product Master sheet works, that still would seem to be redundant since all of the items I have listed are already in the Active Listings sheet. If we could have a Costs section in that sheet with Supplier, Date Purchased and Cost of Goods I would think that would cover everything. Then we wouldn't even need to mess with skus in eBay unless needed for internal tracking.

I think your program is much needed for ebay sellers (like me) who struggle to keep track of there COGS and year end inventory value and sit surrounded by piles of receipts trying to organize for tax time

15

General Discussion / Cost of Goods

« on: July 11, 2017, 12:00:44 pm »

Hi everyone!

Just downloaded the spreadsheet and I really like it so far. The one thing I would like is to be able to enter my Cost of Goods before the item sells. Almost all of my products are one-offs, purchased from thrift stores, online, etc. By entering the product cost right after listing I can file away my receipts as many items take months to sell and I would never remember what I paid at the time.

Is there a way to add the Item Purchase Price, and right next to that the supplier and purchase date, which would then also be carried over to the Sold Items page when the item sells? That way you would know your ending inventory value for year end tax purposes and also have good documentation to prove your COGS if need be.

Thanks!

Sue