Find and replace within formulas?

[Mark]

OK; lack of forward planning! I have set up a weirdly-complicated
spreadsheet to sit pupils in order of test marks – politically
incorrect, but gets results! At the moment the sheet consists of 4
worksheets. These are a list of pupil data, the seats in pupil
perspective (for projection), a similar one from my perspective, and a
final one for "fixing the list".
The desks are arranged in a row of 8, a row of 10 with a bend at the
end, and a final "U" shape.

Currently I have the cells for each table with a reference to the list
"=list!B3" for instance. It would be nicer to have an error trap to
prevent "0" appearing for some classes, so I want to put in
"=IF(ISBLANK(List!B3), " ", List!B3".
What I don't want to do is to manually alter each one. Is there a
"quick way" of doing this?

My next idea is to have certain tables omitted depending on the number
of pupils ... but that's a subject for another posting.....

 

[Bob Greenblatt]

OK; lack of forward planning! I have set up a weirdly-complicated
spreadsheet to sit pupils in order of test marks ­ politically
incorrect, but gets results! At the moment the sheet consists of 4
worksheets. These are a list of pupil data, the seats in pupil
perspective (for projection), a similar one from my perspective, and a
final one for "fixing the list".
The desks are arranged in a row of 8, a row of 10 with a bend at the
end, and a final "U" shape.

Currently I have the cells for each table with a reference to the list
"=list!B3" for instance. It would be nicer to have an error trap to
prevent "0" appearing for some classes, so I want to put in
"=IF(ISBLANK(List!B3), " ", List!B3".
What I don't want to do is to manually alter each one. Is there a
"quick way" of doing this?

My next idea is to have certain tables omitted depending on the number
of pupils ... but that's a subject for another posting.....

You can use that formula (with a closing parenthesis of course) and then
copy or fill it to all needed cells.

 

[Mark]

You can use that formula (with a closing parenthesis of course) and then
copy or fill it to all needed cells.

But each seat is different; The first one will refer to List!B1, the
second to List!B2, the third B3 and so on ...

 

[PhilD]

"=list!B3" for instance. It would be nicer to have an error trap to
prevent "0" appearing for some classes, so I want to put in
"=IF(ISBLANK(List!B3), " ", List!B3".
What I don't want to do is to manually alter each one. Is there a
"quick way" of doing this?

Could you not do a search and replace?

Find <=List!>
replace with <=IF(ISBLANK(List!B3), " ", List!>

(don't type the angled brackets)

You could probably ignore the = at the beginning and ! at the end, too.

This worked on the PC version of Excel I have at work. It should work
on Mac, too.

Hope this helps,

PhilD

 

[JE McGimpsey]

Could you not do a search and replace?

Find <=List!>
replace with <=IF(ISBLANK(List!B3), " ", List!>

(don't type the angled brackets)

You could probably ignore the = at the beginning and ! at the end, too.

This worked on the PC version of Excel I have at work. It should work
on Mac, too.

It doesn't work in XL04 - the result of the replacement is missing the
closing right paren, so gives an error.

I suspect it also would not work for the OP because it hard-codes B3 as
the test cell, when it's likely that the test cell should be the same as
the result cell.

 

[Mark]

It doesn't work in XL04 - the result of the replacement is missing the
closing right paren, so gives an error.

Yeah, I did try that (or something similar) and it gave the missing
parenthesis comment - I wonder if there is a way of turning this off

 

[PhilD]

It doesn't work in XL04 - the result of the replacement is missing the
closing right paren, so gives an error.

I hang my head in shame: I didn't notice the missing bracket. Sorry if
I confused the issue!

PhilD