Software

Ask something
Discussion Locked

Excel Cell change shortcut

0 helpful
5 replies

by jas03

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a spreadsheet with linked values such as this:
=+'E:\My Documents\Excel\[tabfin.xls]tabfin'!E124
my question is:
is there a way to change the end value from
=+'E:\My Documents\Excel\[tabfin.xls]tabfin'!E124
to
=+'E:\My Documents\Excel\[tabfin.xls]tabfin'!E125
without having to edit every single cell and having to do it yourself?

5 replies

by firstpeter

If you want to change the value on numerous cells en masse, you can do the mass find and replace (Ctrl-H, search for "[tabfin.xls]tabfin'!E124" and replace with [tabfin.xls]tabfin'!E125").

If you're wanting to change the 124 to 125 on the next row, simply copying and pasting should do that automatically (provided there isn't a $ before the 124).

Do either of those fit your situation? If not, it can probably be done programatically (i.e., a macro), but unless you had hundreds of cells you'd probably spend more time building/testing the code than the time you'd save by not doing it manually. If you want to go that route, however, let me know and I can help.

by jas03

Thanks
I do have hundreds of cells. Rows are added in the middle which makes the rest underneath point to the wrong value. Your copy & paste solution does work for some cells, but some other cells are combined ex/
=+'E:\My Documents\Excel\[tabfin.xls]tabfin'!E125+'E:\My Documents\Excel\[tabfin.xls]tabfin'!E126
That's why I was wondering if Excel has a function that increases that numeric value by a specific amount.
Thanks again

by pblostit

click edit>find>replace
make sure search within formaulas is activated
in find what enter the value u want 2 replace
in the replace field enter the value u want it replaced with.
then click replace all. all will update immediately.

by jas03

Thanks, but all the values are different
ie
=+'E:\My Documents\Excel\[tabfin.xls]tabfin'!E125
=+'E:\My Documents\Excel\[tabfin.xls]tabfin'!E126
=+'E:\My Documents\Excel\[tabfin.xls]tabfin'!E127

I need to change a series like that to
=+'E:\My Documents\Excel\[tabfin.xls]tabfin'!E126
=+'E:\My Documents\Excel\[tabfin.xls]tabfin'!E127
=+'E:\My Documents\Excel\[tabfin.xls]tabfin'!E128

This discussion is closed to new replies.