Results 1 to 7 of 7

Thread: Using multiple sheets but only pull from one
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using multiple sheets but only pull from one

    Hello, I designed a workbook for my co-workers to easily do their expenses and cash out for the customers. I made it multiple ways and allow each person to use it the way they fill comfortable I.e multiple days, single days, etc. All of the sheets link to a totals sheet which has a printable format for the customer. I fear that someone may input data on a non used sheet which will cause a higher total and the person may not realize it. How can I have multiple sheets to pick from all linking to a master total sheet but if data is filled in from multiple sheets it will either let the user know or only pull from the sheet they are filling out? If a sample needs to uploaded let me know and I will upload the copy I made.

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,185
    Post Thanks / Like
    Mentioned
    56 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Using multiple sheets but only pull from one

    Welcome to the forum

    Are the numbers in the cells in the Totals sheet a simple summation of ALL numbers in each of the optional sheets ?
    - if so, post the formula for any ONE of the cells in the Totals sheet so that we can see how they link together

    Is there ever a situation where the user is permitted to enter data in more than one sheet ?
    - this would complicate things and the most helpful answer is NO

    Is using VBA acceptable ?
    - the VBA would be very basic
    Last edited by Yongle; May 6th, 2019 at 07:15 AM.

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using multiple sheets but only pull from one

    There is never a time that they would use more then one sheet on purpose.

    The total sheet actually just pulls the final SUM =SUM(‘sheet 1’!B2, ‘sheet 2’ !B2, etc.) I thought there was an OR command that would do this and alert the user to many items were filled out.

    The reason it is broke down is sometimes the customer is charged airfare, car rentals, multiple day hotels, etc. and sometimes the customer is only charged the mileage and hours. So each sheet is for a different situation 4 total situations.

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using multiple sheets but only pull from one

    I have never done VBA before. I would have to look into doing it. I know the basics of excel and learning as I go with more tasks that are needed.

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,185
    Post Thanks / Like
    Mentioned
    56 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Using multiple sheets but only pull from one

    There are several ways available to warn the user in the Totals sheet

    One way (avoiding VBA) would be to use a cell in EVERY subsidiary sheet to sum all numeric values entered by the user in that sheet
    - the total does not need to be meaningful (it is simply a way to spot that something has been entered on the sheet)

    If G1 is the cell in each subsidiary sheet, then formula below can be used to test that only one of those cells contains a value
    - this makes use of the property that FALSE equates to zero and TRUE equates to 1
    - 2 should be FALSE and 1 should be TRUE (2 X 0 )+ (1 X 1) = 1
    - any other combination would result in 0 , 2 or 3

    =IF((Sheet1!G1<>0)+(Sheet2!G1<>0)+(Sheet3!G1<>0) = 1,"OK","Problem!")
    Last edited by Yongle; May 7th, 2019 at 08:10 AM.

  6. #6
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using multiple sheets but only pull from one

    Thank you so much. I am going to try this tonight and see how it works out

  7. #7
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using multiple sheets but only pull from one

    It ended up getting really complex with each page being somewhat different. I ended up making two versions. One that if any block is great than “0” it comes up as a red block and says “Data found on other sheets” with a “Clear Other Sheets” button next to it assigned the macro of clear contents. The other version I locked all pages except the first one where I only allowed the user to input the data needed. No fancy stuff but all other sheets pull from that first page. So if they want to view it a different way they can but it’s made with most often used to least often used so it’s simple, easy, and effective. Also I added a clear contents button on second one also just Incase if it is easier for the user.

    Thank you so much for the inputs. I will probably change it more in the future to suit different needs. Probably use more VBA to calculate what the user needs but for now they get it all and don’t use what they don’t need.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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