INDIRECT returning #REF error when trying to convert string to formula

1. INDIRECT returning #REF error when trying to convert string to formula

Hello,
I have a complicated formula which basically returns a text string that looks something like this:

48000/1000

Now I just want to actually calculate this value. I have tried

=INDIRECT(formula)

but that returns a #REF error, and I can't figure out why, since there are no errors in the string itself.
I was hoping someone here might have ideas?

If you are interested in the details of the formula that generates the string, here it is:

TEXT(INDEX('recode ranges'!\$A:\$BG,MATCH('reformat numerical'!\$A2,'recode ranges'!\$A:\$A,0),MATCH('reformat numerical'!K\$1,'recode ranges'!\$1:\$1,0)),"General")&'rules for num format'!A3)

where the INDEX function returns the value which would be 48000 in the string example given above at the top of this post, and where the 'rules for num format'!A3 contains the text "/1000".

Thanks in advance for any help!

2. Re: INDIRECT returning #REF error when trying to convert string to formula

indirect doesnt evaluate text
if its only going to be in one cell try
insert name define new name call it say "mycalc"
in refers to put
=evaluate(TEXT(INDEX('recode ranges'!\$A:\$BG,MATCH('reformat numerical'!\$A\$2,'recode ranges'!\$A:\$A,0),MATCH('reformat numerical'!\$K\$1,'recode ranges'!\$1:\$1,0)),"General")&'rules for num format'!\$A\$3))
now try =mycalc

3. Re: INDIRECT returning #REF error when trying to convert string to formula

It might be easier to offer suggestions if we could see the context that you are using this in?

Maybe something like this though...
=48000/MID("/10000",2,99)
=INDEX('recode ranges'!\$A:\$BG,MATCH('reformat numerical'!\$A2,'recode ranges'!\$A:\$A,0),MATCH('reformat numerical'!K\$1,'recode ranges'!\$1:\$1,0)),"General")/mid('rules for num format'!A3,2,99)

4. Re: INDIRECT returning #REF error when trying to convert string to formula

Thanks, martindwilson and FDibbins, for your quick replies! I used FDibbins suggestion and it worked.

The larger context is that I need to be able to create dynamic formulas where data in a spreadsheet is automatically adjusted by specific formulas (multiplied or divided by a certain factor, for example) if the name of the column in which the data appears is on a particular list. The type of adjustment to each data point depends on what column it is in, and I need it to be flexible so that I can use it with a bunch of different large datasets. I know there is a way to do this kind of thing with Macros, but I prefer to avoid them as my programming skills in that area aren't great, and I tend to have more trouble with Macros when moving from computer to computer (and I work on a bunch of different ones).

5. Re: INDIRECT returning #REF error when trying to convert string to formula

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

There are currently 1 users browsing this thread. (0 members and 1 guests)