Finding #Name Within a Formula

**Hejl82** · 02-09-2008, 12:37 PM

**Bjornar** · 02-09-2008, 12:45 PM

You could use this formula in A1:
=INDIRECT("'"&B1&"'!A26")

If you put Chris in B1, then A1 will show you the value form the cell 'Chris'!A26

**Hejl82** · 02-09-2008, 12:59 PM

INDIRECT is an intersting function, but it's not quite what I need. I want an adjacent cell to essentially search the formula next to is, and return the #Name that is within that formula.

**Bjornar** · 02-09-2008, 01:25 PM

I don't think there is a way to search and extract this from the formula text itself. If it was a text you should search in it could be done with a formula.

If this is something you need to do one time for some reason, I think it could be done with VBA. But if this is something you want to do dynamicy for some purpose in your worksheet, I think you have to rethink your worksheet design.

If you upload a .zip file with your sheet, and show us why you need to do this, I'm sure you will get some useful feedback.

**Hejl82** · 02-09-2008, 01:32 PM

In my above response I added a word doc with a screen shot of the worksheet. You'll see in column A it has cells information from other tabs. I want to have column B list which tab the info from A came from.

If this is still not enough I can work on attaching a zipped file, but I'm trying to stay away from VBA.

Thanks,

**Bjornar** · 02-09-2008, 01:53 PM

Staying away from VBA as long as you can is often a good idea, but sometimes there are no other solutions.
In your case I still think a redesign is what you need. That could be by using the Indirect function I showed you.

You have 3 solutions:
1. Write the sheet name in the formula manualy, and write the sheet name in the B column manually. This requires you to write the name 2 places
2. Write the sheet name in the formula manualy, and display the sheet name in the B column automaticly. This is what you ask for, and requires VBA
3. Write the sheet name in column B manualy, and populate this in the formulas automaticly. This can be done with formula (INDIRECT).

Both alternative 2 and 3 requires you to write the name only once.