reference a cell from another workbook

[Workinhard]

I am referencing a cell on another workbook,which is the sum of several rows of data. However when I add add'l rows of data to the source worksheet, the other (2nd) worksheet doesn't keep the sum cell reference and it pulls data from the incorrect cell. I've tried to format it two ways:


=[FinlDashboard2013.xlsx]Contracted2013!BH63


and also


=[FinlDashboard2013.xlsx]Contracted2013!$BH$63


Either formula seems to work IF I have both workbooks open when I update the source worksheet. But if I update the source workbook (by adding or deleting rows)and don't have the 2nd workbook open , when I open the 2nd workbook, the cells don't have the proper reference. I don't get any message asking if I want to update the workbook when I open it?


thanks in advance for any solitions

 

[SamCas]

Hi,


Why dont you run an 'INDEX' with 'MATCH' formula? You will tell excel to look for specific coordenates, lets suposse that the data you are looking for will always be under the same column name, and will have the same row name, an example:


=INDEX(=[FinlDashboard2013.xlsx]Contracted2013!A$:$BH$,MATCH("-a main cell name on the 63rd row and A:A column"-,A$:A$,0),MATCH("-Column name where your value is located which I think is the BH-",A$1:BH$1,0)).


I know it's kinda long, but it will look it up even if you reduce the amount of columns in your file.


-Insanity is doing the same thing over and over again and expecting different results-