What to show blank when nothing there

R

Robert Wyatt

Board Regular
Joined
Jul 15, 2012
Messages
84
Office Version
  1. 2019
Platform
  1. Windows
I trying to show a blank cell when there is no information I using a formula in F12:F30 but it still adds total all the way down to F30 even when there is nothing in cells A17:E17. Please help
Book1
J
12
Sheet1
 
Sort by date Sort by votes
OK hopefully it works this time if not I'm sorry for all the trouble and well not post here any more.
2024 Blank Time Keeper.xlsm
BCDEFGHIJ
9
10  
11BeginningEndingPay Date
12
1302/04/200815#N/A06/09/20148
14Hire In DateYearsPeriodTransfer DateYears
15
16DayDateDescriptionTime InTime OutWorkedRegularOvertimePTO
18  ----
19  ----
20  ----
21  ----
22  ----
23  ----
24  ----
25TOTAL HOURS: ----
26
27DayDateDescriptionTime InTime OutWorkedRegularOvertimePTO
29  ----
30  ----
31  ----
32  ----
33  ----
34  ----
35  ----
36TOTAL HOURS: ----
37
38TOTAL HOURS-
39
40DescriptionRateHoursThis PeriodSocial Security Information
41Regular $ 11.75--Allowed Monthly Gross$ 1,770.00
42Stand In$ 13.75--Estimated Monthly Gross$ -
43PTO$ 11.75--Difference$ -
44Overtime$ 17.63--Allowed Annually Gross$21,240.00
45Tip Appliance$ 0.98--Estimated Annually Gross$ -
46Trainning$ 11.75--Difference$ -
47TOTAL GROSS WAGES:$ -YTD Wages, Tips$ -
Payroll Keeper
Cell Formulas
RangeFormula
F10F10=IF(C10="","",C10+13)
I10I10=IF(C10="","",C10+20)
D13,I13D13=IF(TODAY()="","",(DATEDIF(C13,TODAY(),"Y")))
F13F13=INDEX(PayDate!B1:B28,MATCH($I$10,PayDate!$A$1:$A$28,0))
B29:B35,B18:B24B18=IF(C18="","",C18)
C18C18=IF(C10="","",C10)
C19C19=IF(C10="","",C10+1)
C20C20=IF(C10="","",C10+2)
C21C21=IF(C10="","",C10+3)
C22C22=IF(C10="","",C10+4)
C23C23=IF(C10="","",C10+5)
C24C24=IF(C10="","",C10+6)
G29:G35,G18:G24G18=ROUND(IF((OR(E18="",F18="")),0,IF((F18<E18),((F18-E18)*24)+24,(F18-E18)*24)),2)
H29:H35,H18:H24H18=G18-I18
I18I18=ROUND(MAX(IF($R$14,MAX(0,SUM(H17:H$17)+G18-$Q$16),0),IF($R$14,IF(G18>$Q$16,G18-$Q$16,0),0)),2)
I19:I24I19=ROUND(MAX(IF($R$14,MAX(0,SUM(H$17:H18)+G19-$Q$16),0),IF($R$14,IF(G19>$Q$16,G19-$Q$16,0),0)),2)
G36:J36,G25:J25G25=SUM(G18:G24)
C29C29=IF(C10="","",C10+7)
C30C30=IF(C10="","",C10+8)
C31C31=IF(C10="","",C10+9)
C32C32=IF(C10="","",C10+10)
C33C33=IF(C10="","",C10+11)
C34C34=IF(C10="","",C10+12)
C35C35=IF(C10="","",C10+13)
I29:I35I29=ROUND(MAX(IF($R$14,MAX(0,SUM(H$28:H28)+G29-$Q$16),0),IF($R$14,IF(G29>$Q$16,G29-$Q$16,0),0)),2)
D38D38=SUMIFS(H18:H35,D18:D35,"Regular")+SUMIFS(H18:H35,D18:D35,"Stand In")+SUMIFS(H18:H35,D18:D35,"Trainning")+SUMIFS(J18:J35,D18:D35,"PTO")+SUMIFS(J18:J35,D18:D35,"FMLA")
J41J41=ROUND(SUM(J44/12),2)
J42J42=ROUND(SUM(F47*2),2)
J43,J46J43=IF(OR(J42=0,J41=0),0,ROUND(IF(J42>J41,0,J41-J42),+ROUND(IF(J42<J41,0,J41-J42),2)))
E41E41=SUMIFS(H18:H35,D18:D35,"Regular")
E42E42=SUMIFS(H18:H35,D18:D35,"Stand in")
E43E43=SUMIFS(J18:J35,D18:D35,"PTO")+SUMIFS(J18:J35,D18:D35,"Vacation")+SUMIFS(J18:J35,D18:D35,"FMLA")
E44E44=SUMIFS(I18:I35,D18:D35,"Overtime")
E45E45=SUMIFS(H18:H35,D18:D35,"Regular")
E46E46=SUMIFS(H18:H35,D18:D35,"Trainning")
F41:F45F41=ROUND(D41*E41,2)
F46F46=ROUND(SUM(D46*E46),2)
F47F47=SUM(F41:F46)
J45J45=ROUND(SUM(F47*26),2)
J47J47=ROUND(SUM(YTD!W4),2)
Cells with Data Validation
CellAllowCriteria
D18:D24List-,Regular,Stand In,Off,PTO,Vactaion,FMLA,Trainning
D29:D35List-,Regular,Stand In,Off,PTO,Vactaion,FMLA,Trainning
J18:J24List0,1,2,3,4,5,6,7,8
J29:J35List0,1,2,3,4,5,6,7,8
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
second attempt hopefully it works if not I want post anymore. So here it goes It is F13 I am asking about Index formula if there is any way to leave it back until I enter a date in I10.

