Data calulations conditional formatting to another sheet

1

10beers

New Member
Joined
Feb 22, 2011
Messages
1
Im am in need of some help with a current work problem. I have a master data entry sheet with Headers in column 1 and each additional column is 1x event.
Data Entry Sheet:
Excel Workbook
ABCDEFG
1Julian Date123456
2GroupABCDEF
3# of passengers123456
4Transportation ModeAirGroundAirGroundAirGround
5Time Request Generated09:0109:0109:0209:0309:0409:05
6Request submitted09:1009:1109:1209:1309:1409:15
7Mission Approval Time09:1509:1609:1709:1809:1909:20
8Mission NumberA1A2A3A4A5A6
9Launch Time09:2009:2109:2209:2309:2409:25
10Customer Arrives Drop off point09:3009:3109:3209:3309:3409:35
11Time arrived Destination09:4010:4109:4210:4309:4410:45
Data Entry
Excel 2007

Based on the type of event label I would like it to auto populate the time differences I have created. Currently I am able to get the calulations to occur as I want, but it populates into all sheets. I think a conditional if then type formula would work, but I dont want to leave empty columns.
Air Mission Sheet:
Excel Workbook
ABCDEFG
1Julian Date123456
2GroupABCDEF
3# Passengers123456
4Time Difference from Request submitted from Request Gernation time9 min10 min10 min10 min10 min10 min
5Time from Submission to Mission Approval5 min5 min5 min5 min5 min5 min
6Mission approval to Launch Time5 min5 min5 min5 min5 min5 min
7Launch Time to Drop off point20 min80 min20 min80 min20 min80 min
8Drop off to Destination Time10 min70 min10 min70 min10 min70 min
9Total Mission Time30 min90 min30 min90 min30 min90 min
Air
Excel 2007
Cell Formulas
RangeFormula
B1='Data Entry'!B1
B2='Data Entry'!B2
B3='Data Entry'!B3
B4=TEXT(IF((('Data Entry'!B6-'Data Entry'!B5)*1440)<1, ('Data Entry'!B6-'Data Entry'!B5)*1440+1440, ('Data Entry'!B6-'Data Entry'!B5)*1440), "#") & " min"
B5=TEXT( IF((('Data Entry'!B7-'Data Entry'!B6)*1440)<1, ('Data Entry'!B7-'Data Entry'!B6)*1440+1440, ('Data Entry'!B7-'Data Entry'!B6)*1440), "#") & " min"
B6=TEXT(IF((('Data Entry'!B9-'Data Entry'!B7)*1440)<1,('Data Entry'!B9-'Data Entry'!B7)*1440+1440,('Data Entry'!B9-'Data Entry'!B7)*1440),"#")&" min"
B7=TEXT(IF((('Data Entry'!B11-'Data Entry'!B9)*1440)<1,('Data Entry'!B11-'Data Entry'!B9)*1440+1440,('Data Entry'!B11-'Data Entry'!B9)*1440),"#")&" min"
B8=TEXT( IF((('Data Entry'!B11-'Data Entry'!B10)*1440)<1, ('Data Entry'!B11-'Data Entry'!B10)*1440+1440, ('Data Entry'!B11-'Data Entry'!B10)*1440), "#") & " min"
B9=TEXT( IF((('Data Entry'!B11-'Data Entry'!B6)*1440)<1, ('Data Entry'!B11-'Data Entry'!B6)*1440+1440, ('Data Entry'!B11-'Data Entry'!B6)*1440), "#") & " min"
C1='Data Entry'!C1
C2='Data Entry'!C2
C3='Data Entry'!C3
C4=TEXT(IF((('Data Entry'!C6-'Data Entry'!C5)*1440)<1, ('Data Entry'!C6-'Data Entry'!C5)*1440+1440, ('Data Entry'!C6-'Data Entry'!C5)*1440), "#") & " min"
C5=TEXT( IF((('Data Entry'!C7-'Data Entry'!C6)*1440)<1, ('Data Entry'!C7-'Data Entry'!C6)*1440+1440, ('Data Entry'!C7-'Data Entry'!C6)*1440), "#") & " min"
C6=TEXT(IF((('Data Entry'!C9-'Data Entry'!C7)*1440)<1,('Data Entry'!C9-'Data Entry'!C7)*1440+1440,('Data Entry'!C9-'Data Entry'!C7)*1440),"#")&" min"
C7=TEXT(IF((('Data Entry'!C11-'Data Entry'!C9)*1440)<1,('Data Entry'!C11-'Data Entry'!C9)*1440+1440,('Data Entry'!C11-'Data Entry'!C9)*1440),"#")&" min"
C8=TEXT( IF((('Data Entry'!C11-'Data Entry'!C10)*1440)<1, ('Data Entry'!C11-'Data Entry'!C10)*1440+1440, ('Data Entry'!C11-'Data Entry'!C10)*1440), "#") & " min"
C9=TEXT( IF((('Data Entry'!C11-'Data Entry'!C6)*1440)<1, ('Data Entry'!C11-'Data Entry'!C6)*1440+1440, ('Data Entry'!C11-'Data Entry'!C6)*1440), "#") & " min"
D1='Data Entry'!D1
D2='Data Entry'!D2
D3='Data Entry'!D3
D4=TEXT(IF((('Data Entry'!D6-'Data Entry'!D5)*1440)<1, ('Data Entry'!D6-'Data Entry'!D5)*1440+1440, ('Data Entry'!D6-'Data Entry'!D5)*1440), "#") & " min"
D5=TEXT( IF((('Data Entry'!D7-'Data Entry'!D6)*1440)<1, ('Data Entry'!D7-'Data Entry'!D6)*1440+1440, ('Data Entry'!D7-'Data Entry'!D6)*1440), "#") & " min"
D6=TEXT(IF((('Data Entry'!D9-'Data Entry'!D7)*1440)<1,('Data Entry'!D9-'Data Entry'!D7)*1440+1440,('Data Entry'!D9-'Data Entry'!D7)*1440),"#")&" min"
D7=TEXT(IF((('Data Entry'!D11-'Data Entry'!D9)*1440)<1,('Data Entry'!D11-'Data Entry'!D9)*1440+1440,('Data Entry'!D11-'Data Entry'!D9)*1440),"#")&" min"
D8=TEXT( IF((('Data Entry'!D11-'Data Entry'!D10)*1440)<1, ('Data Entry'!D11-'Data Entry'!D10)*1440+1440, ('Data Entry'!D11-'Data Entry'!D10)*1440), "#") & " min"
D9=TEXT( IF((('Data Entry'!D11-'Data Entry'!D6)*1440)<1, ('Data Entry'!D11-'Data Entry'!D6)*1440+1440, ('Data Entry'!D11-'Data Entry'!D6)*1440), "#") & " min"
E1='Data Entry'!E1
E2='Data Entry'!E2
E3='Data Entry'!E3
E4=TEXT(IF((('Data Entry'!E6-'Data Entry'!E5)*1440)<1, ('Data Entry'!E6-'Data Entry'!E5)*1440+1440, ('Data Entry'!E6-'Data Entry'!E5)*1440), "#") & " min"
E5=TEXT( IF((('Data Entry'!E7-'Data Entry'!E6)*1440)<1, ('Data Entry'!E7-'Data Entry'!E6)*1440+1440, ('Data Entry'!E7-'Data Entry'!E6)*1440), "#") & " min"
E6=TEXT(IF((('Data Entry'!E9-'Data Entry'!E7)*1440)<1,('Data Entry'!E9-'Data Entry'!E7)*1440+1440,('Data Entry'!E9-'Data Entry'!E7)*1440),"#")&" min"
E7=TEXT(IF((('Data Entry'!E11-'Data Entry'!E9)*1440)<1,('Data Entry'!E11-'Data Entry'!E9)*1440+1440,('Data Entry'!E11-'Data Entry'!E9)*1440),"#")&" min"
E8=TEXT( IF((('Data Entry'!E11-'Data Entry'!E10)*1440)<1, ('Data Entry'!E11-'Data Entry'!E10)*1440+1440, ('Data Entry'!E11-'Data Entry'!E10)*1440), "#") & " min"
E9=TEXT( IF((('Data Entry'!E11-'Data Entry'!E6)*1440)<1, ('Data Entry'!E11-'Data Entry'!E6)*1440+1440, ('Data Entry'!E11-'Data Entry'!E6)*1440), "#") & " min"
F1='Data Entry'!F1
F2='Data Entry'!F2
F3='Data Entry'!F3
F4=TEXT(IF((('Data Entry'!F6-'Data Entry'!F5)*1440)<1, ('Data Entry'!F6-'Data Entry'!F5)*1440+1440, ('Data Entry'!F6-'Data Entry'!F5)*1440), "#") & " min"
F5=TEXT( IF((('Data Entry'!F7-'Data Entry'!F6)*1440)<1, ('Data Entry'!F7-'Data Entry'!F6)*1440+1440, ('Data Entry'!F7-'Data Entry'!F6)*1440), "#") & " min"
F6=TEXT(IF((('Data Entry'!F9-'Data Entry'!F7)*1440)<1,('Data Entry'!F9-'Data Entry'!F7)*1440+1440,('Data Entry'!F9-'Data Entry'!F7)*1440),"#")&" min"
F7=TEXT(IF((('Data Entry'!F11-'Data Entry'!F9)*1440)<1,('Data Entry'!F11-'Data Entry'!F9)*1440+1440,('Data Entry'!F11-'Data Entry'!F9)*1440),"#")&" min"
F8=TEXT( IF((('Data Entry'!F11-'Data Entry'!F10)*1440)<1, ('Data Entry'!F11-'Data Entry'!F10)*1440+1440, ('Data Entry'!F11-'Data Entry'!F10)*1440), "#") & " min"
F9=TEXT( IF((('Data Entry'!F11-'Data Entry'!F6)*1440)<1, ('Data Entry'!F11-'Data Entry'!F6)*1440+1440, ('Data Entry'!F11-'Data Entry'!F6)*1440), "#") & " min"
G1='Data Entry'!G1
G2='Data Entry'!G2
G3='Data Entry'!G3
G4=TEXT(IF((('Data Entry'!G6-'Data Entry'!G5)*1440)<1, ('Data Entry'!G6-'Data Entry'!G5)*1440+1440, ('Data Entry'!G6-'Data Entry'!G5)*1440), "#") & " min"
G5=TEXT( IF((('Data Entry'!G7-'Data Entry'!G6)*1440)<1, ('Data Entry'!G7-'Data Entry'!G6)*1440+1440, ('Data Entry'!G7-'Data Entry'!G6)*1440), "#") & " min"
G6=TEXT(IF((('Data Entry'!G9-'Data Entry'!G7)*1440)<1,('Data Entry'!G9-'Data Entry'!G7)*1440+1440,('Data Entry'!G9-'Data Entry'!G7)*1440),"#")&" min"
G7=TEXT(IF((('Data Entry'!G11-'Data Entry'!G9)*1440)<1,('Data Entry'!G11-'Data Entry'!G9)*1440+1440,('Data Entry'!G11-'Data Entry'!G9)*1440),"#")&" min"
G8=TEXT( IF((('Data Entry'!G11-'Data Entry'!G10)*1440)<1, ('Data Entry'!G11-'Data Entry'!G10)*1440+1440, ('Data Entry'!G11-'Data Entry'!G10)*1440), "#") & " min"
G9=TEXT( IF((('Data Entry'!G11-'Data Entry'!G6)*1440)<1, ('Data Entry'!G11-'Data Entry'!G6)*1440+1440, ('Data Entry'!G11-'Data Entry'!G6)*1440), "#") & " min"

