Posts by Lee(UK)

    Re: View Different Ranges At Same Time


    Sorry, I don't think I'm understanding you correctly here. If I create a vertical split, as far as I can see, there is only one vertical scroll bar and both panes scroll at the same time, unlike a horizontal split where each pane has a vertical scroll bar.

    Re: View Different Ranges At Same Time


    I have tried the split pane method, however the page scrolling problem remains as scrolling is synchronised. I believe that there is a way of stopping his using VBA, however I haven’t been able to get it to work. Do you know of a way?

    Re: View Different Ranges At Same Time


    Dave,
    Thanks for your reply - apologies if my header was wrong, but I thought putting what I was trying to achieve as the title would be more help. Anyway, I’m aware that you can view two ranges side by side in separate windows, but that isn’t what I’m trying to achieve here (unless this is the only way).
    I would like to have a single work sheet that displays two ranges of data so for example A1:F50 is one range, and H1:K20 is a second range. In the actual situation I’m copying data from a hidden sheet into the range on the visible sheet via a macro (there are several ranges that get copied via other macros but appear in the same range on the visible sheet). The visible range of data in most cases is longer than the viewable page meaning that a user would have to scroll the whole page to see the remaining data, which means that range two also scrolls off the page. Ideally what I would like to do is reduce the size of range one to a size that fits onto the page and add a scroll bar to it so that all data can viewed by scrolling the range rather than having to scroll the whole page and therefore keeping range two visible. I hope this makes a bit more sense.
    Lee

    Hi,
    Does anyone know if it is possible (and if so how) to add a scroll bar to excel (currently using 2003). What I am attempting to acheive is to have two areas on a work sheet, the first of which contains data that is much larger than the second but both of them need to appear on the screen at the same time. If it was possible to apply a vertical scroll bar to the first range which is named this would be possible. Any help here would be very much appreciated, if this is a bit vague for a solution and you require any further info, let me know and I'll be more than happy to fill you in.
    Thanks in advance for any help.
    Lee

    Hi Jindon
    Yes, that is pretty much want I want. Thank you very much for you help. Could I ask one further favour, as I'm just learning vba, I can get a general idea of what's going on, however, could you just give me a brief explanation or put some comments into the code so I get a better idea. If you could it would be very much appreciated.
    Cheers
    Lee.

    Hi Dave,
    Thanks for your reply.


    I'm not sure that I've explained myself correctly as I don't think your solution is what I'm after. I've attached the file I'm working on. As you can see the user selects an item in column C from a drop down list that is validated by a list supplied by the other sheets in the workbook. Columns D, E and F are populated via vlookup formulas after the user has selected an item in column C.


    The problem I'm having is that I need some validation for columns D and E. The normal validation function doesn't work as the result is being created by a formula. For column D I need to ensure that there are no duplicates - if a duplicate occurs I'd like a msgbox to alert the user to the error and prevent the input (the same as if you'd used a formula to prevent duplicates upon input). For column E the I need to validate if there are more than three entries the same - i.e you can select 3 players from the same club but not more than this - again the alert should be similar to column D.


    Hope this gives you a better idea of what I'm trying to do. Apologies for being a bit vague in the first post.
    Cheers
    Lee.

    Hi All,


    Can anyone help out with this one. In the very shortest terms, what I am trying to do is validate entries in cells that are populated by a formula.


    The basics of the sheet is that it contains 5 columns and 11 rows, the 1st column contains fixed data, the second is validated by a list using a named range contained on a different sheet. The 3rd/4th and 5th columns are populated by a vlookup formula, by taking the data from column 2 and looking up data contained on the same sheet as the named range.


    The main problem (as you have probably already guessed) is that I cannot validate the cells that have vlookup formulas in them. An example of what I need to achieve is that the user selects 11 items using the validation lists to populate column 2. Following this columns 3/4 and 5 will be poplulated by the vlookup formulas. It is possible that in one of these columns, that there could be four entries of data that are the same, but the user is only allowed three. What I would like to do is prevent the fourth item being entered.


    I am able to do this with a rather lengthy IF, OR formula, however I would like to create a pop up for the user rather than a line of text on the sheet. I'm very new to VBA, which I suspect is what is required here, therefore if anyone can provide any help/guidance here, it would be very much appreciated.
    Many Thanks
    Lee