Understanding external references in Microsoft Excel?

August 8, 2006 7:05 AM

It's very clear in Microsoft Excel how to calculate formulas using external references, but what isn't clear is how, in several worksheets, to have the same data in certain columns. How do I do this?

Say I have an Excel file with multiple workbooks - BOOKONE, BOOKTWO, BOOKTHREE. Now, in each of these workbooks or worksheets, I have data that looks like, well, a price list, with a textual description, part number, price, etc. If in BOOKTWO I want to reference the data in BOOKONE - multiple cells at once - how can I do that? The range operator, e.g. ='BOOKONE'!A1:A15, doesn't work correctly, reporting #VALUE.

posted by arimathea to Technology (9 answers total)

Best answer: you mean you want to type in one formula and return all of the data? Or you want to perform an operation on that data? No matter what you want to use an array formula if you only want to type in one formula. To do that you would enter the entire range you want to reference, highlight the number of cells that matchs with the size of that range then hit F2 then ctrl-shift-enter
posted by JPD at 7:09 AM on August 8, 2006

or the even easier way it to like individual to one cell and copy paste
posted by JPD at 7:10 AM on August 8, 2006

like = link
posted by JPD at 7:10 AM on August 8, 2006

Response by poster: I want an external reference to a separate worksheet across a range of cells. I don't want to sum the cells, I just want to repeat them, I know they can be copied or pasted but i'd like them referenced directly so that changes to BOOKONE affect BOOKTWO.
posted by arimathea at 7:15 AM on August 8, 2006

Response by poster: In BOOKONE, column A has

alpha
beta
delta

I want BOOKTWO's column A to have the same results, and when I make a change to BOOKONE the change is propagated to BOOKTWO. One way to achieve this is by direct cell-to-cell references; is there a way to get all of this with one reference?
posted by arimathea at 7:19 AM on August 8, 2006

I don't think there is a way to have one single formula to reference multiple values/cels on a different worksheet/book. But if you use:
='BOOKONE'!A1
='BOOKONE'!A2
='BOOKONE'!A3
etc
you should be able to 'fill down' to wherever you need to save you having to make each formula manually.
posted by EndsOfInvention at 7:24 AM on August 8, 2006

If you just wnt a column of prices in book two that is identical to the column of prices in book one, just put =bookone!a15 or whatever. If you're looking to make each cell in booktwo's column be the contents of a series of columns in book 1, just kinda crammed together in one cell, you need to use the concatenate function. The computer I'm on doesn't have Excel, but I believe it'd go something like this: =concatenate (a1,' ',a2,' ',a3). YOu need those spaces in the list to set things apart.
posted by notsnot at 7:26 AM on August 8, 2006

Each cell in a spreadsheet has a value or a formula. You're trying to get a value into a spreadsheet cell without actually putting a formula in that cell? That's not how it works. Imagine if you were looking at cell GG947, and it was being remotely assigned a value from somewhere else in the spreadsheet, and you couldn't tell from where by looking at the cell. It would lead to exploding heads.

Note that if a series of formulas in a column are mathematically related, pasting a whole column is usually very easy. Put in the formulas for two adjacent cells, highlight them, and drag the corner down to the bottom of the range you want to fill with similar formulas. So you need not type out the formulas for 100 cells down a column, if that's what you're fearing.
posted by jellicle at 7:28 AM on August 8, 2006

Best answer: Best you can do with writing just a single formula is an array formula as JPD mentioned in the first post and even then you'd need a separate array formula for each column.

Say you want to replicate column A of BOOKONE to column A of BOOKTWO. Highlight all of column A in BOOKTWO, write the formula "=BOOKONE!$A:$A" (without quotes) and hit CTRL-SHIFT-ENTER. This converts it to an array formula and will copy all of column A in BOOKONE to column A of BOOKTWO.

For column B, you'd need to highlight column B of BOOKTWO, write in the formula "=BOOKONE!$B:$B" (without quotes) and hit CTRL-SHIFT-ENTER.

Repeat with the other columns.

The advantage of using the array formula is that it continues to work in BOOKTWO as you add more rows of data to BOOKONE. Had you just used =A1 in cell A1 and performed a fill, you'd have to extend the fill each time you add more rows of data in BOOKONE. Downside is you get dummy data in empty rows. Wherever the array formula encounters an empty cell, it returns "0".
posted by junesix at 8:10 AM on August 8, 2006