I have the following OFFSET equation that works like a charm:
=OFFSET('Sheet 1'!$A$2,0,0,COUNTA('Sheet 1'!$A:$A)-1)
The issue is I need to choose values from more than just sheet 1, 6 other sheets to be exact.
The following equation does not work:
=OFFSET('Sheet 1'!$A$2,0,0,COUNTA('Sheet 1'!$A:$A)-1), OFFSET('Sheet 2'!$A$2,0,0,COUNTA('Sheet 2'!$A:$A)-1)