1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Another Excel Query

Discussion in 'Business Applications' started by CharlesMalt, Apr 23, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. CharlesMalt

    CharlesMalt Thread Starter

    Joined:
    Mar 30, 2010
    Messages:
    9
    Hi Guys

    I've got two sets of data on separate pages they represent two ways of ordering the same product so the columns match.

    I want a formula so that the master page will collate both sets of data. So as data is entered into either page it is copied into the next available row on the master

    See example

    Hope you can help

    Kind regards

    Charlie
     

    Attached Files:

  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Charles, do you mean that if data is entered into either the Safe Stock Out page or the Indents page it would also show automatically be entered into the other page?
    Not very good spreadsheet design, if that is what you want. Why duplicate the data? Also, if you want to be able to enter into either page, that will require VBA code to automatically update both sheets. If you just wanted to show the same data with a somewhat different layout, and would always only use one page for entry of data, then you could do this with formulas.
    However, for how this looks, I might suggest you build a database in Access and then export the data to Excel from queries or reports as you desire.
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Look into the Consolidation feature. You'll create two links, one to each sheet. The key is your format will end up in more columns than you currently have because of how you've entered your column headers. Make sure you keep on the link to the old data and you should be good to go. It basically uses formulas linking to the specified ranges, and groups to show subtotals of each like data row. This is perfect for what you're looking to do. Just get your column headers a little better formatted and it will look identical. You have the option to use the top row as headers brought in from the source data. With your current data structure DO NOT use this option and it won't split into multiple columns. You didn't say what version you were using, so assuming it's 2007 look on the Data tab for the Consolidation button. In previous versions it should be on the Data menu.

    HTH
     
  4. CharlesMalt

    CharlesMalt Thread Starter

    Joined:
    Mar 30, 2010
    Messages:
    9
    Okay thanks for coming back to me Slurpee / Zack

    Right, the other page is actually in another workbook.

    There will be three password protected workbooks for three individuals. The first two will be for individuals managing two sides of a stock control process, the third "master" will combine data allowing a manager to oversee the validity of the ongoing process.

    Access would be lovely but no-one at this company has it on their machines and I don't think they even have a licence, so I'm trying to acheive a 'next best' solution with excel (2003).

    So.

    Spreadsheet one
    This has the data on stock in the safe & stock ordered so when stock is requested the owner will either send the order to distribute stock from the safe or if there is insufficient in the safe order more stock from the supplier and update the spreadsheet accordingly. This data would then be copied / collated on the master.

    Spreadsheet two
    Will track the distribution data (stock In-Coming from safe or supplier) from the Master. The owner would then input Out-Going data to track what went to who when & if there is an excess of stock how much is returned to the safe and update the spreadsheet accordingly. (Stock returned to the safe needs to be tracked and spreadsheet one would pick this data up from the Master).

    Spreadsheet three
    Will track all the data entered into the other two spreadsheets and would effectively be 'view only' Spreadsheet one & two would import and retrieve their data from three.

    I'm pretty sure it's do-able but not sure of the best way to attack it or if it's the best way to approach the problem in excel.
    I have another issue to contend with as well. The stock are Vouchers for making payments instead of using cash, they all have serial numbers that I'm tracking and the vouchers come in a range of values. I think I've got that covered by putting the serial numbers in columns by denomination, running a count on a column and multiplying it by the denomination to return the value of product in the safe but I'm working on a simple to maintain method of moving the serial numbers into a 'out' whilst allowing for more to come 'in'.
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I am going to put this as succinctly as I can without coming off too rudely. If you are going to make any more posts, at any time in your future, you must put all of the information in your post. Perhaps you don't think it made a difference if you told people that the data was in different workbooks. I honestly don't have time to help you if your story is going to change. My time is so tight and valuable right now, I won't help people who don't post everything. It changes the dynamics of the issue so much that often times it's like creating a whole new solution all over again. And I'm not into doing twice as much work for no reason at all. Maybe others here have more time to offer. Please don't take offense to this, just pretty please listen and try to understand.

    Okay, now with that out of the way, let me ask you some questions here.

    Your formula in N13 of the 'Safe Stock Out' sheet is:
    Code:
    =(I13*50)+(J13*20)+(K13*10)+(L13*5)+(M13)
    Where it could be:
    Code:
    =SUMPRODUCT(($I13:$M13)*($I$12:$M$12))
    Copy the formula down only as far as needed. No need to go to row 108 like you did before. If you want to copy the formula down, go to the cell underneath it and press Ctrl + D (fill down). This can also be done via code if you would like on a worksheet change event.

    The password on the files, they're just open passwords right? That is plenty doable. Here is a small example of how to open a file with a known password and known file location, and setting it to a variable:
    Code:
    Sub testOpen()
        Dim wb As Workbook, sFile As String, sPassword As String
        sFile = "C:\Users\Z\Desktop\testA.xls"
        sPassword = "a"
        Set wb = Workbooks.Open(Filename:=sFile, Password:=sPassword)
    End Sub
    I'm assuming that you are on a network, so you would need access to all the files.

    One option you might want to think of, depending on what kind of network you have in place, is to use SharePoint Server and check out the file. You will need access to the files though. Of course I don't have to tell you that an actual database is designed to handle these exact situations, and that it would be in your companies best interest to invest into making the small purchase of Access, or some other database, to adequately handle this task.
     
  6. CharlesMalt

    CharlesMalt Thread Starter

    Joined:
    Mar 30, 2010
    Messages:
    9
    Hi Zack

    My apologies, not rude at all, succinct indeed & understood.

    I've built something that'll do the job but it is imperfect at best and you are right of course that Access (or equivalent) would be a much more straightforward solution.

    Thank you for the help it was much needed.

    Kind regards

    C
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Charles, have you looked at possibly using OpenOffice.org for the database concept? It is free and while the database may not be quite as good as Access, it sure isn't shabby. You can link to Excel files from it, I think, which would allow better data control. The whole thing of having multiple Excel sheets is a pain at best, and they need to be open at the same time to update etc., etc.
     
  8. CharlesMalt

    CharlesMalt Thread Starter

    Joined:
    Mar 30, 2010
    Messages:
    9
    Yeah the open to update issue is going to be tedious & the whole thing is more fragile but the people managing the process seem to be happy enough (they even got me a card and a gift, imagine if I'd done a proper job:))...Got Office 2007 & OpenOffice at home and you're right it would do the trick - if only the I.T. Police at work would release their grip a bit...I'm a temp and I can't download anything without crossing i's and dotting t's (you wouldn't believe how hard it was to get Acrobat Pro)...good news though, they are planning on aquiring some database software in the coming months. Shame my contract ends tomorrow:)
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Another Excel
  1. ranger1
    Replies:
    1
    Views:
    422
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/918715

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice