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.

excel creating a new sheet based on results from two sheets

Discussion in 'Business Applications' started by etaf, Nov 19, 2007.

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

    etaf Moderator Thread Starter

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    I get sent a spreadsheet, which has loads of data in, which is used, but i want to add a new sheet into the workbook called say "ratio", based on a sheet I can create "sales "and uses one of the existing sheets "customers" sent to me. with the minimum work

    So i have attached an example of the one sheet I get sent (remember this is in a workbook with lots of other sheets and data - which needs to be kept) "customer"

    I then have a sheet of sales with a similar layout

    column A will have some of the same names in my sales sheet
    so I update my sales into a "sales" sheet for the latest week, and copy the sheet into the workbook i have been sent

    i now want a new sheet created which will
    look at the names in columnA of "sales" and where they match the name in columnA of "customers " then on a new sheet called "ratio"do a calculation for the rows that have the same dates in

    customer/Sales

    I need to update and repeat this process every week.

    Any help please
     

    Attached Files:

  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi etaf,

    Can you describe for us a little more about where these workbooks will be located and how they cross your path exactly? from what I understand, you'll have two workbooks, one will contain a sheet called "sales" and the other file will contain a workbook with the name "customers". You want to create a sheet with a common list of names from column A? Do you want the data transferred as well? And if so, from which of the two files? Will the two files be open? If so, would you like a form to have all of the open workbooks listed in two combo boxes? If not, would you like to have an open dialog box to choose the file(s)?
     
  3. djangojazz

    djangojazz

    Joined:
    Apr 11, 2006
    Messages:
    301
    That sounds like SUMIF function

    Sumif works like this if a range says this, sum this other range. I don't know exactly what you want since you just gave one sheet with customers.

    You can just specify other sheets in your worksheet by going '(name of sheet)!'

    EG:

    =SUM(Customers!B2:L2)

    See attached.
     

    Attached Files:

  4. etaf

    etaf Moderator Thread Starter

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    Thanks for the replies

    The workbook with the customers sheet in will be emailed and then saved onto a laptop each week (the admin),
    the workbook with the sales sheet will always exist on the local admin laptop and she will update each week.

    I then intended to copy the "sales" worksheet into the Customer Workbook on the laptop, and then create a new worksheet called ratio

    this new sheet called Ratio will then need to look at the following

    ColumnA in "customer" and columnA in "sales" where they are the same then do a calculation on customer/sales for every column (i can at least make sure the weeks are in the same columns)

    i'll have a look at the function sumif and see how that work,

    I guess because i'm copying in sheets this may end up with REF! against each calculation mmmmmm

    The two files can both be open as they are under the admin control each week
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    So the scope of this is only on the Admin's laptop?

    What would you like the "ratio" sheet to look like? What calculations specifically? What columns should be copied? Do you want formulas derived from this data? If so, what?

    Sounds like this might go well as an add-in on your admin's computer with a userform and a couple of buttons for them to choose the two files. Perhaps on a custom toolbar or menu? Please let us know if you are interested in this route.

    Can you supply a copy of your "sales" sheet so we can compare to the "customers" you already posted? And can you give us an example of what you're looking at for a "ratio" sheet? Perhaps lump all three sheets in one sample file and post it? A real-time example (can be dummy data).
     
  6. etaf

    etaf Moderator Thread Starter

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    I have posted an example - so where there is a match between the two sheets - sales & Customers we have a entry on the ratio sheet
    the ratio is sales/customers
    All the columns with a date header that match

    sounds great -

    example attached
     

    Attached Files:

  7. etaf

    etaf Moderator Thread Starter

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    thanks, I have used the vlookupfunction thanks - seems to have solved the issues, as we can just use one spreadsheet now too
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Etaf, did you still want an add-in for this? I was putting one together. If you don't need it anymore I'll abandon it. What I had was a custom menu bar, which launches a custom userform with two Browse buttons, one for each file. Would create a new workbook with a sheet called ratio based on the other two sheets from their respective workbooks. Let me know. :)
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Etaf: bump. :)
     
  10. etaf

    etaf Moderator Thread Starter

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    no thanks, you fixed all i neededin the other posts as it made it easy to do now ..

    Thanks for all your efforts and support
    all the best for the holiday season
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Great. :) You enjoy the holidays as well!!
     
  12. 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...
Thread Status:
Not open for further replies.

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

  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