• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Automate Specific Cell Extraction from Multiple WkShts (not VLookup) (1 Viewer)

Junkee Brewster

Registered User.
Local time
Today, 21:09
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.
 

Attachments

  • Workbook Example.zip
    8.6 KB · Views: 146

shades

Registered User.
Local time
Today, 05:39
Joined
Mar 25, 2002
Messages
516
Use the Indirect in cell H3:

=INDIRECT(C3&"!E3")

Then copy down.

In the next column, I3 put:

=INDIRECT(C3&"!E5")

And copy down.
________
Yamaha Mo History
 
Last edited:

Junkee Brewster

Registered User.
Local time
Today, 21:09
Joined
May 21, 2005
Messages
33
Shades, you're a deadset legend. THANKYOU THANKYOU THANKYOU. You have no idea what you've done :D

I am now remindeded of 3 important things:

1) Something is not useless because I do not know how to use it properly (teehee)
2) K.I.S.S principle
3) Why Access World Forums is my one and only stop in the world of forums when I can't find the answer I need.
 

shades

Registered User.
Local time
Today, 05:39
Joined
Mar 25, 2002
Messages
516
Glad to help. And appreciate that you came back with the results for the board.

Well done!
________
Oxygen vaporizer
 
Last edited:

Users who are viewing this thread

Top Bottom