Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-16-2015, 05:29 AM
chillerlegend chillerlegend is offline Creating macros for a well structured analysis system Windows 7 64bit Creating macros for a well structured analysis system Office 2010 64bit
Novice
Creating macros for a well structured analysis system
 
Join Date: Jun 2015
Posts: 1
chillerlegend is on a distinguished road
Default Creating macros for a well structured analysis system

Hello Community,



I am currently in some troubles. Over the last year I have worked in cooperation with a company on several analyzes of their delivery structures in the framework of a thesis. There are now several hundreds of thousands of date-records which are connected in these documents.

I wanted to capture all analyzes in an Excel document in the first instance, however, the first troubles occurred already after a short time and Microsoft Excel led increasingly to give any feedback after which I decided to distance myself documents after meaningfull criteria after a short time.

Here are some examples:
Worksheet 1 = all items with Item-Number and name and anything else Product specific criteria
Worksheet 2 = all stocks and stock values this one is already connected with Worksheet 1, because I've worked in this as in all other worksheets with item numbers for identification and always linked the item description for understanding by VLOOKUP
Worksheet 3 = consumption
Worksheet 4 = external stocks
Worksheet 5 = first analysis - as an example for people who have a bit of knowledge in the logistics a combined ABC / XYZ analysis which worksheets called from all previous data refers. Worksheet 6/7/8 with further analyzes which relate more specific data from the previous worksheets and analyzes.

I think everyone here knows VLOOKUP only works if the path on which the reference relates isn´t closed. So I have now always to open all the worksheets which are needed for this in the correct order, as these also still could relate to each other and I have to consider what needs to be open, that they all work.
I am done with my entire work and now want to hand over the complete data for further use to the company, with my final presentation.

But then the VLOOKUP must function without opening it, because opening this system always in the correct order has become very complex and is too complex for explaining this exactly to a stranger.
I have lately looked around a bit and have read various forum posts and stumble upon so-called macros with which it should be possible to solve the problem. I can use almost every formula that is somehow required in Excel in my estimation but I've never written a macro. Furthermore, I noticed that these macros are written relatively complicated and have to be recreated each time you do a new references (VLOOKUP).

Here is one VLOOKUP example:
=VLOOKUP([@Material],'Y:\School\Diplomarbeit\IST-Analysen\00 Eingrenzung der Artikel+erste Auswertungen\00_Eingrenzung_-_YLX03_001_007_028_031.xlsx'!Materialliste[#Daten],2,FALSE)

I have read in an other forum that the structured table reference which I'm using is the problem that it doesn't work when the other workbook i closen.
But how should I change about 15 tables in 10 different workbooks which are connect with about 50VLOOKUPS to a normal range?

Therefore, the question to you, dear community is, what do I have to do about 10 worksheets with hundreds of thousands of records that are linked to a variety of combinations by VLOOKUP and I want to pass it to a company?
Is there a tool which may converts VLOOKUP to macros? Or must this all be written manually by myself? And when it’s the case, how does it work? I miss the entire basics of this and I can´t find matching instructions for helping me to solve my problem.

Best Regards
Philipp
Reply With Quote
  #2  
Old 06-21-2015, 05:28 AM
macropod's Avatar
macropod macropod is offline Creating macros for a well structured analysis system Windows 7 64bit Creating macros for a well structured analysis system Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

It seems to me the best solution might be to combine the workbooks referenced by your LOOKUP formulae into the workbook containing those formulae. That way, you don't have to worry about which workbooks are open. Furthermore, this approach requires no macros and, subject to worksheet size limitations with older Excel versions, will work with any version of Excel.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating macros for a well structured analysis system When creating new template from established template macros getting lost. TechEd Word 3 06-09-2014 07:22 PM
Creating macros for a well structured analysis system Creating macros in a new riboon dreamz Word 6 06-08-2014 03:31 AM
Creating macros for a well structured analysis system Delay Analysis ketanco Project 3 03-15-2013 01:06 PM
Creating document from template but macros are still linked to template webharvest Word 0 07-16-2011 09:34 AM
Creating macros for a well structured analysis system Creating macros SCNN Word VBA 2 06-02-2010 04:53 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:37 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft