Replacing strings in forumulas

jawsont · Sep 16th 2013

So I'm trying to generate a mark set workbook that has the following sheets:

1.Student names and averages
2. Assignment titles
3 through sheet#whatever - individual student mark worksheets

I'm trying to update individual names within formulas on the Students sheet that point to named worksheets (see the picture below). Unfortunately, I'd have to repeat the process of linking each and every cell with each and every sheet. I feel like there's a simple way to do this with a formula and I'm just missing the obvious. I tried concatenation, but that doesn't appear to work well as it doesn't result in a formula when done, but rather a string.

For example, if Student in cell 1 is Blow, Joe then the formula on sheet 1 row 1 for the first assignment would be "='Blow,Joe'!B4+'Blow,Joe'!C4 +'Blow,Joe'!E4 + 'Blow,Joe'!I4.
Blow,Joe has his own worksheet (let's say alphabetically he's the first after the Names and Assignments worksheets. It is from this sheet#3 (his sheet) that the values are being pulled from. Next, student #2 is, say Blue, Joesphine. Her formula will be: ='Blue,Joesphine'!B4+'Blue,Joesphine'!C4 +'Blue,Joesphine'!E4 + 'Blue,Joesphine'!I4. Each formula for her is pulling information from her worksheet titled Blue, Josephine.

What would be ideal is if, when I change the student name on sheet#1, then it would change the text_string (e.g. Blow,Joe) to accommodate the new name. Does that make sense? I'm sure there's a better way than simply just copy/pasting information.

I'd embed the picture, but I think you need to see the larger size to see all the tabs/formulas. As such it's linked below.

http://i.imgur.com/v9K2R5A.png

jawsont · Sep 17th 2013

Re: Replacing strings in forumulas

By using the formula

+"'"&A3&"'!B4" I get the right formula for the cell, but it appears as text.

e.g.

'Blow, Joe'!B4'

Any way of turning a text string that LOOKS like a formula into an actual formula? I'd love to automate the columns so that if I change the student name in column 1 it would automatically update the sheet titles in the formula.

Rowddawg · Sep 17th 2013

Re: Replacing strings in forumulas

Closest thing I can find for changing text to formula...

http://www.mrexcel.com/forum/e…convert-text-formula.html

See if this helps. I'm also looking for an overall solution for you, but not sure how close that is...

Rowddawg · Sep 17th 2013

Re: Replacing strings in forumulas

Here's some basic vba code to do what the post is saying... just change the range in the vba code to match where your formulas will be. (i.e. instead of d9, a1:a100) All I did to create it was use the macro recorder, so I'm sure there's a more efficient way, but it should do what you're looking for.

*edit: If your formulas do not have =, substitute what they do have in common, like the + in your example

Code

Sub replace()
'
' replace Macro
'




'
    Range("D9").Select
    ActiveCell.replace What:="=", Replacement:="=", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Find(What:="=", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
End Sub

Display More

jawsont · Sep 17th 2013

Re: Replacing strings in forumulas

Quote from Rowddawg;684285

Closest thing I can find for changing text to formula...

http://www.mrexcel.com/forum/e…convert-text-formula.html

See if this helps. I'm also looking for an overall solution for you, but not sure how close that is...

I found this post in particular to be gold.

Quote

If you have a formula, say

=VLOOKUP(I2,B2:N2001,11,FALSE)

and it shows up as text in an Excel cell, then you have to go -> Edit->Find->Replace and 'replace' any part of the text with the same thing for instance ',FALSE)' with ',FALSE)' then it will recognise and convert it to a formula.[Blocked Image: http://www.mrexcel.com/forum/images/smilies/tongue.png]

It allows me to autofill the columns by pulling the various student names, then I simply replace any equal sign with any other equal sign and the boxes all return the correct formula (if that makes sense). =+"=+'"&A2&"'"&"!B4" spits out =+'Blow, Joe'!B4' which, when I find/replace any symbol occurring in all cells (e.g. =) immediately re-recognizes the text as formula. Done! To think I too was digging into VB for an answer Rowddawg.

Thanks all!!!

Rowddawg · Sep 18th 2013

Re: Replacing strings in forumulas

Glad it worked out. My wife teaches, so I certainly see the functionality with what you're working on.

jawsont · Sep 18th 2013

Re: Replacing strings in forumulas

Quote from Rowddawg;684414

Glad it worked out. My wife teaches, so I certainly see the functionality with what you're working on.

Frustratingly we're being mandated locally to go from traditional marking to a new form. It means that the sheets I've developed and used for years are obsolete. Ultimately this new form of evaluation isn't excel friendly, but then again, there are no other ways to generate marks atm.

I'm trying to develop the spreadsheet to make it more friendly. My next step is to read up and see if I can embed "buttons" with the VB code so that people using the spreadsheet don't see the VB being run on the tail end to populate various worksheets and copy the base assignments across all the sheets.

In the end I strongly suspect going to pure VB to write a graphical interface might work best, but hey, I'll try to stick with excel as long as I can.

Thanks again.

Replacing strings in forumulas

Participate now!

Share