# Find and return sheet name

I am looking for help in finding a string within a workbook and returning the sheet name (Address) or where the same can be found, e.g. Let's look for Lisbon which is located in sheet Portugal (sheet 5 of 10)

Lookup string "Lisbon" ... Looking all sheets within a workbook and would return... Sheet "Portugal!A2"

I do have a formula which lookup through all sheets but don't tell me the location, so any help will be appreciated.

Kind regards,

• I'm not familiar with looking up strings across multiple sheets. However, I'm aware there are some functions that will return a cell's location reference. Perhaps if you can provide the formula you're starting from, I could mash it together with what I know to give you something workable? – Iszi Sep 4 '13 at 17:12

I can give you a more specific answer if you provide the way in which you are looking up across multiple sheets, however I can provide some guidance in that you may want to look into the INDIRECT funtion. It allows you to take a value as text and look up the cell that it references. It is in the form INDIRECT(,[reference style])

Where Cell reference can be something as Simple as

• A1 to mean Column A Row 1 or
• R1C2 to mean Row1 Column1

Or something More Complicated like

• CONCATENATE("'October SM Cards'!B",'2C2FWorksheet'!A1+34)

Where I an Concatenating

``````'October SM Cards'!B
``````

with

``````'2C2FWorksheet'!A1+34
``````

resolving to the Value of A1 +34 on the 2C2FWorksheet before it appends to the 'October SM Cards'!B creating a reference like

``````'October SM Cards'!B37
``````

when everything is done. Wrap that in an INDIRECT and it will reference the cell name/number you have pieced together.

Complete reference example

``````=INDIRECT(CONCATENATE("'October SM Cards'!B",'2C2FWorksheet'!A1+34))
``````

With all of this information perhaps you can piece together a location by using the sheet name