Posts by jl2509

Hi All,

Yet another challenge for me.

Sample sheet attached which I hope helps you understand

Thanks for any help offered.

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.

Ha ha, I guess so.

ever thought of some youtube videos which would explain how you make the very flexible Excel, even more flexible.

Many thanks to all the supporters at Ozgrid.

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

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,

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

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,

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

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

Once again, awesome results.

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,

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,

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

Ill give this a try and feedback

many thanks

Hi All

Any thoughts for a dynamic range that will use data from sheet1 but only from every 6th cell in Row E starting from E11.

i.e. E11, K11, Q11 etc

Thanks

managed to sort this now, appreciate the help offered.

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 Appreciate the repsonse,

Is there a way to do this in a formula not a macro?

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