syntax for naming ranges and range update automatically

Do this:

Press Ctrl+F3

This will open you Name Manager window

Then click: NEW

Write some name (like Acanthacea)

and in refers to write: =OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A)-1, 1)

But also, delete all cells from A4 to A35 because they use some spaces or something so you getting wrong COUNTA for it.

Check result (yelllow box is list from that name)...

You did right with INDIRECT formula, but you need to name range with that name (No spaces).

So under Asteraceae (F1)

select range F2 to F17 and name it Asteraceae

Then in B14 you will see drop down list with those names.

If your ranges can change then you need to make OFFSET approach for EACH range as I showed you above.