Hi,
I'm looking for a little help. Sorry it's a long post. Either Excel 2016 or Excel 365 are used depending on PC used to access document.
I have been given a spreadsheet (copied from another spreadsheet monthly) that contains every occasion someone has undertaken overtime in the previous 6 months.
eg
Mr Smith 22/1/22 6 hours,
Mr Jones 23/1/22 3 hours,
Mr Smith 24/1/22 1 hour.
With the aim of creating a list of staff and their total for overtime undertaken in the last 6 months.
I have generated a SORT(UNIQUE) list so each name only appears once, therefore pre-populates my base list. I have also SUM hours worked, so that's taken care of. The next stage is to place their current Station or Dept (both are locations) next to their name.
I've have inherited and currently manage a document that lists all Workers (of 3 ranks) and their permanent posting, as well as a separate column listing their temporary promotion posting (with in the 3 ranks). Senior Managers are on a separate tab. I update this spreadsheet on a daily basis.
My New Spreadsheet is literally just:
Name Current Location
Mr Jones CRG
Mr Smith 12
Mr Young
I have already INDEX and MATCH the permanent posting for Workers and Senior Managers, but am stuck on how to then search for Workers Temp posting. The formula I am using so far is:
=IFERROR(INDEX('http://fireplace/servicedelivery/crg/crg dept/CRG ESTABLISHMENT/CRG ESTABLISHMENT DASHBOARD.xlsx'!Table2[Station],MATCH(B2,'http://fireplace/servicedelivery/crg/crg dept/CRG ESTABLISHMENT/CRG ESTABLISHMENT DASHBOARD.xlsx'!Table2[Name],0)),(INDEX('http://fireplace/servicedelivery/crg/crg dept/CRG ESTABLISHMENT/CRG ESTABLISHMENT DASHBOARD.xlsx'!Table1[Dept],MATCH(B2,'http://fireplace/servicedelivery/crg/crg dept/CRG ESTABLISHMENT/CRG ESTABLISHMENT DASHBOARD.xlsx'!Table1[Name]))))
I'm aware that the formula looks messy due to the spreadsheet being held on a intranet web page.
I wouldn't need the IFERROR part when the formula works as everyone has a permanent location.
Ideally I would like to search and input locations stopping if a location is found, in the following order, :
Senior Managers Dept,
Temporary Promotion Station,
Permanent Station.
eg
Mr Jones is in CRG.
Mr Smith is temporary promoted for will have 12 as his location,
When Mr Smith revokes back to his permanent position and I delete his L, M & N data, it will then show his location as 20.
When I search for Mr Wilson:
it will search the Senior Manager list for the Mr Wilson,
if it finds an entry copy it next to their name (in my new spreadsheet) and stops,
if it finds nothing then search the Station No (column L) for the Mr Wilson,
if it finds an entry copy it next to their name (in my new spreadsheet) and stops,
if it finds nothing then search the Station (column D) for the Mr Wilson,
when it finds Mr Wilson it copies it next to their name (in my new spreadsheet) and stops.
I get alternatively it could work in the reverse order and just replace the Permanent posting with the Temp posting.
I hope this makes sense. Any help would be most gratefully received.
Kinds Regards,
Dave
I'm looking for a little help. Sorry it's a long post. Either Excel 2016 or Excel 365 are used depending on PC used to access document.
I have been given a spreadsheet (copied from another spreadsheet monthly) that contains every occasion someone has undertaken overtime in the previous 6 months.
eg
Mr Smith 22/1/22 6 hours,
Mr Jones 23/1/22 3 hours,
Mr Smith 24/1/22 1 hour.
With the aim of creating a list of staff and their total for overtime undertaken in the last 6 months.
I have generated a SORT(UNIQUE) list so each name only appears once, therefore pre-populates my base list. I have also SUM hours worked, so that's taken care of. The next stage is to place their current Station or Dept (both are locations) next to their name.
I've have inherited and currently manage a document that lists all Workers (of 3 ranks) and their permanent posting, as well as a separate column listing their temporary promotion posting (with in the 3 ranks). Senior Managers are on a separate tab. I update this spreadsheet on a daily basis.
My New Spreadsheet is literally just:
Name Current Location
Mr Jones CRG
Mr Smith 12
Mr Young
I have already INDEX and MATCH the permanent posting for Workers and Senior Managers, but am stuck on how to then search for Workers Temp posting. The formula I am using so far is:
=IFERROR(INDEX('http://fireplace/servicedelivery/crg/crg dept/CRG ESTABLISHMENT/CRG ESTABLISHMENT DASHBOARD.xlsx'!Table2[Station],MATCH(B2,'http://fireplace/servicedelivery/crg/crg dept/CRG ESTABLISHMENT/CRG ESTABLISHMENT DASHBOARD.xlsx'!Table2[Name],0)),(INDEX('http://fireplace/servicedelivery/crg/crg dept/CRG ESTABLISHMENT/CRG ESTABLISHMENT DASHBOARD.xlsx'!Table1[Dept],MATCH(B2,'http://fireplace/servicedelivery/crg/crg dept/CRG ESTABLISHMENT/CRG ESTABLISHMENT DASHBOARD.xlsx'!Table1[Name]))))
I'm aware that the formula looks messy due to the spreadsheet being held on a intranet web page.
I wouldn't need the IFERROR part when the formula works as everyone has a permanent location.
Ideally I would like to search and input locations stopping if a location is found, in the following order, :
Senior Managers Dept,
Temporary Promotion Station,
Permanent Station.
eg
Mr Jones is in CRG.
Mr Smith is temporary promoted for will have 12 as his location,
When Mr Smith revokes back to his permanent position and I delete his L, M & N data, it will then show his location as 20.
When I search for Mr Wilson:
it will search the Senior Manager list for the Mr Wilson,
if it finds an entry copy it next to their name (in my new spreadsheet) and stops,
if it finds nothing then search the Station No (column L) for the Mr Wilson,
if it finds an entry copy it next to their name (in my new spreadsheet) and stops,
if it finds nothing then search the Station (column D) for the Mr Wilson,
when it finds Mr Wilson it copies it next to their name (in my new spreadsheet) and stops.
I get alternatively it could work in the reverse order and just replace the Permanent posting with the Temp posting.
I hope this makes sense. Any help would be most gratefully received.
Kinds Regards,
Dave
