How to change the cell reference of many formulas quickly?

[Computer]

Subject	Start Date	Start Time	End Date	End Time	All Day Event	Description	Location	Private
Orient	1/7/2019	8a	1/7/2019	12p	FALSE	Spring Orient. Rm 681		TRUE
LM	1/7/2019	1p	1/7/2019	4p	FALSE	Resume Building rm 681		TRUE
LAB	1/8/2019	1p	1/8/2019	4p	FALSE	Skills Lab		TRUE
AH	1/9/2019	8a	1/9/2019	12p	FALSE	Skills Lab		TRUE

<tbody>
</tbody>

The formula in cell A2 looks like this { =IF(ISTEXT(LEFT(OFFSET('Spring Weekly schedule'!C$6,0,ROW()-2),SEARCH(" ",OFFSET('Spring Weekly schedule'!C$6,0,ROW()-2))-1)),LEFT(OFFSET('Spring Weekly schedule'!C$6,0,ROW()-2),SEARCH(" ",OFFSET('Spring Weekly schedule'!C$6,0,ROW()-2))-1),"") }

Id like to be able to quickly change the row reference for C$6 as there are many cells and Ill do this many times. I was hoping I could do something like C$(B22) and then every time change the number in B22 it auto populates. Is this possible or is there some other reasonable solution?

 

[Computer]

It looks like cell B23 that you are using for the row number is in the "Spring Weekly Schedule" sheet.

See adjustment.

=TRIM(LEFT(SUBSTITUTE(INDEX('Spring Weekly schedule'!$C:$Z,'Spring Weekly schedule'!$B$23,ROWS(A$2:A2))," ",REPT(" ",255)),255))

This is a beautiful thing! Thank you both very much, you've saved hours of entering in schedules. Yes, I see now I wasn't clear. I was entering the row B23 in the 'Spring Weekly schedule' sheet, not in the '.cvs creator' sheet. I also modified $C:$Z to $C:$ES as I had many more columns but It works like a dream FormR.

 

[Computer]

From looking at your image in Post 1

Is Subject in A1
Is Orient in A2
And Orient is the result of the formula correct?


If not please explain.

Yes, That is correct. Row 1 is the headers required to for google calendars to accept an excel .cvs file and correctly convert that to a schedule.

 

[My Aswer Is This]

Glad to see you have things working.
FormR is great with formulas.