Forum Discussion
Solved
Please help! Tangled up in a nested IF formula for DnD!
HI everyone, first time posting - I've come here for some much needed help! I've spent days creating a ginormous excel workbook that randomly generates everything I need to run my Dungeons and Drago...
- Is there a reason why this has to be a SINGLE formula?
I'd be surprised if there's any Excel manual out there that recommends writing formulas this long.
I KNOW that there are Excel manuals that warn AGAINST writing formulas this long.
It might be fun when it works, but as you've encountered here, formulas this long are opportunities for Orcs and all other kinds of creatures to get in and make mischief.
A suggestion: assuming each section (i.e., each INDEX section) works independently, and effectively, give each of them a cell of their own; and then use IF or CHOOSE based on race to go pick whichever of those cells is appropriate. Another way to describe this recommendation: use helper columns to break up a nearly unintelligible formula into discrete sections, and then extract the one you want.
I'm using the Excel App that I am able to install at home through my workplace login; I assume that means I'm likely using 365?... Not 100%. 😕
The formulas, using both IF and INDEX, keep correcting themselves, unprompted, to include an @ at the beginning. The first time it happened, there was a little pop up drawing attention to it and saying something along the lines of 'you may notice an @ sign appearing in your formulas, don't worry'.
Thank you so much for your help and having a go at solving my problem for me - I was toying around with the LOOKUP functions so for you to have given me an example is ideal. Thanks a lot!
The formulas, using both IF and INDEX, keep correcting themselves, unprompted, to include an @ at the beginning. The first time it happened, there was a little pop up drawing attention to it and saying something along the lines of 'you may notice an @ sign appearing in your formulas, don't worry'.
Thank you so much for your help and having a go at solving my problem for me - I was toying around with the LOOKUP functions so for you to have given me an example is ideal. Thanks a lot!
I have attached an Office 365 work file in the hope that it might work under your copy of Office. The mockup of your data appears as a (blue bordered) table because I used Power Query to normalise it. The output is the green-bordered table. on the next sheet.
The XLOOKUP returns the first or last instance of matched records depending on the (random) setting of 'gender'.