Thanks:  0
Likes:  0

# Thread: Returning sheet name from formula

1. Mudface,

Thanks to Jay and Ivan, we have the answer:
http://www.mrexcel.com/board/viewtop...c=5003&forum=2

and the formula would be:
=MID(getformula,2,SEARCH("!",getformula))

2. Mudface,

Thanks to Jay and Ivan, we have the answer:
http://www.mrexcel.com/board/viewtop...c=5003&forum=2

and the formula would be:
=MID(getformula,2,SEARCH("!",getformula))

3. If a have a formula such as: -

=Sheet2!A1

is there a way of returning which sheet is referenced by the formula (ie in a separate cell I would like to have Sheet2 returned)?

Cheers for any help.

4. Another option. Why not specify the sheet in another cell and pull it for the formula. So in cell A1 is the value:
Sheet2
and in cell A2 is the formula:
=INDIRECT(A1 & "!B17")

5. Thanks, Rob, I did think of trying that, but thought it would be easier just to Copy the data from the source file and Paste it as a link (there are four spreadsheets, each with 3 sheets and I wanted around a 1000 rows by 20 columns from each sheet linked so I could summarise the source data via a pivot table). Might create another sheet, using your suggestion and see if that can be done easily (sigh) .

Code:
```Dim formula As String
Dim ExlLoc As Integer
formula = ActiveCell.formula
ExlLoc = Application.WorksheetFunction.Search("!", formula)
ActiveCell.Offset(0, 1) = Mid(formula, 2, ExlLoc - 2)```

7. Couldn't you just use one of Aladin's or Mark W's formulas that use =LEFT(A1...blah blah blah. I think I saw something like this posted in the last day or so.

8. Mark: You mean

=LEFT(C20,FIND("!",C20,1)-1)
and that will give Sheet2

[ This Message was edited by: Yogi Anand on 2002-04-11 14:13 ]

9. Yogi,

That could very well be something like that to which I was referring.

10. I may be wrong here, but I don't think that formula works. Doesn't find only search the value of the cell and not the formula?

## User Tag List

#### Posting Permissions

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