Skip
# Understanding external references in Microsoft Excel?

(adsbygoogle = window.adsbygoogle || []).push({});

(adsbygoogle = window.adsbygoogle || []).push({});

Post

# Understanding external references in Microsoft Excel?

August 8, 2006 7:05 AM Subscribe

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.

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.

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

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

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

posted by arimathea at 7:15 AM on August 8, 2006

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

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

='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

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

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 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

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

This thread is closed to new comments.

posted by JPD at 7:09 AM on August 8, 2006