Index Match Date Range for Invoices

IanPayroll

New Member
Joined
Nov 9, 2015
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Hi,

I'm trying to create Invoices for multiple clients from a master list of daily work performed.
I would like to be able to enter a single client address on an Invoice Sheet (currently 'Sheet2!$b$3') & Date From: (Sheet2!$d$3) & Date To (Sheet2!$d$4).
I would like excel to auto populate all the information for that client for work completed in that date range. ("Date", "Description", "In", "Out", "Hours")

Sheet1! is the Master List & Sheet2! is the Invoice Sheet

I was thinking it would be a Index Match If And Formula Looking something like:

=INDEX(Sheet1!$a$2:$a$32(MATCH(Sheet2!$b$3,Sheet1!$d$2:$d$32,0)) (to get the date from Sheet 1) - Then INDEX(Sheet1!$e$2:$e$32...) (to get Description); Then INDEX(Sheet1!$b$2:$b$32...) (to get "In"); and "Then INDEX(Sheet1!$c$2:$c$32...) (to get "Out")

I would use the relevant formulas to fill hours, rate, tax and total

I've tried adding a variety of IF AND formulas but I'm stumped at how to only pull data from a date range for a single client. Can anyone offer some suggestions? I'm sure it's really easy but I'm just not able to figure it out.

Thanks in advance.
 

Attachments

  • Daily Log.JPG
    Daily Log.JPG
    117.1 KB · Views: 9
  • Invoice Example.JPG
    Invoice Example.JPG
    53.6 KB · Views: 9

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,540
Office Version
  1. 2016
Platform
  1. Windows
Hi Ianpayroll,

Try this. Here's your data in Sheet1
IanPayroll.xlsx
ABCDEF
1DateInOutClientDescriptionHours
22021 01 017:00 AM5:00 PM123 Acacia AveTask110
32021 01 027:00 AM4:30 PM2 Browns RdTask29.5
42021 01 037:30 AM5:00 PM44 Clutch StTask39.5
52021 01 040
62021 01 050
72021 01 067:00 AM5:00 PM123 Acacia AveTask610
82021 01 077:00 AM5:00 PM2 Browns RdTask710
92021 01 087:30 AM4:30 PM2 Browns RdTask89
102021 01 097:30 AM6:15 PM2 Browns RdTask910.75
112021 01 107:00 AM5:00 PM44 Clutch StTask1010
122021 01 110
132021 01 120
142021 01 137:00 AM5:00 PM123 Acacia AveTask1310
152021 01 147:30 AM4:30 PM2 Browns RdTask149
162021 01 157:00 AM5:00 PM2 Browns RdTask1510
Sheet1
Cell Formulas
RangeFormula
F2:F16F2=(C2-B2)*24


Here's your invoice page:
IanPayroll.xlsx
ABCDE
1Invoice
2
3Client Address2 Browns RdDate From:2021 01 03
4Date To:2021 01 14
5
6DateDescriptionInOutHours
72021 01 07Task77:00 AM5:00 PM10
82021 01 08Task87:30 AM4:30 PM9
92021 01 09Task97:30 AM6:15 PM10.75
102021 01 14Task147:30 AM4:30 PM9
11     
12     
13     
14     
15     
16
17Hours38.75
18Rate
19Tax
20Total
Sheet2
Cell Formulas
RangeFormula
A7:A15A7=IFERROR(INDEX(Sheet1!$A$2:$A$9999,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$9999)-ROW(Sheet1!$A$1)/((Sheet1!$D$2:$D$9999=$B$3)*(Sheet1!$A$2:$A$9999>=$D$3)*(Sheet1!$A$2:$A$9999<=$D$4)),ROW()-ROW($A$6))),"")
B7:B15B7=IFERROR(INDEX(Sheet1!$E$2:$E$9999,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$9999)-ROW(Sheet1!$A$1)/((Sheet1!$D$2:$D$9999=$B$3)*(Sheet1!$A$2:$A$9999>=$D$3)*(Sheet1!$A$2:$A$9999<=$D$4)),ROW()-ROW($A$6))),"")
C7:D15C7=IFERROR(INDEX(Sheet1!B$2:B$9999,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$9999)-ROW(Sheet1!$A$1)/((Sheet1!$D$2:$D$9999=$B$3)*(Sheet1!$A$2:$A$9999>=$D$3)*(Sheet1!$A$2:$A$9999<=$D$4)),ROW()-ROW($A$6))),"")
E7:E15E7=IFERROR(INDEX(Sheet1!$F$2:$F$9999,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$9999)-ROW(Sheet1!$A$1)/((Sheet1!$D$2:$D$9999=$B$3)*(Sheet1!$A$2:$A$9999>=$D$3)*(Sheet1!$A$2:$A$9999<=$D$4)),ROW()-ROW($A$6))),"")
E17E17=SUM(E7:E15)
 
Solution

IanPayroll

New Member
Joined
Nov 9, 2015
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
That's exactly what I was looking for. Thank you very much for taking the time to write all that out. I really appreciate it Toadstool.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,540
Office Version
  1. 2016
Platform
  1. Windows
That's exactly what I was looking for. Thank you very much for taking the time to write all that out. I really appreciate it Toadstool.
You're welcome!
Thanks for the feedback.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,065
Office Version
  1. 365
Platform
  1. Windows
Just a suggestion. Using the same concept Toadstool has used, you could simplify the majority of the formulas and reduce the amount of calculations required considerably by using a helper column.
Here I have used column F in Sheet2 (Sheet1 is identical to Toadstool's Sheet1) as the helper column and that column could be hidden once you enter the formulas.

IanPayroll.xlsm
ABCDEF
1Invoice
2
3Client Address2 Browns RdDate From:2021 01 03
4Date To:2021 01 14
5
6DateDescriptionInOutHours
72021 01 07Task77:00 AM5:00 PM107
82021 01 08Task87:30 AM4:30 PM98
92021 01 09Task97:30 AM6:15 PM10.759
102021 01 14Task147:30 AM4:30 PM914
11     0
12     0
13     0
14     0
15     0
16
17Hours38.75
18Rate
19Tax
20Total
Sheet2
Cell Formulas
RangeFormula
A7:A15A7=IF(F7,INDEX(Sheet1!$A$2:$A$9999,F7),"")
B7:B15B7=IF(F7,INDEX(Sheet1!$E$2:$E$9999,F7),"")
C7:C15C7=IF(F7,INDEX(Sheet1!B$2:B$9999,F7),"")
D7:D15D7=IF(F7,INDEX(Sheet1!C$2:C$9999,F7),"")
E7:E15E7=IF(F7,INDEX(Sheet1!$F$2:$F$9999,F7),"")
F7:F15F7=IFERROR(AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$9999)-ROW(Sheet1!$A$1)/((Sheet1!$D$2:$D$9999=$B$3)*(Sheet1!$A$2:$A$9999>=$D$3)*(Sheet1!$A$2:$A$9999<=$D$4)),ROW()-ROW($F$6)),0)
E17E17=SUM(E7:E15)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,701
Messages
5,654,818
Members
418,155
Latest member
demasisi

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
Top