Junkee Brewster
Registered User.
- Local time
- Tomorrow, 02:00
- Joined
- May 21, 2005
- Messages
- 33
Completely Automate data extraction from specific single cells per worksheet (multiple per book) into a summary column (descending in the same order as the worksheet orders):
I am a complete Excel blockhead. Access = fine. VB for Access = fine. Excel = pretty formats and autosums. That is it. This is probably a common enough request, however I assure you I have spent some time searching here and Google.. and nothing applies to me, or (perhaps more likely) I can’t make it apply to me and I’m just getting more confused the more I read! VLookup is making things worse - it sort of is what I want but don't think it applies and it's throwing me off - plus I need to have a VARIABLE worksheet reference.
I’ve attached a dummy spreadsheet so you can see the kind of workbook I am dealing with, and what I want to happen (I think it’s easier than me trying to explain). You can see that each worksheet is more of a “design” than for data storage. I've had to unmerge all the cells to get the single cells I want. Red colour = what i would like to happen.
I will end up merging into large workbooks (currently: 2 days per workbook for 4 YEARS = thousands of worksheets). So as you can see, the “formula” MUST simply allow me to “paste down” in the summary columns – I cannot do any manual editing or referencing of individual worksheets - it's just not humanly possible. There are no set numbers of w/s per w/b or names of w/s - each will have a different name (corresponding to a row on the summary sheet).
I apologize for the “quick fix” nature; normally I like to teach myself, but I was given 3 weeks to finish the db (after 2 years of saying we need one for this area) and I'd hate to be thwarted by something I have previously avoided in FAVOUR of Access (the irony!)
Thanks for your help and patience - Junkee.
I am a complete Excel blockhead. Access = fine. VB for Access = fine. Excel = pretty formats and autosums. That is it. This is probably a common enough request, however I assure you I have spent some time searching here and Google.. and nothing applies to me, or (perhaps more likely) I can’t make it apply to me and I’m just getting more confused the more I read! VLookup is making things worse - it sort of is what I want but don't think it applies and it's throwing me off - plus I need to have a VARIABLE worksheet reference.
I’ve attached a dummy spreadsheet so you can see the kind of workbook I am dealing with, and what I want to happen (I think it’s easier than me trying to explain). You can see that each worksheet is more of a “design” than for data storage. I've had to unmerge all the cells to get the single cells I want. Red colour = what i would like to happen.
I will end up merging into large workbooks (currently: 2 days per workbook for 4 YEARS = thousands of worksheets). So as you can see, the “formula” MUST simply allow me to “paste down” in the summary columns – I cannot do any manual editing or referencing of individual worksheets - it's just not humanly possible. There are no set numbers of w/s per w/b or names of w/s - each will have a different name (corresponding to a row on the summary sheet).
I apologize for the “quick fix” nature; normally I like to teach myself, but I was given 3 weeks to finish the db (after 2 years of saying we need one for this area) and I'd hate to be thwarted by something I have previously avoided in FAVOUR of Access (the irony!)
Thanks for your help and patience - Junkee.