0

https://docs.google.com/spreadsheets/d/1LG7AgcumhfQ1ZV0Em_oVjdPdrLCP-5CWWrw0WV1oCnY/edit?usp=sharing

(We have to track 3 separate point totals for everyone who has participated in each game, game rewards are not formatted in a uniform manner, so we have to manually parse everything to figure out what character was rewarded which kind of points).

I Have three formulas I need populated across a massive range, repeating, but the formulas need to reference the current cell's column header.

For example:
=IF(REGEXMATCH($B4,INDIRECT("G1")),$C4,0)
=IF(REGEXMATCH($B4,INDIRECT("H1")),$D4,0)
=IF(REGEXMATCH($B4,INDIRECT("I1")),$E4,0)
And on and on...

=IF(REGEXMATCH($B4,INDIRECT("CJ1")),$C4,0)
=IF(REGEXMATCH($B4,INDIRECT("CK1")),$D4,0)
=IF(REGEXMATCH($B4,INDIRECT("CL1")),$E4,0)

(CJ1, CK1, and CL1 all contain the same info, so if I could do an =IF(REGEXMATCH($B4,INDIRECT(THISCOLUMNHEADER)),$C4,0) that would be perfect.)

Then the next set of 3 columns of cells would need to be
=IF(REGEXMATCH($B4,INDIRECT("CM1")),$C4,0)
=IF(REGEXMATCH($B4,INDIRECT("CN1")),$D4,0)
=IF(REGEXMATCH($B4,INDIRECT("CO1")),$E4,0)
again, CM1, CN1 and CO1 all contain the same header.

I need to repeat this ad nauseum across about a thousand columns, each of which the header changes every third cell. Even if I had to use some sort of step loop to increment the indirect cell reference. (Then I can just auto populate all the rows based on the formula, which are also about 1000 deep)

Improve this question
2
  • 1
    Despite your post description, what you are trying to do is not clear (at least not to me). In addition, any time I hear someone has this many columns, I have to question the overall design; there is most always a simpler approach. If you care to share a link to the sheet (or to a copy of the sheet), that would at least make the goal clearer (and may also lead to suggestions for a simpler setup).
    – Erik Tyler
    Dec 26, 2021 at 22:47
  • Thanks, yeah as a complete amateur at using sheets, we just did the best we could to try to track everyone's rewards in a large event, there is no uniformity to the postings. I added in a link to the copy of the sheet.
    – Tripsette
    Dec 27, 2021 at 0:49

1 Answer 1

Reset to default
0

Sheet was completed by hand, question is unnecessary to be answered. Not certain if/how I should delete the question, or leave it up for posterity. My apologies everyone!

Improve this answer
1
  • 3
    I'd recommend just deleting the post. What you were trying to do will not be similar to anything future users would be trying to do; and no actual solution was presented.
    – Erik Tyler
    Dec 27, 2021 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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