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?

 

[My Aswer Is This]

Not sure about your case but I use Named Ranges.
Then change the value in the named Range
Like This:

=SUMIF(Names,Name,Totals)

 

[Computer]

This is the sheet that the data is being pulled from. If any other info is needed please let me know.

 

[My Aswer Is This]

I cannot see a image here. And I just gave a example of mine.

I really do not know what your formula is trying to do.

 

[Computer]

This is the correct photo I meant to send the first time. As far as sumif goes I am pretty sure it won't work the way I have it set up. Id be happy to change my setup but Im not sure I could make it work?

 

[Computer]

I can't see your example nor can I figure out how to post my png file. I used the insert image and it adds the url but that's the end of it. I'm pulling data from an excel calendar for multiple people covering 6 months. The formula is only supposed to pull the string from left to right ending with space as several points of data are in one cell (orient 9a-12p). That data must be in 3 separate cells, the other problem is the sheet follows a row order and I need it in column order.

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

I'm sure later today someone else here at Mr. Excel will be able to help you.

 

[FormR]

I was hoping I could do something like C$(B22) and then every time change the number in B22 it auto populates.

Hi, welcome to the forum!

Try this alternative in A2 copied down.

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

I'm assuming your original formula was normally entered (even though you included curly braces {}) - this is normally entered also.

 

[Computer]

https://drive.google.com/file/d/1xxr-FOU4ozLGDz17T_juRQw1f31HSW1eVg/view?usp=sharing

Round three. If this doesn't work can someone tell me how to post an image? FormR this is a copy of the 'Spring Weekly Schedule' sheet. Each row is a student. I tried your formula (which I don't understand what it is so I can adjust it) and it returns blanck cells all the way through? Also, yes the formula is entered normal lol. I have no idea how to use this editor.

 

[My Aswer Is This]

Not sure why but I'm now able to see your image which before I could not see.

But FormR formula works for me.

You need to enter formula into cell then Press Ctrl+Shift+Enter

Now in the Formula Bar you should see:

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