Copy data down and across range

  • Hi All,


    Attached is a sample sheet to help my plight.


    I hope sheet 2 explains enough for for you to help me.
    The sample data is a small amount compared to the actual data, and I need the formula to be as dynamic as possible due to addition and deletion of data from sheet1



    Thanks

  • Hello,


    First of all ... thanks for your file ... which is crucial to explaining your constraints ...!!!


    Attached is your Test file ... :wink:


    Hope this will help ... :smile:

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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 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,


    Regarding the sheet name issue ... see attached Version 2 ... :wink:


    Regarding the chart issue and how to skip #N/A .... take a look at following explanations:


    https://support.microsoft.com/en-ca/...contains-blank


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Carim


    Once again, awesome results.


    Thank you


    Pleased to hear this is helping you out .. !!! :wink:


    Once again ... Thanks a lot ... both for your Thanks ... AND for the Like ... :smile: :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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 again,


    You are right ...


    Why don't you attach your file with your chart ...


    This would make it easier to design a solution fully customized ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi again,


    Thanks for your file ...


    A couple of recommendations you will find in the attached version 3 ...


    1. Given the chart type you have picked ... my ' cosmetic ' recommendation would be to swap the Mean ...to make it stand out ...


    2. Adjust your Average formula ...in sheet 1 ...


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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,


    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


    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


  • Quote

    Supreme MVP

    ...:lol::lol::lol:


    Pleased to hear this is helping you out ... :wink:


    The real beauty is ... How Smart and Flexible ..EXCEL can be ... !!!


    Again Thanks for your Thanks AND for the Like :cheers:
    :jumpupdo:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hey JL, we have enough of a problem keeping Carim's head small enough to fit on the site without promoting him to Supreme MVP!!!!!! :spin:

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hey JL, we have enough of a problem keeping Carim's head small enough to fit on the site without promoting him to Supreme MVP!!!!!! :spin:


    :lol::lol::lol::lol::lol::lol::lol:


    :facepull::facepull::facepull:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)