Constructing a cell reference with a formula

[justinx]

In sheet1!A1, I have the text "Cat"

In sheet 2, column 5 is labeled "Cat" in E1

In sheet 1, I have complex formulas that reference the range 'sheet2'!$E$2:'sheet2'!$E$300


I already have a formula that finds the column number of Cat in sheet2 and turns it into a letter, E.

The problem is how do I replace the two E's in this reference... 'sheet2'!$E$2:'sheet2'!$E$300 ...with that formula? Is there a way I can insert it using concatenation?

[mehmetcik]

=SUM(Sheet2!E1:E300)

Becomes

=SUM(Indirect("Sheet2!" & E & "1:" & E & "300"))

=LET(E,"E",SUM(INDIRECT("Sheet2!" & E & "1:" & E & "300")))

[etaf]

you can use an INDIRECT() to convert the formulas into something that uses that address range
concatenate will join
Not sure what formula you are using to get the E

Indirect( "'sheet2'!$"& formula for E & "'$2:'sheet2'!$"& formula for E & "$300" )

But you dont need to refer to the sheet twice

you maybe able to just use ADDRESS() with a FIND "CAT" to get E