0

I'm trying to do some organizing for my characters in a video game I play. I'm not sure if/how I can do what I'm trying to do. Essentially, I'd like the "Max Crew Skills" cell range in the bottom-right of the attached picture to automatically update based on what I enter in the "Crew Skills" range. Hopefully the image below makes it clear. I hand-input the data as it is now but I'm wondering if there's a way to get it to dynamically update.

If, say, I fill in row 4 with new information (Skill 1 = "Armormech" and Level = "300") I'd like that to be reflected in the "Max Crew Skills" range. I can think about how one might do this manually (stack columns A+G+H, A+I+J, A+K+L together; sort by skill name and skill number; keep the combination with the highest number; print the skill/number/character name) but if I can get away with just some filters in columns P/Q that would be preferable.

Thanks!

Table

Follow-up: I was able to accomplish this via a lot of manual manipulation on a second sheet. I'd still love to know if there's a cleaner solution! The sheet can be found here, feel free to take a look!

1 Answer 1

1

Please, see test sheet

Column O contains following formula

=IFERROR(ARRAYFORMULA(
 QUERY(
  {$A$2:$A,$G$2:$H;
   $A$2:$A,$I$2:$J;
   $A$2:$A,$K$2:$L},
 "SELECT Col3, Col1 
  WHERE Col1 IS NOT NULL
  AND Col2 ='"&N2&"'
  ORDER BY Col3 Desc
  LIMIT 1",0)),"No Character")

where we first combine all data into table form and then using QUERY() we return MAX level of skill and Character name based on Skill name in Column N

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.