Formulas, names - and never the twain shall meet?

[Kiran]

hi folks,

i get excel models with upwards of 20 worksheets, and formulas on the sheets
that sometimes link to 5-6 sheets, and no named ranges used!!! i have to
audit these models... and if you have ever faced this scenario, you'll know
that its a short, slippery slope to tearing your hair out!

so.. my question is this. if formulas have been created using the standard
referencing of excel ie. revenue = A1*D32, is there a way for me to name A1
as Sales & D32 as MRP, and have the formula automatically use the names?

i sincerely hope there is a way to do this.. and one of you will point it
out to me... else, cuckooland beckons!

regards,

 

[T. Valko]

Hmmm...

I do the same thing for a living but I'm just the opposite, I prefer cell
references (except on real long complex formulas).

About the only thing I can think of is to create the names then do
Edit>Replace.

Find what: A1
Replace with: Sales

Lather, rinse, repeat!

Find what: D32
Replace with: MRP

 

[Kiran]

biff,

thank you for your response. but, it leaves me floundering still. i have
to repeat for all sheets of the workbook manually! nevermind the tedium, but
the scope for error is rather large.

regards,

 

[Lori Miller]

Try Insert>Names>Apply (xl2003 menus), you can then select all the names
that youu want to use in place of cell references in formulas on the same
sheet.

Note that if this is used with row and column named ranges, the name is
understood to refer.to the corresponding value in the same row/column.
Formulas referring to names in other sheets will not be updated however.