Using multiple sheets but only pull from one

[SJCLane]

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.

 

[Yongle]

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

 

[SJCLane]

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.

 

[SJCLane]

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.

 

[Yongle]

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!")

 

[SJCLane]

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

 

[SJCLane]

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.