Jan 27 2020 03:24 PM
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 Dragons game. I've used this nested IF formula in the workbook already and it's worked fine but I'm lost as to why it is getting tangled up here.
I appreciate that it is a lengthy one but I'm just a keen, self-taught amateur so apologies if I'm doing it in a very messy way!...
This is my formula (return spaces added to make it easier to read):
=@IF('NPC Descriptions'!K7="human",(INDEX(Names!$AW$47:$AW$48,RANDBETWEEN(1,COUNTA(Names!$AW$47:$AW$48)),1)),
IF('NPC Descriptions'!K7="dwarf",(INDEX(Names!$AW$32:$AW$33,RANDBETWEEN(1,COUNTA(Names!$AW$32:$AW$33)),1),
IF('NPC Descriptions'!K7=”elf”,(INDEX(Names!$AW$35:$AW$36,RANDBETWEEN(1,COUNTA(AND(Names!$AW$35:$AW$36)),1),
IF('NPC Descriptions'!K7=”half-elf”,(INDEX(AND(Names!$AW$35:$AW$36, Names!$AW$47:$AW$48),RANDBETWEEN(1,COUNTA(AND(Names!$AW$35:$AW$36, Names!$AW$47:$AW$48))),1),
(IF('NPC Descriptions'!K7="halfling",(INDEX(Names!$AW$38:$AW$39,RANDBETWEEN(1,COUNTA(Names!$AW$38:$AW$39)),1),
IF('NPC Descriptions'!K7="half-orc",(INDEX(AND(Names!$AX$7:$AX$8,Names!$AW$47:$AW$48),RANDBETWEEN(1,COUNTA(AND(Names!$AX$7:$AX$8,Names!$AW$47:$AW$48))),1),
IF('NPC Descriptions'!K7="tiefling",(INDEX(Names!$AW$50:$AW$51,RANDBETWEEN(1,COUNTA(Names!$AW$50:$AW$51)),1),
IF('NPC Descriptions'!K7="dragonborn",
(INDEX(Names!$AW$53:$AW$54,RANDBETWEEN(1,COUNTA(Names!$AW$53:$AW$54)),1)),(INDEX(Names!$AW$41:$AW$42,RANDBETWEEN(1,COUNTA(Names!$AW$41:$AW$42)),1))))))))
The cells point at a single column containing race-specific names from a sheet full of possible ones (if you're not familiar, that's the 'halfing', 'elf' etc logical tests). The reason I have the random pick going on in this formula is because I want it to pick 50:50 the randomly selected male or female name.
One attached screenshot shows the 'NPC Descriptions' sheet, which randomly selects a race from the race column. The other screenshot shows the 'Names' sheet, which has a table of a randomly selected male and female name for each race. The table is where the formula should be getting the names from after checking what race has been generated. But it's giving me #REF, #VALUE and FALSE results... :(
Please help!
Jan 27 2020 07:34 PM
SolutionJan 27 2020 08:12 PM
I agree with the idea of re-evaluating how your data is organized. It struck me when I took a whole 4 minutes looking at your data, that a single table might be helpful. You'll want to add columns for 'species' and perhaps other attributes, but then you'll be able to use VLOOKUPs, and other methods for extracting data (including building intermediate, or 'helper', data).
(BTW, its been over 40 years since I played D&D ... didn't even know people were still paying.)
Jan 27 2020 10:20 PM
Jan 27 2020 10:22 PM - edited Jan 28 2020 12:11 PM
Thank you for your help! Yeah, I understand DnD is experiencing quite a resurgence at the moment!
Jan 28 2020 08:05 AM
Why does your formula start with an "@"?
Are you porting a workbook from an old version of Excel 365? If so, changes are meant to be backward compatible and errors should be reported to Microsoft.
If you are using Office 365 other options become possible. The attached shows a version of data with some rearrangement. Then I have used XLOOKUP to return a name corresponding to the given species where 'gender' is a randomly chosen ±1 (search from start or search from end). A unique instance will be returned either way.
Jan 28 2020 12:16 PM
Jan 29 2020 04:06 AM
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'.
Jan 27 2020 07:34 PM
Solution