Hi all,
Hoping you can point me in the right direction. I've recently inherited a workbook that I need to keep up to date. One of the tasks at the moment is to download several reports and combine them into a master sheet. To date, it's been possible to do straightforward cross-sheet lookups (ie, ='Sheet2'!F55) after spending a lot of time sorting the inputs so that everything is in the same order across all the different reports - sheet1 B2 will look at sheet2 b2, sheet 3 b9 and sheet 4 d6, and then sheet1 B3 will look at sheet2 b3, sheet 3 b10 and sheet 4 d7, and so on. (apologies, there has to be a better way to explain this, but I'm not familiar with the board controls)
However, we're now getting to a stage with the reports we're ingesting aren't going to line up neatly any more - data's not going to be as homogenous or reliably sortable into precisely the same order, as the original reports will have missing lines. At the moment, my solution is to manually move the data in the feeder sheets to where the master sheet is expecting to pull from, leaving several blank rows where data hasn't yet been received. It's doable, but it's very clunky and potentially error prone, and if there's a more effective way of running this for the next few years, I'd like to try it.
Every project has a project ID, and all the data in all the reports is organised vertically. It feels like it wouldn't actually be that much hassle (compared to manual faffing) to restructure my master sheet as a vlookup - functionally, what I want is a code that looks for a project number on a specified sheet, and then brings back the data in a specified range of cells to a specified range of cells in the master sheet, rather than the current fixed location links. (preferably, I'd put all this into an actual database, but that's not currently an option)
Questions, then:
a) would you consider it a waste of time to restructure the master sheet? I'm currently pulling data from 3 sheets into 60 columns, and I'm expecting to add another 40-odd columns from another 2 sheets.
b) vlookup or index-match?
c) where to start?
Hoping you can point me in the right direction. I've recently inherited a workbook that I need to keep up to date. One of the tasks at the moment is to download several reports and combine them into a master sheet. To date, it's been possible to do straightforward cross-sheet lookups (ie, ='Sheet2'!F55) after spending a lot of time sorting the inputs so that everything is in the same order across all the different reports - sheet1 B2 will look at sheet2 b2, sheet 3 b9 and sheet 4 d6, and then sheet1 B3 will look at sheet2 b3, sheet 3 b10 and sheet 4 d7, and so on. (apologies, there has to be a better way to explain this, but I'm not familiar with the board controls)
However, we're now getting to a stage with the reports we're ingesting aren't going to line up neatly any more - data's not going to be as homogenous or reliably sortable into precisely the same order, as the original reports will have missing lines. At the moment, my solution is to manually move the data in the feeder sheets to where the master sheet is expecting to pull from, leaving several blank rows where data hasn't yet been received. It's doable, but it's very clunky and potentially error prone, and if there's a more effective way of running this for the next few years, I'd like to try it.
Every project has a project ID, and all the data in all the reports is organised vertically. It feels like it wouldn't actually be that much hassle (compared to manual faffing) to restructure my master sheet as a vlookup - functionally, what I want is a code that looks for a project number on a specified sheet, and then brings back the data in a specified range of cells to a specified range of cells in the master sheet, rather than the current fixed location links. (preferably, I'd put all this into an actual database, but that's not currently an option)
Questions, then:
a) would you consider it a waste of time to restructure the master sheet? I'm currently pulling data from 3 sheets into 60 columns, and I'm expecting to add another 40-odd columns from another 2 sheets.
b) vlookup or index-match?
c) where to start?