2024 Blank Time Keeper.xlsm
BCDEFGHIJ
9
10  
11BeginningEndingPay Date
12
1302/04/200815#N/A06/09/20148
14Hire In DateYearsPeriodTransfer DateYears
15
16DayDateDescriptionTime InTime OutWorkedRegularOvertimePTO
18  ----
19  ----
20  ----
21  ----
22  ----
23  ----
24  ----
25TOTAL HOURS: ----
26
27DayDateDescriptionTime InTime OutWorkedRegularOvertimePTO
29  ----
30  ----
31  ----
32  ----
33  ----
34  ----
35  ----
36TOTAL HOURS: ----
37
38TOTAL HOURS-
39
40DescriptionRateHoursThis PeriodSocial Security Information
41Regular $ 11.75--Allowed Monthly Gross$ 1,770.00
42Stand In$ 13.75--Estimated Monthly Gross$ -
43PTO$ 11.75--Difference$ -
44Overtime$ 17.63--Allowed Annually Gross$21,240.00
45Tip Appliance$ 0.98--Estimated Annually Gross$ -
46Trainning$ 11.75--Difference$ -
47TOTAL GROSS WAGES:$ -YTD Wages, Tips$ -
Payroll Keeper
Cell Formulas
RangeFormula
F10F10=IF(C10="","",C10+13)
I10I10=IF(C10="","",C10+20)
D13,I13D13=IF(TODAY()="","",(DATEDIF(C13,TODAY(),"Y")))
F13F13=INDEX(PayDate!B1:B28,MATCH($I$10,PayDate!$A$1:$A$28,0))
B29:B35,B18:B24B18=IF(C18="","",C18)
C18C18=IF(C10="","",C10)
C19C19=IF(C10="","",C10+1)
C20C20=IF(C10="","",C10+2)
C21C21=IF(C10="","",C10+3)
C22C22=IF(C10="","",C10+4)
C23C23=IF(C10="","",C10+5)
C24C24=IF(C10="","",C10+6)
G29:G35,G18:G24G18=ROUND(IF((OR(E18="",F18="")),0,IF((F18<E18),((F18-E18)*24)+24,(F18-E18)*24)),2)
H29:H35,H18:H24H18=G18-I18
I18I18=ROUND(MAX(IF($R$14,MAX(0,SUM(H17:H$17)+G18-$Q$16),0),IF($R$14,IF(G18>$Q$16,G18-$Q$16,0),0)),2)
I19:I24I19=ROUND(MAX(IF($R$14,MAX(0,SUM(H$17:H18)+G19-$Q$16),0),IF($R$14,IF(G19>$Q$16,G19-$Q$16,0),0)),2)
G36:J36,G25:J25G25=SUM(G18:G24)
C29C29=IF(C10="","",C10+7)
C30C30=IF(C10="","",C10+8)
C31C31=IF(C10="","",C10+9)
C32C32=IF(C10="","",C10+10)
C33C33=IF(C10="","",C10+11)
C34C34=IF(C10="","",C10+12)
C35C35=IF(C10="","",C10+13)
I29:I35I29=ROUND(MAX(IF($R$14,MAX(0,SUM(H$28:H28)+G29-$Q$16),0),IF($R$14,IF(G29>$Q$16,G29-$Q$16,0),0)),2)
D38D38=SUMIFS(H18:H35,D18:D35,"Regular")+SUMIFS(H18:H35,D18:D35,"Stand In")+SUMIFS(H18:H35,D18:D35,"Trainning")+SUMIFS(J18:J35,D18:D35,"PTO")+SUMIFS(J18:J35,D18:D35,"FMLA")
J41J41=ROUND(SUM(J44/12),2)
J42J42=ROUND(SUM(F47*2),2)
J43,J46J43=IF(OR(J42=0,J41=0),0,ROUND(IF(J42>J41,0,J41-J42),+ROUND(IF(J42<J41,0,J41-J42),2)))
E41E41=SUMIFS(H18:H35,D18:D35,"Regular")
E42E42=SUMIFS(H18:H35,D18:D35,"Stand in")
E43E43=SUMIFS(J18:J35,D18:D35,"PTO")+SUMIFS(J18:J35,D18:D35,"Vacation")+SUMIFS(J18:J35,D18:D35,"FMLA")
E44E44=SUMIFS(I18:I35,D18:D35,"Overtime")
E45E45=SUMIFS(H18:H35,D18:D35,"Regular")
E46E46=SUMIFS(H18:H35,D18:D35,"Trainning")
F41:F45F41=ROUND(D41*E41,2)
F46F46=ROUND(SUM(D46*E46),2)
F47F47=SUM(F41:F46)
J45J45=ROUND(SUM(F47*26),2)
J47J47=ROUND(SUM(YTD!W4),2)
Cells with Data Validation
CellAllowCriteria
D18:D24List-,Regular,Stand In,Off,PTO,Vactaion,FMLA,Trainning
D29:D35List-,Regular,Stand In,Off,PTO,Vactaion,FMLA,Trainning
J18:J24List0,1,2,3,4,5,6,7,8
J29:J35List0,1,2,3,4,5,6,7,8
 
Upvote 0
How about
Excel Formula: 
=IF(I10="","",INDEX(PayDate!B1:B28,MATCH($I$10,PayDate!$A$1:$A$28,0)))
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
AverageIfs with range criterion... can't get the right answer?
Replies
9
Views
411
Fluff
Fluff
K
  • Question
Trouble with moving average test
Replies
3
Views
262
Kauri
K
N
  • Solved
VBA/Macro Help. Hopefully easy but I have NO idea what I am doing.
Replies
4
Views
274
ned lud
N
E
  • Question
If Statement Formula Syntax
Replies
1
Views
259
DanteAmor
DanteAmor
S
  • Question
Multiselect drop down using VBA code is not totaling correctly
Replies
1
Views
93
Fluff
Fluff

Forum statistics

Threads
1,214,912
Messages
6,122,204
Members
449,072
Latest member
DW Draft

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