1

BACKGROUND and PROBLEM:

  • Sheet1 has a row per record, where each record is a different scenario.
  • Each record/row has a column1 which has similar, but slightly different, formulas in it, one per row.
  • Each formula contains varied references to multiple NamedRanges.
  • Sheet2 would like to lookup a recordID in Sheet1, return the formula for that record, and substitute text "new" for "old" in the NamedRanges.

As far as I can tell, this cannot be done in a function, but would love to be proved wrong.

To date, I've

  • just copied the formula, and done a find/replace "searching within formulas", but going forward I need this to be done within the formula.

  • tried to find a way to do INDIRECT, but since each formula can be different, that doesn't work.

Substitute returns text and doesn't appear to search within a formula.

Here's an EXAMPLE (https://docs.google.com/spreadsheets/d/1YlNVICaJyw3AdIpiJ7Fhw1TJgOiWd3k-MRGafVXvTjY/edit?usp=sharing)

  • I want the formulas in Sheet2 for "Calculation" Column to look up the Record Number in Sheet1, find the formula for that record, replace it, and then substitute "_old" with "_new" in the NamedRanges.
  • Note that each formula (for record 1, 2, and 3) are all slightly different
  • Note that on Sheet2, some record numbers can be repeated, and the Color/size/inputs vary on Sheet2, but the FORMULA should be the same (except for updating the named ranges)
PC McL
  • 11
  • 1
  • 5
  • Have you looked at [this](https://stackoverflow.com/q/26522305/2638872)? – mrtig Jan 14 '18 at 03:04
  • @mrtig yeah, i read that and didn't look like it solved it for me since my original formulas I want to reference are different. – PC McL Jan 14 '18 at 03:37

1 Answers1

0

I believe what you need can be achieved using the INDIRECT() formula function.

You can read about it here: https://support.google.com/docs/answer/3093377?hl=en

If you've tried and failed to get the job done using INDIRECT(), post your attempts here (preferably link to a spreadsheet with some data and the formulas you've come up with so far), so we can try to help.

Alternatively, you may want to manipulate the formulas using a user-defined function.

Jacek Lampart
  • 1,741
  • 13
  • 25
  • I don't think we can search within one of those. In this case I would either need to: (1) somehow substitute NameRange_old with NameRange_new during the INDIRECT, or (2) need to redo all the original Formulas with a ton of new INDIRECTS, but I'd still not be able to reference those when the formulas are different. – PC McL Jan 14 '18 at 02:58
  • Yes, having to redo existing formulas is very likely. Can you share an example spreadsheet? – Jacek Lampart Jan 14 '18 at 03:01
  • Added an example to OP. – PC McL Jan 14 '18 at 03:24
  • See https://docs.google.com/spreadsheets/d/1BinqubaFwOzTEshS7NOftToN1935PelD5cVZlr0wVOs/edit#gid=0 for the solution - the important parts are described in red on the last tab. – Jacek Lampart Jan 14 '18 at 03:59
  • Unfortunately that doesn't solve it, since the formulas in Calculations column differ by Record Number (basically each record number is its own function). The goal is to be able to add a new row in Sheet2, and based on the RecordNumber, pull the correct function from Sheet1, but refer to the Input/Color/Size on Sheet2, and the "new" named ranges. I'm starting to think this can't be solved unless i get crazy with user-defined functions, which i'm trying to avoid. – PC McL Jan 14 '18 at 04:25
  • Check it out now. – Jacek Lampart Jan 14 '18 at 04:33
  • It tells me to move this to chat, but says I don't have enough reputation to do so. The issue is that it still doesn't dynamically get the right formula from Sheet1. For instance, if you changed the Record numbers in Sheet2, it wouldn't get the right one from Sheet1. – PC McL Jan 14 '18 at 04:49
  • Yeah, if you need full flexibility of the formulas and being able to look them up by id, then you're looking at a UDF. – Jacek Lampart Jan 14 '18 at 05:11