Reference cell(DATE) in worksheet based on a group of dates in another worksheet

Hopefully I can explain this better here, then in the title.

I have a workbook that I use to track my tickets that I work on. I have a form that I enter the information, and it populates that information to the appropriate cells. The first Column is the date I did each ticket. Some days I will more tickets the other days. As well I have a spot for the mileage that I drive each day.

On the second sheet (calculations), is what I have to use to submit my mileage expenses at the end of each month. It is standard for everyone at work, and the format can't change.

What I am trying to do is have the date on the calculations sheet automatically populate based on the date on the Tickets sheet. As well, the mileage from the Ticket sheet, to the calculations sheet by the appropriate date.

My workbook is attached. I have been searching for awhile, and haven't been able to find anything that comes close to working.

Hi

Try this
tickets!F3: =IF(AND(COUNTIF($A$3:A3,A3)=1,A3>=EOMONTH(TODAY(),-1)+1,A3<=EOMONTH(TODAY(),0)),ROW(),"")
Copy down as required.
Calculations!C9: =INDEX(Tickets!A:A,SMALL(Tickets!E:F,ROW()-8))
Calculations!D9: =SUMPRODUCT(--(Tickets!$A$3:$A$10=Calculations!C9),--(Tickets!$E$3:$E$10<=100),Tickets!$E$3:$E$10)
Calculations!E9: =SUMPRODUCT(--(Tickets!$A$3:$A$10=Calculations!C9),--(Tickets!$E$3:$E$10>100),Tickets!$E$3:$E$10)

If you don't have the eomonth function, then select the standard excel analysis tookpak addin.

HTH

rylo

Once I read the cell reference "F3" correctly, and put the formula in F3, and NOT A3, it seems to be working as intended.

Just curious if there is anyway to put this in a vba script? If not, how can I protect that cell from being erased if I have delete those rows? If I try to protect Column F it says that it can't do it because I have merged cells.

Also, on the calculations worksheet it has NUM through all the the cells without data, is there away to hide all this?

I attached the workbook so it can be viewed and see what I mean.

Ya, this isn't working like I was expecting.

Hopefully, I'll find something that works.

Hi

To remove the #NUM problem, change the formula in Calculations:C9 to be
=IFERROR(INDEX(Tickets!A:A,SMALL(Tickets!F:F,ROW()-8)),"")

Also, you will have to remove the undated entry from the Tickets sheet.

You could use a macro to put in the relevant formulas, then either leave them or convert the results to a value.

Re your comment in #4, exactly what are you expecting if this solution doesn't suit?

rylo