This problem is having me scratching my head. I am not sure if there is a solution as a search on internet does not come up with anything close to an answer. So hopefully someone out there can show me a way or say "give up - there is no way".

I have a IFERROR, INDEX, MATCH formula that finds and returns values from other workbooks. The other source workbooks are named Site01, Site02 and so on. The results are listed in a column so row 1 will return values from Site01, row 2 will return Site02 and so on. So in the formula it will refer to ..."Site01.xlsm" (twice).

Is there a formula (to place next to these results) that will search the said IFERROR, INDEX, MATCH formula and return the source workbook name i.e. Site01 , Site02 and so on? I am assuming there may be a generic way to search a given text in a formula or a specific way to return a workbook name.

Please note I am searching a formula not a text.

Any guidance is greatly appreciated.

Thanks!

## Replies (7)

## Please login or register to join the discussion.

So your formula contains Site01.xlsm

not the returned value?

Can you therefore not just use

MID(

address of cell with formula,FIND("filename to find",address of cell with formula,1),LEN("filename to find"))Or perhaps I have misunderstood

returns #VALUE

Thank you ADCWebb. Unfortunately your suggestion returns #VALUE!.

Also (and I realise I did not explain this very well), I need the result to return the file name and there are 65 different file names in the column, which would mean writing a formula for each file name find ie Site01, Site02 etc,. So would not be possible to copy the FIND formula all the way down the 65 rows.

I hope this makes sense?

Because of course

it is searching the returned result not the formula ... silly me.

Does THIS article help in

way?anywill try Excel 2013

Hi ACDWebb

Apologies for not responding sooner.

Another respondent has suggested using Excel 2013. So I have upgraded and this resolves. Just leaves writing the search/find formula but I should be able to work that out.

Many thanks

If you have Excel 2013

use FORMULATEXT to return the formula as text which you can then search using the normal text functions. Otherwise a VBA solution is probably the best bet.

Response

Paul

Apologies for my slow feedback.

I have now upgraded to Excel 2013 and =formulatext works. Many thanks for the tip.

Another thought

ASAP Utilities has a function ASAPGETFORMULA which will also return the formula as text.

ASAP Utilities is an (in my view, excellent) addon from here: http://www.asap-utilities.com/

However it is not free for business use.