Pulling cells from different excel files

[madadd]

Hello all,

I have a sheet with 700+ file names; starting at A3. In column C through J I want about 10 rows (and 7 columns) worth of information from each of the file names in column A. I realize I might have to manually go through Column A and add 10 rows in between each file name to give the proper space, but thats a separate issue (though if you have a quick idea of how to do this, that'd be great). In the past I've gotten help here on pulling information from different files. That being said, here's what I've got that doesn't yet work:

Please Login or Register  to view this content.

I can get this to work if I point to a single cell, say A11, just not the whole range, A11:G20

Thank you ahead of time for the help

[bentleybob]

Are you nesting this formula inside another? If not, what you're trying to do is to pull a range into a cell, which doesn't work so well. But if you left it referring to a$11, then copied it across seven columns, you would get the range that I think you're looking for.

[madadd]

No, its not nested. And I had thought about that, but because the A11 value that I want to change is inside a quotation (at least I think this is why), it does not change when expanded to other cells. Of course as I type this, I realize I can manually input what I need once (changing the A11 to C11 to D11 etc) and then copy that 7x10 table to the rest of the 700+ numbers. That's still a lot of work, but it'll have to do unless someone else has a better suggestion?

[bentleybob]

Ok. Assuming that you are referencing the first column in the target (Column A) from cell A6 of your spreadsheet, just make your formula:

Please Login or Register  to view this content.

Obviously, if it's not A6 you have to adjust the reference in COLUMN and/or the offset factor after ROW, but this should be sufficiently dynamic.

[madadd]

Sweet cheese! I was about to reply that it was a total and utter failure, but then I realized there were some typos/grammar mistakes. The resulting formula works great, thank you.

Please Login or Register  to view this content.

Very interesting use of the char(column()) functions. All just to put the column back into a letter format.

[bentleybob]

It's either use CHAR (or a lookup table) or use the dreaded R1C1 format.

[madadd]

So, your solution created a different problem. While I can easily expand out the formula to my 7x9 table or whatever size I want, I can't use it but for the first entry in my list. When I bring it down (10 rows down to the next filename) suddenly B5 is B15 and thats way out of the range within the table I'm looking at. And I can't add $ to it because that would make every cell display the same thing. I could subtract 10 from the +5 every time I change to a new filename. But again, there are 700+ filnames and thats a lot of changes.

[bentleybob]

Okay, I think there's a fix for this too. I think you're telling me that the first cell is, say B5, and that the first cell is for the second set is 10 rows down (B15). It shouldn't be hard to figure out a formula that determines where you're at (like B8) and what the corresponding lookup number is (Row 14). Can't look at it right now, but will tonight.

[bentleybob]

In my example (starting cell is B5 and needs to refer to A11 on the target), you would use:

Please Login or Register  to view this content.