Create a Cell that Sets which Column is being Used within a Formula

[FGCguitar]

I've got a problem I can't seem to figure out...

I want to create a cell that allows me to change the value for a column I'm using within a formula.

I get that that is confusing.

For example, I put "BB" in cell A1.
Then I want to create a formula that sums from C1: the column identified in cell A1 &1
So in that case it would allow me to sum from C1:BB1

But I could update cell A1 and enter in "DX"
Which would then sum C1:DX1

I'm currently trying to use Indirect to accomplish this, but as far as I can tell, it is returning the value of the reference cell, not actually using the value as the column.

---

Then, the kicker is that I want the source of the summing to come from a different sheet, while my column reference cell is on the sheet where I want the data to output.

So really it's more like:
I'm trying to extract data into a cell on Sheet 2. This value is a sum from Sheet 1, using an adjustable reference that defines the column within the range, which is also on Sheet 2.

So something kind of like this:
Sum(Sheet1!C1:the column identified in cell A1 of Sheet 2 &1)

I can move the adjustable reference cell to Sheet 1 if that helps.
Sum(Sheet1!C1:the column identified in cell A1 of Sheet 1 &1)


Here is my real formula:

=IFERROR(INDEX(Endo!$B$4:$GP$4,SMALL(IF(Endo!$B9:$GP9="x",COLUMN(Endo!$B$4:$GP$4)-MIN(COLUMN(Endo!$B$4:$GP$4))+1),COLUMNS($E$5:E$5))),"")

Instead of using GP as the column on the other sheet (Endo), I want to enter in a value of my choosing into A1 on my current sheet that determines the column in which this formula will use.


Sorry if I'm being redundant. Didn't know how convoluted this was and thought explaining a few ways may make it easier to understand. Plus, I'm no Excel expert. I've googled my way through problem solving for a few years but am no expert by any means.

Really really really appreciate any help or advice!!

 

[Norie]

For your first example all you should need is this.

=SUM(INDIRECT("C1:" & A1 & "1"))

 

[taurean]

Following would be one way how your real formula will look if you insert INDIRECT in it.

=IFERROR(INDEX(INDIRECT("'Endo'!B4:"&A1&"4"),SMALL(IF(INDIRECT("'Endo'!B9:"&A1&"9")="x",COLUMN(INDIRECT("'Endo'!B4:"&A1&"4"))-MIN(COLUMN(INDIRECT("'Endo'!B4:"&A1&"4")))+1),COLUMNS($E$5:E$5))),"")

 

[FGCguitar]

For your first example all you should need is this.

=SUM(INDIRECT("C1:" & A1 & "1"))

Following would be one way how your real formula will look if you insert INDIRECT in it.

=IFERROR(INDEX(INDIRECT("'Endo'!B4:"&A1&"4"),SMALL(IF(INDIRECT("'Endo'!B9:"&A1&"9")="x",COLUMN(INDIRECT("'Endo'!B4:"&A1&"4"))-MIN(COLUMN(INDIRECT("'Endo'!B4:"&A1&"4")))+1),COLUMNS($E$5:E$5))),"")

That's great! Just took a little rearranging of my quotation marks. Thank you so much for the assistance!! All works great now.