SpreadEveryone: The Fetchland Excel wire

98 watchers
ChrisHB
ChrisHB
Aug 2021
9:55am, 30 Aug 2021
21,144 posts
  •  
  • 0
ChrisHB
about 20 dates, 10 roles and 20 people.

That's the sort of solution I was looking to improve on, but it is better than nothing. A weakness is that if I mis-spell someone's name, all is in vain.
um
um
Aug 2021
10:02am, 30 Aug 2021
5,091 posts
  •  
  • 0
um
You can also replicate the job role below the names, then set up a fairly complex
HLOOKUP - if Libre has that as the same as Excel.

And I'd recommend a validation list of names so you can enter from a drop down list and remove the mistype threat
um
um
Aug 2021
10:03am, 30 Aug 2021
5,092 posts
  •  
  • 0
um
um
um
Aug 2021
10:12am, 30 Aug 2021
5,093 posts
  •  
  • 0
um
If that's of interest, then the HLookup values need to be set in row 15 then dragged down each column.
eg going across row 15

P15=HLOOKUP($O15,$P$2:$AB$11,10,0)
Q15=HLOOKUP($O15,$P$3:$AB$11,9,0)
R15=HLOOKUP($O15,$P$4:$AB$11,8,0)
decreasing the array start row and offset as you go across

Then with the $settings fixed, you can drag each cell down to autopopulate the new grid
P16=HLOOKUP($O16,$P$2:$AB$11,10,0)
P17=HLOOKUP($O17,$P$2:$AB$11,10,0)
um
um
Aug 2021
10:13am, 30 Aug 2021
5,094 posts
  •  
  • 0
um
(as opposed to some on this thread, I do like V & H lookups!)
um
um
Aug 2021
10:39am, 30 Aug 2021
5,095 posts
  •  
  • 0
um
Although if you start using this approach, I'd also recommend
- different sheets for your source and the transposed outcome
- allowing a long list of dates in the source, so you don't have to re-edit it as you extend the rota
- using the names in the transpose sheet as the master validation, also with scope for expansion
- adding some error checking so you display null instead of error values if the hlookup fails

(conditional formatting may start to look more appealing!)
GregP
GregP
Aug 2021
11:01am, 30 Aug 2021
129,025 posts
  •  
  • 0
GregP
Apropos of nothing apart from I thought this group would enjoy it. The protagonist in my novel has just ‘loaded up WordPerfect’ to write a letter…
Badger
Badger
Aug 2021
11:02am, 30 Aug 2021
14,127 posts
  •  
  • 0
Badger
The blue screen of not death?
GlennR
GlennR
Aug 2021
11:04am, 30 Aug 2021
58,382 posts
  •  
  • 0
GlennR
The blue screen of worse than death.
ChrisHB
ChrisHB
Aug 2021
11:29am, 30 Aug 2021
21,145 posts
  •  
  • 0
ChrisHB
If there's not a means in ordinary spreadsheets then I would be tempted - next time, not this time - to get to grips with the SQL interface in Google Sheets.

About This Thread

Maintained by John66
This is bugging me, I'm sure it should be much easier

A1 has 25/6/2010
B1 has 24/6/2013

So t...

Related Threads

  • excel
  • support
  • tech
  • work









Back To Top
X

Free training & racing tools for runners, cyclists, swimmers & walkers.

Fetcheveryone lets you analyse your training, find races, plot routes, chat in our forum, get advice, play games - and more! Nothing is behind a paywall, and it'll stay that way thanks to our awesome community!
Get Started
Click here to join 112,274 Fetchies!
Already a Fetchie? Sign in here