I need a formula for an inventory workbook for to calculate sales and add inventory

1. I need a formula for an inventory workbook for to calculate sales and add inventory

Hi all I am new here. I am creating a spreadsheet for inventory for a charity who sells items to help others.
My question is I have not used excel in years and I have made a workbook with 3 sheets, a Master, add Inventory, and Sales. now I have a dummy list of products so that I can just start with formulas. I have tried several formulas and i am just not getting it right.
Firstly I need to add and delete add inventory and sales sheets from the master inventory sheet.
Secondly I would like to auto populate the sales and add inventory sheets with the price when the product is entered
I Would appreciate any help I can get .
Thank you in advance I have also attached this basically empty spreadsheet ss

2. Re: I need a formula for an inventory workbook for to calculate sales and add inventory

Hi,
What are you trying to do with the formula you apply in G2?
To sum the items in "Add Inventory" and subtract the "Sales" per item?

3. Re: I need a formula for an inventory workbook for to calculate sales and add inventory

Hi,

Please notice the name of your products MUST be the same in all tabs.
you had double space in "Bracelet 1" in Master sheet , whereas in the other tab with only one space; excel will not recognize them as the same expression and will not find a match, so you need to make sure they are typed the same. In order to avoid typos - you can create a Data validation like I did in the attached (in all 3 tabs - product name column), so that you just need to select your product from a given list.

I amended the formula in G2, so that it brings inventory amount and subtracts the sales for each product:

For the price- D2 in "Sales":
=IFNA(VLOOKUP(B2,'Master '!\$C:\$E,3,0),"")

=IFNA(VLOOKUP(C2,'Master '!\$C:\$E,3,0),"")

Hope this is what you need and good luck with your project : )

4. Re: I need a formula for an inventory workbook for to calculate sales and add inventory

Hi Belinda200,I appreciate your help so much. I changed my tab add inventory to stock in, and in input all the formulas yousuggested and they are all coming up with errors I will attach for you. I have looked these over and over to see if I had the columns wrong or cells, but i cannot see them. However i did change some of the cell references as i had to add a row. And yes i see where on bracelets I had two spaces, silly mistake. Can you take a look for me. When i am going through the formula to find error, i can see the cell reference in sales and stock in, are referencing correctly, but i dont see anything on master.

5. Re: I need a formula for an inventory workbook for to calculate sales and add inventory

Actually, I downloaded the one you sent back finally, i was having trouble since i was working from onedrive, (its so restrictive) and what you sent was exactly what i needed Thank you so much

6. Re: I need a formula for an inventory workbook for to calculate sales and add inventory

Great, you're welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

X vBulletin 4.1.8 Debug Information

• Page Generation 0.06134 seconds
• Memory Usage 9,021KB
• Queries Executed 16 (?)
Template Usage (34):
• (1)footer
• (1)forumrules
• (1)gobutton
• (6)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (3)postbit_attachment
• (6)postbit_legacy
• (6)postbit_onlinestatus
• (6)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (50):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_attachment
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit
• tag_fetchbit_complete