Ongoing Custom Lookup Function Questions

Thought I would dedicate a thread to this project instead of constantly opening new threads as this project evolves.

This is a report I need to generate daily, and it pulls the data from 4 sheets (FreshDesk, CallTek, Kentrone & CRM). I realize this is a lot to ask from the community but if we can pick at each function one by one I would be very grateful.

I manually copy and paste the data from these 4 different ticketing systems into these 4 sheets...but they then require manual sorting to get the report sheet to display correctly. The ultimate goal here is an input-output workbook, as in copy the data into the 4 sheets and everything is automated generating the report without any further intervention.


FreshDesk (sheet)
First I paste the data into columns A-C which populates the total open and pending ticket count on the report. Then I sort the list A-Z based on column B (to get the open tickets consolidated) --> copy that block over to columns E-G for which the report automatically pulls the oldest date from column G in order to display the oldest open ticket.

I feel this can be done better without the need to sort and copy open tickets to a new column just for the oldest open date. There must be a way to VLOOKUP column B then find the oldest date based on the rows with an open status.


CallTek (sheet)
First I paste the data into columns A-N which populates the open tickets by their Tier on the report. Then similar to FreshDesk I copy columns D-J for each Tier to the right, between the grey columns which the report automatically pulls the value from P2, X2, AF2 and AN2. Using a formula on the reports sheet it pulls the value from these 4 cells and also converts the "# days ago" into a uniform "##/##/##" date format. However with this sheet it requires me to manually sort columns P2, X2, AF2 and AN2 with a custom sort in order to bring the oldest tickets up to row 2. Would be nice to just paste the data into the CallTek sheet and have it automagically pull the ticket counts per Tier along with the oldest ticket value for the corresponding Tier similar to the way E3 does on the report sheet.


Kentrone (sheet)
Again very similar to CallTek sheet moving everything to the right based on its Tier in column J....luckily these are always in the order of oldest to newest so no manual sorting is required for columns M, R, and W...but would love to avoid moving everything over to the right!


CRM (sheet)
This is already pretty good minus the oldest date it already pulls the ticket count on the report sheet for each person, but if possible would be awesome if a function would grab the oldest date from column F based on each name in column D. The weird part is when I pull this data the date column includes the person who assigned the ticket and I do not need this in the date column on the report sheet (ex: ## Month Year Time, Name --> just need ## Month and Year).