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.

Hy I have a Excel File (a) and a cell into it that is equal to another cell in another file (b);

The of the (b) excel file is "2010 something";

Now The cell (c) is ='[2010 something.xls]test'!$K$224

What I want is to make a variable that mantains the year and will be stored into a cell

Then the cell (c) will change its reference based on the year; So in 2011 instead of looking for the 2010 something.xls will be looking for the 2011 something.xls

How to do it? thanks

share|improve this question
    
I don't understand what you mean by "maintains the year". Does it maintain the year from the referenced source? The original source? The current year? –  Raystafarian Jul 16 '13 at 17:38
    
This could be helpful. –  Jerry Mar 29 at 7:43
add comment

2 Answers

If your 2011 value is stored in cell A1, then, in some other cell, you could try this formula: ="'[" & A1 & " something.xls]test'!$K$224"

share|improve this answer
1  
this just makes a string.. i dont get the referenced value –  Totty Mar 2 '11 at 17:07
1  
Just found it: use INDIRECT function/method –  Totty Mar 2 '11 at 17:26
    
yeah...sorry; I meant to include the INDIRECT idea ! (No, really!) –  F106dart Mar 2 '11 at 17:55
    
still so not solves my problem because I need this to work even if the worksheet is closed –  Totty Mar 3 '11 at 9:43
add comment
up vote 0 down vote accepted

use INDIRECT function/method https://www.google.pt/search?q=INDIRECT+excel .

share|improve this answer
    
You can also concatenate data. ="[" & A1 & "something.xls]test'!$K$224". However function is usually more user-friendly way to do it ;) –  lowak Mar 31 at 10:36
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.