Posts by r_hind

    Cell Validation

    Depending on how data ends up in the cells I'm not so sure that there is any need to use code (VBA) to perform validation.

    If data ends up in a cell by linking or importing then maybe vba code is necessary but if you want to validate values / text etc as you enter then Excel's built in Data Validation should more than suffice.

    There are some excellent examples of Data Validation at Data validation when combined with Dynamic Named Ranges is extremely powerful and could replace vba code of reasonable complexity.

    Alternatively you could do a search of the forum looking for postings on the subject....there are lots of postings.

    Data Table Demonstrator

    I'm not certain why the data table demonstrator to work.

    If the (simplified) data table demonstrator won't work then sending (a more complex) example is unlikely to help.

    When you say "it won't work" can you clarify what you mean or what symptoms are evident?

    Have you got iterations turned on (Tools, options, [Calculation Tab]?
    Do you have the data analysis add-in installed and selected?

    I have used the data table concept to run multiples of very complex financial costings with significant degrees of success so I know it will provide the solution. It is important though to comprehend the basic concepts and structures demonstrated in the example.

    Have you got a basic example (spreadsheet) illustrating what you're trying to do?

    I've tried Application.EnableEvents=True and it didn't make any difference.

    Unfortunately the spreadsheet is quite large, contains confidential data and has quite a few veryhidden worksheets and code I'd need to do a bit with it before I could send it to you.

    I've used the WorkBook_Open functionality many times successfully.

    I have one particular spreadsheet however where for some reason Workbook_Open seems to be ignored.

    Does anyone have any idea why? Can anyone point me in the right direction.

    I've been trying to use some code suggested by Chip Pearson ( but can't get it to work for CustomDocumentProperties..

    I'm hoping that MSDN references to some Excel examples ..


    You access the DocumentProperties collection by using the BuiltInDocumentProperties and CustomDocumentProperties properties of an Office document. For an example that prints all built-in and custom document properties for an Office document to the Immediate window, see the PrintAllDocProperties procedure in the modDocumentPropertiesCode module in the ExcelExamples.xls file in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM.

    Does anyone have access to the file referred to?

    Was just looking through the messages posted prior to your own post of 3:18pm.

    Every single one of the messages posted provided good, valuable information that should help you work towards your solution…but I sense you're disappointed no one has given you the answer. If you take the time to work through them you will learn a lot and surprise yourself with your new found skills.

    In my opinion you've already conquered two of the more important concepts (Vlookup and Named Ranges) although if you explored Vlookup further you would find it more helpful.

    You've already discovered that (fixed) named ranges won't change in size (which is why you've created a lot of blank entries to fill up the rest of the named ranges). Take the time to learn about Dynamic Named Ranges and you'll be forever grateful. A Dynamic Named Range will expand or shrink as you add or delete entries from a list ..and with no need to "pad out" with blank entries or zeros. There are some excellent instructions on Dynamic Named Ranges on the OzGrid site see:

    It doesn't matter what technique you use (vlookup, autofilter, match, index or pivot tables) you should work with contiguous block of data (ie no blank or empty columns or rows). The same applies VBA Arrays (as opposed to Excel Arrays).

    For the solution you're looking for (returning multiple values matching the same criteria) you're going to find AutoFilter or Excel's built in database functions the best to work with.


    By the way I'm assuming, as I think others are, that you're using this forum because you want to learn and improve your skills and not that you expect a solution on a plate.

    It doesn't matter which of the suggested solutions (in previously posted replies) you're going to use you'll need to follow at least steps 1 and 2 from my posting and preferably also Step 3.

    When you've carried out those check out the help files on Pivot Tables and AutoFilter. Next learn about the Match and Index functions. When you've done that if you need more help leave a message.

    The original question is lacking detail.

    I'm assuming you have a spreadheet whose macros you want to use in new versions of the spreadsheet.

    If this is the case have you considered setting up templates (*.xlt files)?

    You've got the start of a good approach here but you need to re-think your structure. There is no need to keep repeating the User ID column.

    Step 1. Go through your tables. Delete blank columns

    Step 2. Delete 0s shown instead of blank records

    Step 3. Apply a dynamic named range to complete tables...say ItemsBought, ItemsSold, Sellers, Buyers (look through the OzGrid site - there are great references to Dynamic Named Ranges).

    Step 4. Set up some VBA Code

    'Read Named Ranges into Arrays
    ArrayBought() = Range _("ItemsBought").value

    ArraySold() = Range _

    ArrayBuyers() = Range _

    ArraySellers() = Range _

    ' Code to display all items bought for say
    ' USER_ID = 2
    ' and display in an area starting from say
    ' Cell A35

    UserID = range("C5").value
    MaxCol = ubound(ArrayBought,2)

    for X = 2 to ubound(ArrayBought,1)
    If ArrayBought(X,1) = UserID then
    For Y = MaxCol
    Range("A35").offset(X,Y) = ArrayBought(X,Y)
    Next Y
    End if
    Next X

    Note: Ubound identifies number of elements in the Array

    Hope this helps

    Can you clarify how you receive the new data?

    If you have control over how you receive the data I would do something like this:-

    Ensure new data is received in the format:-
    Location Number (as a unique identifier)
    Date (in a format that is not going to get confused - ie US vs Aus Format).

    New data would get added sequentially (new data gets added on next row

    Run a pivot - table to summarise results

    Or alterantively run some code to display data.

    Try to think of data as going in a series of boxes...

    (Assuming that you'll more entry dates than location numbers)
    across the top ....location number
    Rows represent dates

    Values entered into cells = temperatures

    If the data is as simple as you suggest this is a very easy task ...

    Since we can't post spreadsheets here at the moment ...feel free to send me (via e-mail) a sample of your data and I'll see if I can helpv you.

    I'm not certain what type of entries you're referring to...but if, for example, they were financial type entries you would structure your processes so that data entry is a distinct (sequential) process and clearly visible.

    The database (analysis would be of data entered).

    This sort of concept can also apply to other types of data although it doesn't typically work well when you're updating existing records.

    Good spreadsheet / database design (where you're dealing with data records with the same structure - ie fields) will always be based on keeping data entry and analysis structured and separate). If you're having to jump from one area of the spreadsheet to another (to put data in the right boxes) there is something wrong with your spreadsheet design.

    Robert's No#1 Rule. Get the spreadsheet design and structure right and the rest is easy!!