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?

 

[FormR]

can someone tell me how to post an image?

Hi, in general, images are not a good way to show your set-up - if someone wants to help and needs to re-create your sheet for testing then they would need to manually re-type all the data from the image, some may be willing do that but I suspect most would not (me included )

There are tools you can use to post copyable data directly to the forum, which you can find here https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or you can upload a pared-down copy of your workbook to a file sharing site (like dropbox) and share the link here - you should note that this method restricts your potential helpers to those that are willing and/or able to take the risk opening files from unknown sources.

it returns blanck cells all the way through

What cell did you enter the formula in?
What value did you have in B22?
What did you have in column C onwards for the row entered in B22 on the sheet'Spring Weekly Schedule'?

 

[FormR]

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

You DO NOT need to use array entry (Ctrl+Shift+Enter).

 

[My Aswer Is This]

You DO NOT need to use array entry (Ctrl+Shift+Enter).

I'm sorry I see now you do not need {}

It works with them I now see and works without them.

But your script does work for me. I original mentioned using name range which also works but was not originally able to see image.

Thanks for your knowledge.

 

[Computer]

I will attempt to share a copy.

What cell did you enter the formula in?
What value did you have in B22?
What did you have in column C onwards for the row entered in B22 on the sheet'Spring Weekly Schedule'?

I entered it in A2 of the second sheet, I changed B22 to B23 as I told you the incorrect cell.

In B22 I have on the number 6.

In Row 6 starting with column C it is the data I started the thread with. - [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Orient 8a-12p[/TD]
[TD]L&M 1p-4p[/TD]
[TD]LAB 1p-4p[/TD]
[TD]AH 8a-12p[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, with the set-up you describe - these are the results I get. Are they what you expect?

Excel 2013/2016
	A	B
2	Orient
3	L&M
4	LAB
5	AH
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23		6
Sheet1

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

Excel 2013/2016
	C	D	E	F
6	Orient 8a-12p	L&M 1p-4p	LAB 1p-4p	AH 8a-12p
Spring Weekly Schedule

 

[My Aswer Is This]

Edit​

<strike></strike>

 

[Computer]

Hi, with the set-up you describe - these are the results I get. Are they what you expect?

Yes that is exactly what I was hoping for, but not quite what im getting. I have a few random values like monday in A2. I made a copy and took out the student names if you want to look at mine?

https://www.dropbox.com/s/dpgmy4h63t1r7c7/Spring Master 2.1 copy.xlsx?dl=0

 

[My Aswer Is This]

Edit.

 

[FormR]

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))

 

[My Aswer Is This]

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.