# Thread: Finding a cell . . .

1. ## Finding a cell . . .

I'm not sure how to describe this, precisely, but I'll try my best.

Cell A1 has a Name, and cell A2 has a number which is associated with A1.

In another worksheet in the same workbook a cell, say K9, has the formula =Name, so it displays the contents of cell A1.

I want to put in K10 the number associated with the Name that is referenced in K9, that is to say the value in A2.

I had though it would be OFFSET(INDIRECT(K9),0,1), but clearly it isn't.

What should the function I put into K10 be, please ?

2. Martin,

When you say Cell A1 has a "Name" do you mean a value like "Bruce" or does it have a Range Name like "MyName". NameEx.JPG

If it is a range name the formula: =offset(MyName,1,0) will return the number.

3. I knew it would be difficult for me to explain, and thanks for the reply.

A1 has a Range Name. I know that the OFFSET formula will work but I want to extract MyName from K9 so that I can copy the formula elsewhere, and so it will still work when the formula in K9 changes.

I hope that is clearer.

PS I am wondering if it is possible to write a user-defined function, using cell.formula, stripping out the "=" at the beginning and then using the result in the OFFSET function as you described ?

4. Hi Martin

In cell [K10] you could use:
=INDEX(Sheet1!B:B,MATCH(K9,Sheet1!A:A,FALSE))

zeddy

5. Something like this?

Code:
```Function ExtractName(rCell As Range)
ExtractName = Mid(rCell.Cells(1).Formula, 2)
End Function```
There is no checking to see if the formula starts with an equal or if it only contains a name, etc etc

Steve

6. Thanks for the replies.

Zeddy: I'm trying to avoid INDEX as, by the thousand, it seems as slow as VLOOKUP.

Steve: I assume that if you leave out the number of characters in MID, it simply takes from character 2 to the end ?

7. Hi Martin

I guess you mean avoid MATCH, as INDEX is very fast.
There are ways to speedup MATCH and VLOOKUP by restricting the 'search range'.
It all depends on what you are doing.

zeddy

8. Thanks everyone. This code did it beautifully, and simply:

Code:
```Function Price(Asset As Range)
Price = Range(Mid(Asset.Cells(1).Formula, 2)).Offset(0, 1).Value
End Function```
It is very quick and the Workbook is both uncluttered and self-documenting.

9. No its doesn't work - properly.

When I first enter the UDF in a cell, or many cells, the calculation works properly.

I have an updating Macro, and after it has run every instance of the UDF returns #VALUE!

The only way I can restore the cells is by re-entering each and every UDF formula. Other methods eg recalculate don't restore the proper cell values.

I have debugged the Macro and narrowed the problem down to a single line:

Code:
`Workbooks.Open Filename:=PriceFile`
This line "works" insofar as it opens the other Workbook correctly, but I don't understand why it has this disabling effect on my new UDF :-(

Any ideas most welcome.

10. Hi Martin

Try including..
Application.Volatile
..in the code in your UDF.

Excel's OFFSET() is a volatile function (like RAND(), NOW() etc)
(INDEX() became non-volatile in Excel 97 which is why it is fast)

A Volatile Function in a formula in a cell makes that cell always recalculate at each recalculation even if it does not appear to have any changed precedents. You should avoid volatile functions wherever possible.

zeddy

11. ## The Following User Says Thank You to zeddy For This Useful Post:

MartinM (2012-02-04)

12. Zeddy,

The major re-write is complete and working as quickly as I had hoped - I knew about volatile functions but had no idea you could affect that behaviour in VB. Neither did I expect that not sorting that out would lead to complete failure.

Martin

13. Your mention of volatile functions has triggered some research and I found this illuminating article: http://www.decisionmodels.com/calcsecretsi.htm which explains why the code . . .

Workbooks.Open Filename:=PriceFile (which happened to be a .csv file, naturally !)

. . . screwed up my Workbook:

Opening a .CSV file

Whenever you open a .CSV file, either through VBA or File-->Open, a recalculation of all open workbooks will be triggered, even if calculation is in Manual. Switching off .EnableEvents does not stop this recalculation. The only way I have found to prevent this recalculation from happening is to use VBA to switch Worksheet.EnableCalculation to False for each worksheet that you do NOT want to be calculated.

14. Hi Martin

I presume you have now fully resolved your reported problem.

The link was excellant - I particularly liked "conditional formats seem to be super-volatile".

zeddy

15. Fully resolved, thank you.

I remain bemused why the calculation order goes wrong, but am happy to accept that I now have an efficient and working Workbook.

And, as ever, I've learnt a lot on the way.

Martin

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•