1. If you have fixed lists, I would use named ranges. For example, the list currently in column G could be named Range5 - then the validation range for the second dropdown could be set as =INDIRECT("Range"&$A$1).
If I've completely missed the point of why you used 5 columns, then shout. (I assume that, since you already appear to have a dependent dropdown, you're already sussed on how these work.)
2. The formula is in two parts. =COUNTA(OFFSET($C:$C,0,0,,$A$1-1))-$A$1+($A$1>1) starts with column C, moves it 0 rows down and 0 columns right, doesn't change its row dimensions, but expands it to $A$1-1 columns (check the help on OFFSET for more details). Then it counts the number of entries in these columns. It does this because, if the cell of interest is in the fifth column, we want all the entries in the previous four columns. We then take off ($A$1 - 1) to discount the header items. Then we deal with the case where A1 = 1 (so we want this whole first section to return 0). COUNTA(OFFSET($C:$C,0,0,,0)) = 1, so if A1 = 1, we need to -1, but not otherwise - so we can use (($A$1>1)-1). And -($A$1 - 1) + (($A$1>1)-1) = -$A$1 + ($A$1>1). So that's the whole first bit.
+COUNTA(OFFSET($C$2,0,$A$1-1,$A$3,)) just counts the number of items in the A1th column, as far down as the A3th row - pretty similar to the formula you already had.
Apologies for more than 25 words - it's tricky without knowing which bits you already understand!
3. Offset is defined as a volatile formula. This means that every time something in your sheet changes, offset will recalculate, which can make your spreadsheet slow if you have a lot of formulas doing this.
4. My husband is the fan. The first line sounds a bit like "Emma was a cornflake girl" - if you listen really hard
.