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

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 Carim

Once again, awesome results.

Thank you

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,

Well thats not often said "You are Right"

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

Thanks

Hi Carim

Sample sheet update witha sample chart
There is an error on the formula on sheet 1 which is throwing an #N/A where not required but I can look at this later.

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,

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

Sample sheet attached with my attempts, of dynamic ranges but cannot get them to end at the end of my data.

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