Ask Your Question

Extract named formulas and use them in another spreadsheet [closed]

asked 2012-12-21 17:05:06 +0100

tntrush gravatar image


I have named several formulas and use them in my spreadsheet.

For example, through Insert -> Names -> Define, I created -among others- the "sum" formula which performs a sum up procedure.

Could I possibly extract all the formulas I created along with their names, in order to use them in another spreadsheet?

Even better, is there a way to store all the names and the corresponding formulas in a spreadsheet called "Formulas" and make it work from there? (probably not ...)

I would appreciate your help.

Kind regards,

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-19 18:49:28.723459

3 Answers

Sort by » oldest newest most voted

answered 2012-12-22 03:08:04 +0100

m.a.riosv gravatar image

We can make a try, I have not look at all, but it seems to work,because copying a cell using a named formula in other spreadsheet, copy also the named formula.

  • Create a new blank sheet, in the spreadsheet with named formulas.
  • Menu/Insert/Names/Insert - Paste all.
  • Convert the formula names in formulas with Find&Replace:
    Select the column with formula names
    Search for: .*
    Replace with: =&
    More options, selected range, mark regular expressions.
  • Open the second spreadsheet where you want the named expressions.
  • Copy the arranged sheet in the second spreadsheet.
    Right-click on the tab, select copy/move.
    Select in Location the second spreadsheet. Ok.

The named formulas are in the second spreadsheet.
Some choices, delete in the second spreadsheet the new sheet (named formulas remain), hide this new sheet, or left as is.

edit flag offensive delete link more

answered 2012-12-27 23:52:05 +0100

m.a.riosv gravatar image

If you can use in the second spreadsheet then they are there, do not need to redefine.
Menu/Insert/Name/Manage (Ctrl+F3) to see all named ranges/formulas.

To concatenate text or cells is easier use the & than the CONCATENATE() function.
i.e. ="Hello "&A5&named_formula

edit flag offensive delete link more

answered 2012-12-27 17:18:31 +0100

tntrush gravatar image

Thank you Mariosv!

Indeed, the named formulas are in the second spreadsheet.

How could I append them in the "Insert->Names->Define" table?

Should I do it manually or there is a way to append them at once?

I need to do that, because the formulas contain other formulas. For example:

=CONCATENATE("<tr class=""mprow_title""><td

colspan=""3"" class=""mpcell1"">",OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,2),"")

Which grubs the contents of the secondly next cell and concatenates them in the predefined text.

Kind regards, tntrush

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2012-12-21 17:05:06 +0100

Seen: 884 times

Last updated: Dec 27 '12