Posts by jl2509

    Hi All,


    Anythought show to attach a macro to a dropdown box or a named cell which would unlock and lock cells after any changes are made to the Dropdown or cell?
    I cannot do this generically on sheetchange as other macros need the sheet to be unlocked during their functioning and it wopuld get rather messy to have multipl lock and lock statements throughout the macros.


    so...


    Sheet1 cells H17 and I17 have a dropdown box with "NA" and "OK" as selections


    when H17 is changed to = "OK", then unlock Range H18:H26
    when I17 is changed to ="OK", then unlock Range I19:I26


    and vise versa when "NA" is selected


    Thanks for any help.

    Hi Carim


    wow, thats exactly what I wanted to achieve.
    Again, I never though of adding maxcol into the offset function but is another method I will not forget..


    Where abouts do we selecte on the website to elevate you to supreme MVP


    Many thanks

    Hi Carim,


    I have created a sample dynamic range =OFFSET(Sheet2!$B$3,0,0,1,COUNTA(Sheet2!$3:$3)) but because of underlying formula From B3 through to column DD the range is indicated though to column DD even if there are only 4 datasets on sheet 2
    How do I stop the range going past the last entry "Visible Data"



    Thanks

    Hi Carim


    Thanks for the updated sheet. Looks great.
    The fix on the formula was also a bonus.


    A bit lost though, as this is not dynamic. If I add new data sets on sheet1, they are not transposed on the chart.
    I have copied the range B5:B10 over to Column DD, but do I ned to manually add the data into the chart?


    Is there a way to make each of th series dynamic with offset named range in the name manager to link the chart series to?


    Sorry to be a pain


    Thanks

    Hi Carim


    Using NA() or #N/A works fine, but leaves a blank area where the bar should be in the chart.
    Not very pretty...


    What would be your thoughts on creating an offset named range for Sheet2 range(B3:DD9) that ignores NA() or #N/A
    If this is possible, then I could reference that instead of the range direct and would of course be dynamic.


    Thanks

    Hi Carim,


    Bit of an issue, the original Sheet1 name has a space in, I only used Sheet1 as sample name forgetting about the spaces in tab names.
    The original Sheet1 tab name is "Act and Exams Complete"


    How do I change this?


    I have tried wrapping in "' Act and Names Complete"' but I get a #REF! error


    Thanks

    Hi Carim,


    Thats awesome, very much appreciated.I noticed that you changed the fromula from Idex to indirect.
    I didnt think to use that function!!!


    One thing though, this condensed range is for a chart and I dont want to show the results with NA, do you know how I would achieve this.
    I can start a new thread if thats the best way forward.


    Thank you

    Hi All,


    Sorry to be a pain, but this is not my intent.


    This is not just about copying the data, it needs to be dynamic hence as records on sheet 1 change. I need to use a formula such as offset(columns, index(columns etc but dont know how to achaieve this.


    Thanks

    Hi All,


    I would appreciate some help on the following please.


    Sheet1 - Data1 is in Cell E11, then Data2 is in K11, then Data 3 is in Q11 etc


    I want to be able to show the data on sheet2 but in sequential coumns by dragging cell B3 across tthe columns


    i.e Data 1 in Cell B3, Data2 in Cell C3, Data3 in Cell D3 etc.


    I have played around with index and columns to create a formula to copy this accross but cannot accomplish it.


    If anyone can put light on this, can you please exaplin what the steps are in the index and columns forula (if you go this way)


    Thanks