Ground Mission Sheet:
Excel Workbook
ABCDEFG
1Julian Date123456
2GroupABCDEF
3# Passengers123456
4Request generation to Launch Time29 min30 min30 min30 min30 min30 min
5Launch time to Destination time10 min70 min10 min70 min10 min70 min
6Total Mission Time39 min100 min40 min100 min40 min100 min
Ground
Excel 2007
Cell Formulas
RangeFormula
B1='Data Entry'!B1
B2='Data Entry'!B2
B3='Data Entry'!B3
B4=TEXT(IF((('Data Entry'!B10-'Data Entry'!B5)*1440)<1, ('Data Entry'!B10-'Data Entry'!B5)*1440+1440, ('Data Entry'!B10-'Data Entry'!B5)*1440), "#") & " min"
B5=TEXT( IF((('Data Entry'!B11-'Data Entry'!B10)*1440)<1, ('Data Entry'!B11-'Data Entry'!B10)*1440+1440, ('Data Entry'!B11-'Data Entry'!B10)*1440), "#") & " min"
B6=TEXT( IF((('Data Entry'!B11-'Data Entry'!B5)*1440)<1, ('Data Entry'!B11-'Data Entry'!B5)*1440+1440, ('Data Entry'!B11-'Data Entry'!B5)*1440), "#") & " min"
C1='Data Entry'!C1
C2='Data Entry'!C2
C3='Data Entry'!C3
C4=TEXT(IF((('Data Entry'!C10-'Data Entry'!C5)*1440)<1, ('Data Entry'!C10-'Data Entry'!C5)*1440+1440, ('Data Entry'!C10-'Data Entry'!C5)*1440), "#") & " min"
C5=TEXT( IF((('Data Entry'!C11-'Data Entry'!C10)*1440)<1, ('Data Entry'!C11-'Data Entry'!C10)*1440+1440, ('Data Entry'!C11-'Data Entry'!C10)*1440), "#") & " min"
C6=TEXT( IF((('Data Entry'!C11-'Data Entry'!C5)*1440)<1, ('Data Entry'!C11-'Data Entry'!C5)*1440+1440, ('Data Entry'!C11-'Data Entry'!C5)*1440), "#") & " min"
D1='Data Entry'!D1
D2='Data Entry'!D2
D3='Data Entry'!D3
D4=TEXT(IF((('Data Entry'!D10-'Data Entry'!D5)*1440)<1, ('Data Entry'!D10-'Data Entry'!D5)*1440+1440, ('Data Entry'!D10-'Data Entry'!D5)*1440), "#") & " min"
D5=TEXT( IF((('Data Entry'!D11-'Data Entry'!D10)*1440)<1, ('Data Entry'!D11-'Data Entry'!D10)*1440+1440, ('Data Entry'!D11-'Data Entry'!D10)*1440), "#") & " min"
D6=TEXT( IF((('Data Entry'!D11-'Data Entry'!D5)*1440)<1, ('Data Entry'!D11-'Data Entry'!D5)*1440+1440, ('Data Entry'!D11-'Data Entry'!D5)*1440), "#") & " min"
E1='Data Entry'!E1
E2='Data Entry'!E2
E3='Data Entry'!E3
E4=TEXT(IF((('Data Entry'!E10-'Data Entry'!E5)*1440)<1, ('Data Entry'!E10-'Data Entry'!E5)*1440+1440, ('Data Entry'!E10-'Data Entry'!E5)*1440), "#") & " min"
E5=TEXT( IF((('Data Entry'!E11-'Data Entry'!E10)*1440)<1, ('Data Entry'!E11-'Data Entry'!E10)*1440+1440, ('Data Entry'!E11-'Data Entry'!E10)*1440), "#") & " min"
E6=TEXT( IF((('Data Entry'!E11-'Data Entry'!E5)*1440)<1, ('Data Entry'!E11-'Data Entry'!E5)*1440+1440, ('Data Entry'!E11-'Data Entry'!E5)*1440), "#") & " min"
F1='Data Entry'!F1
F2='Data Entry'!F2
F3='Data Entry'!F3
F4=TEXT(IF((('Data Entry'!F10-'Data Entry'!F5)*1440)<1, ('Data Entry'!F10-'Data Entry'!F5)*1440+1440, ('Data Entry'!F10-'Data Entry'!F5)*1440), "#") & " min"
F5=TEXT( IF((('Data Entry'!F11-'Data Entry'!F10)*1440)<1, ('Data Entry'!F11-'Data Entry'!F10)*1440+1440, ('Data Entry'!F11-'Data Entry'!F10)*1440), "#") & " min"
F6=TEXT( IF((('Data Entry'!F11-'Data Entry'!F5)*1440)<1, ('Data Entry'!F11-'Data Entry'!F5)*1440+1440, ('Data Entry'!F11-'Data Entry'!F5)*1440), "#") & " min"
G1='Data Entry'!G1
G2='Data Entry'!G2
G3='Data Entry'!G3
G4=TEXT(IF((('Data Entry'!G10-'Data Entry'!G5)*1440)<1, ('Data Entry'!G10-'Data Entry'!G5)*1440+1440, ('Data Entry'!G10-'Data Entry'!G5)*1440), "#") & " min"
G5=TEXT( IF((('Data Entry'!G11-'Data Entry'!G10)*1440)<1, ('Data Entry'!G11-'Data Entry'!G10)*1440+1440, ('Data Entry'!G11-'Data Entry'!G10)*1440), "#") & " min"
G6=TEXT( IF((('Data Entry'!G11-'Data Entry'!G5)*1440)<1, ('Data Entry'!G11-'Data Entry'!G5)*1440+1440, ('Data Entry'!G11-'Data Entry'!G5)*1440), "#") & " min"

What I would like is to check Data sheet Row 4 for each clomun for whether it was a ground or air mission and then populate that sheet with the calulations that I have currently compiled. Rather than my current problem where every mission is on both sheets.

I appreciate your help and assistance.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
You must log in or register to reply here.

Similar threads

S
  • Solved
Calculating minutes to midnight
Replies
4
Views
486
FormR
FormR
willow1985
  • Question Question
Macro to Copy Folder and all its contents (including subfolders) to a new location based on a list
Replies
6
Views
215
Tupacandres
T
M
  • Question Question
Copied sheet code to new file and lost some functionality....not sure how/why
Replies
5
Views
118
MeaclH
M
S
  • Question Question
Formula for Equity Vesting
Replies
6
Views
1K
Dave Patton
D
LearningByDoing
  • Solved
No correct calculation with Nested IF + AND Function
Replies
4
Views
120
Alex Blakenburg
A

Forum statistics

Threads
1,224,607
Messages
6,179,871
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top