How to change the cell reference of many formulas quickly?

Computer

New Member
Joined
Jan 24, 2019
Messages
9
[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?
 
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.
 
Upvote 0

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top