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 trying to pull pricing data from a larger workbook (masterprice.xlsx) for a new basic "price quote" workbook. The large workbook has a different sheet for each model name. In my new workbook, I would like to take the data for a given cell depending on what model I am working with. I was hoping, for example in the new sheet that if A2 is "Model A" (ideally this would be selected from a pulldown menu-but that is not quite the step I am at yet or exactly what I am asking here), then B2 take the corresponding price from the other workbook, so B2 would be set to ='[masterprice.xlsx]MODEL A'!$S$23 .

My question is, if A2 could be either "Model A", "Model B", or "Model C" (probably about 50 different sheets in total), what do I make the formula for B2? Instead of MODEL A is there a "text variable" (?) I would put in its place?

Sorry if this is unclear it is a little hard to explain. Any help appreciated.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

What you are looking for is the INDIRECT Function:

=INDIRECT("'[masterprice.xlsx]"&A1&"'!"&$S$23) Wrong answer

=INDIRECT("'[masterprice.xlsx]"&A1&"'!$S$23") Try this instead.

Where A1 replaces MODEL A etc. Please note, masterprice.xlsx needs to be open for this to work.

share|improve this answer
    
I am getting a #REF! error.. this seems like it is right but i can't figure out why. All I did was sub the actual filename for masterprice.xlsx and there is a valid integer in S23 of that larger workbook and A1 does have the name of the sheet in masterprice.. –  Mark Dec 30 '13 at 6:12
    
Sorry, I just read my answer and saw my error. The "$S$23" should be included in the quotes, otherwise the formula looks at the value inside S23 in the current worksheet and inserts the value into the formula. Try my fixed answer above. –  Nate Bergeron Dec 30 '13 at 6:46

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.