Returning sheet name from formula

Al Chara · Jan 14th, 2002, 04:34 PM

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))

Al Chara · Jan 14th, 2002, 04:34 PM

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))

Mudface · Apr 11th, 2002, 04:08 AM

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.

Rob Jackson · Apr 11th, 2002, 04:31 AM

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")

Mudface · Apr 11th, 2002, 05:01 AM

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)

.

Al Chara · Apr 11th, 2002, 02:48 PM

How about using VBA:

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)

Mark O'Brien · Apr 11th, 2002, 02:54 PM

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.

Yogi Anand · Apr 11th, 2002, 03:12 PM

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 ]

Mark O'Brien · Apr 11th, 2002, 03:40 PM

Yogi,

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

Al Chara · Apr 11th, 2002, 04:35 PM

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?

Jan 14th, 2002, 04:34 PM	#1
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	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)) __________________ Kind regards, Al Chara

Jan 14th, 2002, 04:34 PM	#2
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	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)) __________________ Kind regards, Al Chara

Apr 11th, 2002, 02:48 PM	#6
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	How about using VBA: 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) __________________ Kind regards, Al Chara

Apr 11th, 2002, 02:54 PM	#7
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	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. __________________ Mark O'Brien Columbus Ohio Celtic Supporters Club

Apr 11th, 2002, 03:40 PM	#9
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	Yogi, That could very well be something like that to which I was referring. __________________ Mark O'Brien Columbus Ohio Celtic Supporters Club

Apr 11th, 2002, 04:08 AM	#3
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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.

Apr 11th, 2002, 04:31 AM	#4
Rob Jackson New Member Join Date: Apr 2002 Posts: 6	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")

Apr 11th, 2002, 05:01 AM	#5
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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) .

Apr 11th, 2002, 03:12 PM	#8
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	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 ]

Apr 11th, 2002, 04:35 PM	#10
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	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?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode