Using multiple sheets but only pull from one

SJCLane

New Member
Joined
May 6, 2019
Messages
5
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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,725
Office Version
365
Platform
Windows
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:

SJCLane

New Member
Joined
May 6, 2019
Messages
5
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

New Member
Joined
May 6, 2019
Messages
5
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

Well-known Member
Joined
Mar 11, 2015
Messages
5,725
Office Version
365
Platform
Windows
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:

SJCLane

New Member
Joined
May 6, 2019
Messages
5
Thank you so much. I am going to try this tonight and see how it works out
 

SJCLane

New Member
Joined
May 6, 2019
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,458
Messages
5,450,574
Members
405,619
Latest member
mjh237

This Week's Hot Topics

Top