Referencing/linking cells

QuestionEdit

Using07

I am trying to use data from another externalfile which is constantly updated.

Say I need prices on icecream from icecream.xls

I have my own calculations and formulas in calculations.xls, but I want to use the data from icecream.xls without having to reference each cell one at a time.

icecream.xls is updated daily by a vendor, and all I want to do is open up calculations.xls and see the data automatically imported.

Is there a way to do this?

Also, in that same question, how can I create formulas that will auto-correct horizontal data instead of vertical when writing to vertical data.

i.e. I have A1 B1 C1 from sheet1

and I want to formulate A1,A2,A3 on another sheet2 referencing sheet1, but the auto correct formula will reference A1,A2,A3 from sheet1.

AnswerEdit

If the data in Icecream.xls is organized as a database or list, then you could set up a querytable or a pivottable to pull the data over. You can set the query to be refreshed whenever the workbook is opened. You would find this under the Data menu (in xl2003 or earlier) or in Excel 2007 under the Data tab. You would be looking at getting external data.

Excel won't automatically adjust in a transposed fashion as you desribe.

You can write a formula to overcome this

Say in A1 of sheet2 I put

=Offset(Sheet1!$A$1,column()-1,row()-1,1,1)

then drag fill it down column A in sheet2 and it will sequentially reference the cells in the first row of Sheet1.

row() returns the row number of the cell that contains the formula column() returns the column number of the cell that contains the formula

so in the offset function, I have placed the column() in the row argument and the row() in the column argument which then does a transpose.

so you can do what you want, but you have to do the calculations to set up the formula to do what you want.

If there is anything not clear, you can post a follow up or contact me with a sample workbook at