+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 26

Looking for help with daily auto population of fields

  1. #1
    Registered User
    Join Date
    01-28-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2016
    Posts
    43

    Looking for help with daily auto population of fields

    I would like to use a book similar to the one attached to track daily sales of individual products. As seen in the attachment I would copy and past a daily update that is emailed to me every workday to the tab named load daily here. From this I would like to have the other two sheets auto update.
    For tab 1 (Inventory List) I would like to add any new data including new items. I know some of the fields would still be blank but they would be filled manually. I would also like the Quantity in Stock column to move to Previous Day Onhand column. Finally on the tab marked Daily Sales I would like to see auto population of rows that changed by date. I would like to keep this data to complete a running total so new data would have to load in the next empty row. I would like negatives that are caused by inventory receipt to equal 0 so it doesn't subtract from sales but when the next day loads I would like to continue tracking sales.
    The list of items I am working with is about 500 items so I don't want to auto populate every item everyday.

    I am running Excel 2016 now

    I hope I have enough detail here and hope the attachment is on this post
    Thank you in advance for any help.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    6,373

    Re: Looking for help with daily auto population of fields

    This is a help Forum not a free consultancy site.

    Normally I would ignore a request like yours.

    However I had a similar job to yours twenty years ago and know how frustrating it can be so I will help you this time.

    Load your data onto the Daily Update sheet, then select the Update sheet.
    Attached Files Attached Files
    Last edited by mehmetcik; 01-05-2019 at 10:57 AM.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Registered User
    Join Date
    01-28-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2016
    Posts
    43

    Re: Looking for help with daily auto population of fields

    Thank you again for the offer to help. Although I have been a member for a few years I honestly have just recently started to use excel to create files rather than just a user of other peoples files. I am really enjoying using Excel and I like to try new things with it and that is why I have come to this site looking for solutions from Excel masters like yourself. I am sorry if I appeared to be looking for free consultancy as that is not my intention, my hope was to get a solution or maybe some help to steer me to a solution, but again I Thank you for your interest in helping me and look forward to more conversation with you and others that may have solutions.

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    6,373

    Re: Looking for help with daily auto population of fields

    I used two macros here.

    The first one is sheet specific. It runs the main macro when you click on the Update Tab.

    Right Click On Your Sheet Name At the Bottom Of Excel and Select View Code
    Paste this code in the module that opens and close it.

    Please Login or Register  to view this content.

    The second Macro goes into a normal macro module.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-28-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2016
    Posts
    43

    Re: Looking for help with daily auto population of fields

    Thank you again for your work on this and for understanding what I am trying to do.

    I tried the attached updated file and I keep getting an error when I hit the update tab.

    Compile error:
    Sub or Function not defined

    I am very new to using excel and I am not sure what to do next?
    Once I know how it works in the sample file I am pretty sure I can apply it to the working file so again your help would be appreciated.

  6. #6
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    3,244

    Re: Looking for help with daily auto population of fields

    Remove the word Private from this
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-28-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2016
    Posts
    43

    Re: Looking for help with daily auto population of fields

    Thank you for the reply Fluff13, I removed the word Private which cleared the error but I am not seeing anything happen in the file when I change data in the Load Daily Here tab. I am sure it is probably something I am doing wrong but would appreciate confirmation if you could check.

  8. #8
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    3,244

    Re: Looking for help with daily auto population of fields

    Did you select the UPDATE tab?

  9. #9
    Registered User
    Join Date
    01-28-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2016
    Posts
    43

    Re: Looking for help with daily auto population of fields

    I changed data in the Load Daily tab then I selected the update tab and see no changes?
    I have attached the file with the changes that I made in the hope that someone can help me figure out what I am doing wrong.
    Thank you again
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    3,244

    Re: Looking for help with daily auto population of fields

    Try changing this as shown
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    01-28-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2016
    Posts
    43

    Re: Looking for help with daily auto population of fields

    Changed it from 6 to 2 as suggested, I am still not seeing updates on the Inventory List sheet and the Daily Load sheet is changed to currency in the Quantity in Stock column?
    I am in all new territory here as I don't understand VB code and I appreciate your patience.

  12. #12
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    6,373

    Re: Looking for help with daily auto population of fields

    Hi

    1. Sorry I went for a nap as you were not around.

    I have tried this again and made a couple of changes.

    Try this version.

    1. Enable Macros when you open the workbook.
    2. You must have data in the Update Sheet for the Macro to Run.

    2. I noticed that you have shaded alternative rows on your spreadsheet.

    The problem is that you can you can mess up the shading if you fill down or fill up.

    Select cell B7 and move your cursor to the bottom right hand side so it looks like "+" then drag it down.

    All your formatting is messed up.

    You should use conditional formatting to achieve the same result.

    Select rows 7 to 33
    Select the home tab
    Select conditional formatting, new rule
    Enter this formula into the formula box "=AND(ISEVEN(ROW()),COLUMN()<16)"
    Then select Format, Fill, and select your fill colour, then OK
    Attached Files Attached Files
    Last edited by mehmetcik; 01-05-2019 at 04:23 PM.

  13. #13
    Registered User
    Join Date
    01-28-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2016
    Posts
    43

    Re: Looking for help with daily auto population of fields

    Thank you again and sorry for not being available straight through this project. What you have done is amazing! I am wondering if there is code that could be added to add new items to the inventory list as well. I am hoping for just the info that is loaded in the Load Daily Here sheet can added so if a new item comes into stock it will also start tracking or would this have to be added manually?

  14. #14
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    6,373

    Re: Looking for help with daily auto population of fields

    No I can do that for you.


    Try this version. This is a starting point. You need to give me samples of before and after data.
    Attached Files Attached Files
    Last edited by mehmetcik; 01-05-2019 at 08:00 PM.

  15. #15
    Registered User
    Join Date
    01-28-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2016
    Posts
    43

    Re: Looking for help with daily auto population of fields

    Thank you, my actual data has about 500 hundred items and sees phase in/phase out of products as designs change. So tracking sales for both would allow for more complete data capture when I look at weeks, months/years...

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1