Excel 2007 : Formatting issue when linking a column/s of data to other excel files

**gannon_w** · 02-03-2009, 08:08 PM

So in the attached document I have linked the "A" column to the "ADY" column in another file. When I drag the formula to the right I need it to go to every 24th column after the ADY in the other file. I just typed them in at first. So I do have the correct columns linked in the file. BUT although each row needs to link to the same column but a different worksheet...Is there a way to conditionally format the name portion in the equation...since I only have 10 rows in the file named 1.xlsx and 10 in 2.xslx and 10 in 3.xlsx...If I have adequately explained this I have a total of 11 columns and 30 rows meaning I have to individually edit each cell...I am trying to find a way to conditionally format the name portion?...Let me know if I have explained this well enough.

**Roger Govier** · 02-04-2009, 09:34 AM

Instead of
='[1.xlsx]S1'!$ADY$1

use
=INDEX('[1.xlsx]S1'!$1:$1,805+(COLUMN(A1)-1)*24)
and copy across.
It will self adjust to pick up each 24th column value.

If 1.xlsx is open, then you could use Indirect to set the sheet names
Enter the following in A1 and copy both across and down
=INDEX(INDIRECT("'[1.xlsx]S"&ROW(A1)&"'!$1:$1"),805+(COLUMN(A1)-1)*24)

If 1.xlsx is not open, you will see a #REF error.

In answer to your question about formatting parts of the formula a different colour - you can't.

**gannon_w** · 02-11-2009, 04:37 PM

The second function works like I need it to....THANKS

A new question...Now that I can reference a cell....how do I reference the column. I wan the minimum value from the ADY column and every 24th column after that. I've tried a few modifications to youe formula but can figure it out since I have never used the Index of Indirect functions....Thanks ahead of time