Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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,

share|improve this question
2  
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
add comment

1 Answer

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.