How to change the cell reference of many formulas quickly?

[Computer]

[TABLE="class: grid, width: 850, align: center"]
<tbody>[TR]
[TD]Subject[/TD]
[TD]Start Date[/TD]
[TD]Start Time[/TD]
[TD]End Date[/TD]
[TD]End Time[/TD]
[TD]All Day Event[/TD]
[TD]Description[/TD]
[TD]Location[/TD]
[TD]Private[/TD]
[/TR]
[TR]
[TD]Orient[/TD]
[TD]1/7/2019[/TD]
[TD]8a[/TD]
[TD]1/7/2019[/TD]
[TD]12p[/TD]
[TD]FALSE[/TD]
[TD]Spring Orient. Rm 681[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]LM[/TD]
[TD]1/7/2019[/TD]
[TD]1p[/TD]
[TD]1/7/2019[/TD]
[TD]4p[/TD]
[TD]FALSE[/TD]
[TD]Resume Building rm 681[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]LAB[/TD]
[TD]1/8/2019[/TD]
[TD]1p[/TD]
[TD]1/8/2019[/TD]
[TD]4p[/TD]
[TD]FALSE[/TD]
[TD]Skills Lab[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]AH[/TD]
[TD]1/9/2019[/TD]
[TD]8a[/TD]
[TD]1/9/2019[/TD]
[TD]12p[/TD]
[TD]FALSE[/TD]
[TD]Skills Lab[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

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.