Ongoing Custom Lookup Function Questions

[PinkFloydEffect]

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).

[AliGW]

You are allowed one thread per issue here and each thread title needs to explicitly address the issue you need solving, and not be generic.

If you require further help with previous queries, then bump the thread in question by posting to it.

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[AliGW]

From your message, you understand that it's one thread per issue, so I am now closing this multi-issue thread. Thanks.