Hi everyone, I've been stuck on this for hours and can't seem to get it to work, though I know what I'm trying to do is a bit complicated. Wish I could post pictures .
First of all I'm working from 2 sheets in the same workbook:
Randomizer and Lyrics
Lyrics sheet has 3 columns:
A2:A14 = Theme Tags
B2:B14 = Category Tags
C2:C14 = Lyrics
Each Lyric Line can have one or more theme /category tags associated with it
For Example:
A7 could look like "Theme1, Theme2" meaning that the lyric in that row fits both themes.
On the Randomizer Sheet, I have a theme drop down where the user selects the theme. Only one theme can be selected at any given time. There are 4 lines generated from the lyric based on the theme selected.
The rules for each line:
1) Selecting the theme from the drop down finds a random line that fits the theme and puts it in line 1
2)It then generates a random line that fits the theme(same theme tag) and fits the category (same category tag) of line 1 and is not line 1 and puts it in line 2
3)It then generates a random line that fits the theme(same theme tag) and is not line 1 or line 2 puts it in line 3 (doesn't care about category)
4)It then generates a random line that fits the theme(same theme tag) and fits the category(same category tag) of line 3 and is not line 1 or line 2 or line 3 and puts it in line 4
Basically I'm trying to create a poetry generator where the user can set the theme and it will generate lines that can rhyme the first 2 and second 2 lines (AABB). The Category will later become phonetic ending syllables. But I'm trying to just get the excel sheet working before filling it with content.
What I have for Line 1 cell in the randomizer is:
=IF(ROWS(G$11:G11)<=$I$1,INDEX(Lyrics!C2:C14,SMALL(IF(ISNUMBER(SEARCH("*"&Criteria&"*",Lyrics!A2:A14)),ROW(Lyrics!A2:A14)-ROW(Lyrics!A2)+1),ROWS(G$11:G11))),"")
Important info:
G11 = Line 1
G12 = Line 2
G13 = Line 3
G14 = Line 4
Criteria is the theme dropdown cell in the randomizer
I1 =COUNTIF(Lyrics!A2:A14,"*"&Criteria&"*")
Currently it pretty easily detects the theme, even when a line could be tagged for multiple. However the randomization is proving to be very difficult and not really sure where/how to do the category searching.
I realize this may be hard to debug without seeing the file. It is not for work or anything so I can freely send it to anyone if you need to take a closer look.
Thanks
Bookmarks