Copy data down and across range

  • 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

  • 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 "Thumbs Up" icon, 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 "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,


    Well thats not often said "You are Right" :thumbup:


    I will put the sample sheet together in a while and send to you.
    Appreciate the help.


    Thanks

  • 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

  • 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 "Thumbs Up" icon, 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 "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!