Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    associating cells between sheet tabs (2002)

    Hello all...
    I know this can be done in Access, but I'd rather use Excel...
    I have my own business and I'm using Excel to keep records...(I'm also using Quickbooks Premiere, but this is so I can e-mail this file to others who don't have QB)
    I have a sheet tab for the following:
    -Information (containing Info about the workbook and my business entity)
    -Sales (showing orders)
    -Expenses (showing any expenses incurred)
    -Customers (list of customer names, addresses, etc)
    -Vendors (list of my distributors)
    Now, I have created a few column values so that I can organize the data easily...
    namely the following:
    -CustomerID
    -OrderID
    -VendorID
    -ExpenseID

    In my calculations (functions or formulas) I want to be able to not only reference another tab (3d Reference - I know how to do this), but I also want to be able to associate the ID's between sheet tabs...
    is there a way to do this?
    for instance:
    Under Customers tab, I have columns for Customer ID, name, address, state, etc...
    under the Orders tab, I have to calculate state tax for my state (PA)...and I have columns for OrderID, CustomerID, VendorID, Amount, S&H, Tax, Total, and some other stuff...
    Amount= base price (user input)
    S&H= flat rate (user input)
    Tax= IF statement, saying, if CustomerID's state is equal to "PA", calculate tax as 6%, if false, calculate it as 0%
    Total= IF statement, saying if tax=0%, calculate Amount plus S&H, if its 6% (rather, not 0), to calculate the total as Amount plus S&H * 1.06

    I also have a few other associations I'd like to make...but this is the easiest example to explain

    can this be done? and if so how?
    tia

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: associating cells between sheet tabs (2002)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> mallenbu

    I guess what you are saying is that all these entries should be coming from their respective worksheets, so your formula would need to have the worksheet name in it followed by the cell address example:

    <font color=red> =Sheet1!A1 </font color=red>

    This formula will return what is in A1 on sheet1.

    So you can also use the row number to get other data as well.

    I hope I answered your question, if not please reply with more details.

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: associating cells between sheet tabs (2002)

    what you said would work only if I had a new sheet for every customer...I want to create a relationship (as it is called in Access) between the same column headings (OrderID, CustomerID, VendorID, and ExpenseID) of each sheet that they appear on...

    what you said is a 3d cell reference and I do know how to that as it is pretty basic...
    I hope the details are more celar now...its kind of hard to explain...
    thanks for the reply though...if you have any new thoughts, I'd be glad to hear them...
    if you'd like, I can upload the Excel file to a site so you can see what I mean...

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: associating cells between sheet tabs (2002)

    mallenbu

    When you say relationship what is that relationship?

    I am bypassing the obvious and the "basic" as you said, and am trying to understand what you are trying to do.

    If you want to have a relationship in the sense that an orderID depends on a CustomerID, (a customer can have many orders but an order can only have a single customer, or at least 90% of the time that is the case) then you need a VLookup table or something simlar.

    For each Customer you will have a bunch of records that each has a unique OrderID. Then you need to look up the OrderID by looking up the CustomerID because they will be on the same row. I suggest you use Filtering vs the simple Find command.

    Then you will know what the orderID is, and thus will go to that worksheet that has the orders and find that matching orderID. In any "database" you need x=1 tables where that extra table joins all these IDs together. You would look up in that table=worksheet and then go find the data where it is in the other worksheets.

    I hope I got it this time, but again you truly want to emulate a database behavior, so why not use a database in the first place?

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: associating cells between sheet tabs (2002)

    OK, lets say you have a sheet named Customer and it contains 5 columns:

    A=Customer Number
    B=Customer Name
    C=Street Address
    D=Town
    E=State

    On another sheet, A1 contains a customer number, and you want to enter a formula in another cell that gives the sales tax muiltiplyer for the customers state. You would use this formula:

    <pre>=IF(VLOOKUP(A1,Customer!A1:E100,5,FALSE)="PA" ,1.06,0)
    </pre>

    Legare Coleman

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: associating cells between sheet tabs (2002)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> mallenbu

    The attachments helped a lot, though you could have attached them to your lounge message just the same.

    OK so I see what you are trying to do, and I have a fundamental question that i should have asked earlier, and that is do you want to use VBA?

    If you would create a userForm for all of your data entries then you will be able to ease the amount of work needed to finish this project.

    What I like about Userforms is that it allows you to control what goes on during User Interaction and respond to "events" gracefully. Its doable with worksheet data entry as well, but its harder to keep saine that way.

    OK a userform will ask the questions to pick the customer, the ones on the Infor worksheet. This could be summarized by just picking a company Name and then the rest is filled.

    Then the user will switch to a second tab on the form and enter an order, it will generate an order ID automatically.

    The tax question could be eliminated via an If statement, but again its your project and you think you need it or maybe give the user an option to override but that could be a part of the Order tab based on the order ship to or company state or what have you.

    So you see once you hit the Ok button on the form it will write the data for the order with the information about the customer all at once.

    You will still need a table, maybe the sales table can be it, to tie all the IDs together. Also in your diagram you don't have a table for Orders.

    Now the amounts in the Orders table could transfer to the Expense table with accounts built-in and all of that.

    If you will be using the same structure that is in the workbook that I downloaded I can help you with the Userform over the weekend.

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    New Lounger
    Join Date
    Apr 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: associating cells between sheet tabs (2002)

    <P ID="edit" class=small>Edited by WebGenii on 04-Apr-02 19:26.</P>You are completely correct on the database software...however, not everyone is as familiar with Access as Excel and some other people will need this data...
    what you said about the VLookup table sounds to be what I need...
    I've uploaded some files to explain what I mean...First I took my spreadsheet and cut it down and made up fake stuff...
    http://quake.hypermart.net/TEST.xls
    the relationship idea I tried to express visually, even though it sounds like you know what I mean
    http://quake.hypermart.net/relate.bmp

    or you can download both (for security reasons) http://quake.hypermart.net/test.zip


    basically, I'm asking about this for tax reasons...if I want to use it for other things, I can use this as a reference...

    so, as you can see from the book, when I get an order (leaving the Vendors Tab and the Expense Tab out of this scenario), I will first enter the data into the Customer Tab.
    Then I will enter the data in the Orders tab, and as soon as I enter the CustomerID for the Order, I want the Question (Should I use 6% sales tax or not?) to be answered..then as I enter the amount of the product and the shipping, sales tax and the total amount will have been calculated...
    as you can further see, I have it setup now so that sales tax is figured out correctly, but only if the OrderID cell number is the same as the Customer ID cell number...

    if in fact the VLookup table is what I need, any information on that would be greatly appreciated...its been awhile since I've done any programming, but I'll work hard at it...

    thanks again for replying back...
    I have to run out to do some business, so please don't think it rude of me not to post back (I'm sure you wouldn't, but I'm just being polite)
    have a good day!

  8. #8
    New Lounger
    Join Date
    Apr 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: associating cells between sheet tabs (2002)

    <P ID="edit" class=small>Edited by WebGenii on 04-Apr-02 19:32.</P>Wassim...thank you very much for your help on this...
    I visit a lot of forums and many have different protocol about attaching items...I just use my own little thing to make it easy...
    The excel spreadsheet would never go to customers (I wouldn't want them seeing my list of vendors, or the price I paid)
    however, using customized forms does sound quite nice...for my own personal ease of entering the data...
    since that requires VBA, I'd be happy to give it a go...
    pushing back to the tax problem...I found a site http://www.thewordexpert.com/tipwarez.htm#MyInvoicing which also had http://www.thewordexpert.com/tipwarez.htm#MyVlookup
    these have helped immensely (kudos on over to LegareColeman for posting what I found)
    and they pretty much solve my problem...

    However, you obviously know your way around Excel, and after I do some major reformatting on my spreadsheets (not to mention putting all the data back in), forms will start to look pretty good sooner or later, and I'll be contacting you about those...but it may not happen...
    especially around tax season (businesses have to file quarterly)

    I do want you to know that I extremely appreciate the level of concentration you gave me today...it has helped a lot and I would like to personally thank you...
    maybe a discount on an item or two in the future...shoot me a PM or whatever they're called here with your e-mail address...

    thanks again

Posting Permissions

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