Need help pulling data from cells in different workbooks

[Plasma]

Hi, let me try and explain this the best I can:

say i have a workbook with 2 pages, on the first sheet, in cells C27
C47, C67 and C87

on the Sheet2, I want to make a list. in that list contains data fro
cells C27, C47, C67 and C87 from Sheet1 . on sheet2, the list wil
look like:


List
='Sheet1!C27
='Sheet1!C47
='Sheet1!C67
='Sheet1!C87

Is there any easy way to pull that data without doing each lin
individually. I know i can put in the = and pick the cell on the othe
sheet, but I have 100's of cells I need to get data from. If i try an
copy paste each line, it does:

='Sheet1!C27
='Sheet1!C28
='Sheet1!C29
='Sheet1!C30

i hope that makes some sense. i'm still new to excel and trying t
explain this is difficult.

thx for any hel

 

[Dave Peterson]

One way.

Put this in your top cell of the list:

="='Sheet1'!C" &(7+ROW(A1)*20)
(watch those apostrophes!)

And drag down.

Select the range
edit|copy
edit|paste special|values

Now you have text that looks like:
='Sheet1'!C27
='Sheet1'!C47
='Sheet1'!C67
='Sheet1'!C87
='Sheet1'!C107

With that range still selected,
edit|replace
what: = (equal sign)
with: =
replace all

Excel will see the change and notice that they're really formulas.

Be very careful with the formula--in fact, I'd just the conversion to formulas
on the top two cells.

If you made a mistake, you don't want to have to dismiss those "I can't find the
file--where is it" dialog boxes.