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 have 100 worksheets with different names saved in a folder. I have another worksheet in another folder where I conduct analysis of data from one of those 100 worksheets. In this analysis worksheet, I have formulas containing name of a worksheet. While all such formulas remain same, I need to change the name of worksheet to another worksheet. How can I do it? Please, help asap.

share|improve this question
    
Welcome to superuser. Whats wrong with Excels build-in find & replace function? Its capable to replace strings within formulas. –  nixda Feb 14 '13 at 8:48
    
Thanks for the suggestion. Shall try it. –  user199159 Feb 14 '13 at 17:47
    
If you need to change the external reference often, then consider using the INDIRECT() function. You can then store the external spreadsheet name in a cell and reference it in your formulae. See Excel help for details. –  Lord Peter Dec 23 '13 at 8:23
add comment

1 Answer

Your question is very vague, but I think I understand it.

Basically you have a spreadsheet which gets information from a spreedsheet in another document and then does something with it. What you want to do is do the exact same thing for information taken from 100 different documents.

To get information from a spreedsheet in another document you need to use the following syntex in your formula...

='[DOCUMENT-NAME]SPREEDSHEET-NAME'!CELL

For example, to get the information in cell A1 in spreedsheet Sheet 1 from document one you need to write:

='[one]Sheet 1'!A1

Now you "just" need to change the information in each formula as needed, how you do this all depends on your naming system. Without more information about the names of each documents and the spreedsheets used it is impossible to give you more help.

share|improve this answer
    
Thank you for answering to my question. I clarify my question further: I have saved daily quotes (historical data) i.e. open price, high price, low price, close price, volume etc., for 100 stocks - named IBM, Microsoft, Apple etc. individually in a folder. Now, my new workse –  user199159 Feb 14 '13 at 10:50
    
Thank you for answering to my question. I clarify my question further: I have saved daily quotes (historical data) i.e. open price, high price, low price, close price, volume etc., for 100 stocks - named IBM, Microsoft, Apple etc. individually in a folder. Now, my new worksheet named My Analysis contains formulas that takes data from the IBM worksheet. While all formulas remain same, I just need to change the name from IBM to Microsoft so I get all data for analysis as all Stock worksheets contain fixed ranges, only data variable. Pls, help me now asap. Thanks in advance! –  user199159 Feb 14 '13 at 11:04
    
If you look at my second line of "code" (highlighted in grey) in my answer, you should have something similar to this in your My Analysis document, simply replace the word "one" (which would be "IBM" in your case I think) with the next document name (I imagine "Microsoft"). –  Kevin Anthony Oppegaard Rose Feb 14 '13 at 12:15
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.