Pulling data from spreadsheets without set cells

Sir Carcass

This is more of a general question for something I may have to do in the near future. Say you get monthly spreadsheets from many sources and the cells you need to pull from could move around from month to month. What would be the best way to pull the data from these? Would VBA be capable of something like this? Is there a better way of dealing with spreadsheets that I'm not aware of?

schuss

Depending on what you're looking for, you could use VLOOKUP or HLOOKUP to find the cell, then refer to that result in your mapping. It would require a known unique label or key value in front of whatever you're looking to pull though.

Tofystedeth

Do they have column headers?
If the headers are least consistent you should be able to look them up that way.
Alternatively, try to fix the upstream process if at all possible.

Sir Carcass

That's part of the problem I'm having, I don't know exactly what these spreadsheets look like. I'm picturing them as a summary of how different funds performed rather than a traditional spreadsheet of a table of data. I know it's hard to offer a suggestion without more detail.

I guess I'm mainly curious if there's a way to find a cell based on the location of another cell and do that for many sheets with different references. Say something like cell D4 in one book says "Growth" and E4 has the data I need, and the next month those are in D5 and E5. And then another sheet has "Value" and the data is in the cell under it.

Bobble

Well, you need something to be consistent in order to write a formula for it. If you're consistently looking at column D to find the data, that's good enough, you can either do VLOOKUP or some OFFSET+MATCH stuff as needed.

ArbitraryDescriptor

Bobble wrote: »

Well, you need something to be consistent in order to write a formula for it. If you're consistently looking at column D to find the data, that's good enough, you can either do VLOOKUP or some OFFSET+MATCH stuff as needed.

Yeah, I mean it is absolutely possible to solve this problem if there is some kind of relative consistency; but you may need to eyeball the data to find it.

You could make a sheet of translation keys for different data arrangments (if they're extremely different, such as Growth's label not always being the same relative offset to it's value, or not being consistently labeled at all), but you would still need them to have a finite variance. If their relative layout is also changing month to month and not merely source to source, that key may be impossible to maintain, and Tofystedeth's recommendationof fixing the upstream process (or simply preprocessing) may be necessary.

Sir Carcass

MATCH and OFFSET are both functions I've never used before. That gives me a starting place, so thank you for that. And yeah, if having these things modified upstream is an option, that will definitely help, but I'm going on the assumption they're not at the moment. If everything works out, I should have more detail in the next few weeks, but I wanted to try and get a head start on it.

azith28

I would have them delivered in CSV format, import them all into there own tables in a database then program it to do whatever you wanted.

Sir Carcass

azith28 wrote: »

I would have them delivered in CSV format, import them all into there own tables in a database then program it to do whatever you wanted.

That is what I'm hoping, but I think there are like 90+ of these, each from a different source, so I'm not confident.

azith28

well it depends on what kind of cooperation you get from the various sites...are all the spreadsheets virtually identical in terms of columns/row headers? If so then thats even easier cause you only need one table in the database. if you get several of them that are identical then they can all go in the same table. if there are 90 different types of mixes of headers then you still want to do it this way instead of doing it yourself. Once you have it done you have it done forever.