Dynamic formula, (copy from dynamic formula and ignore 0)

Hi,

See the attached file, the list is converted to table so that it can take future addition and be dynamic. Column E & F has formula solution.

Hi,

Updated file with +Index +array+ Vlookup

Punnam

Thanks for the fast reply guys, @Misrasomendra can you pls convert the file to excel 2003 forgot to mention im sitting on an old version.

A sheet for NAveed Raza

Hey Punnam can you update the file in excel 2003? thanks

Can you explain the date in Column A will be copied whole. and yellow cells based on filled or not?

The dates should only be copyed if there is a value in the coulored.

The idea is im trying to create a graph, but i am struggeling so im trying several solutions.

my goal is: Graph for coloum A+B, Graph for column A+C.

The graph should only show the Valued numbers of "B", in this case 15, <10, <10, <10
im not interested in the dates where the column is empty, and i want to be able to look at last X tests,

i can post another version where i have applyed several things, such as look at last x months, (i dont want months i want testresults).

so far i thought if i copyed the "valued cells" i could make a way around the problem when creating the graph

So this will create 12X2 columns for each data. Than the graph should be individual or in same?
Than how are thinking of plotting <10 on a graph.as rest are numbers?

Please ignore <10 part of my last comment, it is CF. Please answer remaining part.

yeah i want to create a new sheet where the graphs are posted, (check the oploaded file)

and the sheet is individual, (12 graphs on that sheet), you can see on the sheet i oploaded that i can change the number of months,
unfortunetly i want to change the number of "tests" the months are irrelevant but the tests are what i want to get a fast overview of.

the <10, if you look at the first page you will notice i type 9,9 and it is displayed as <10 this is not somthing of importance,

i uploaded a wrong version, (the dates are numbered meaning i get a horrible x-axis scaling)

This version shows the pylons on top of the date

See the attached file, I had tried to build as per 2003. I had extracted two set of data and draw the graph of 1 set, with dynamic named range.

AAAAAAAAmazing

THis is definetly way more useful than what i made so far, one last thing (big last). Is there a way to edit it so i can change the range?

as you can see in some colomns i have many "tests" and the year of which some have been taken is 2009, if i just want to look at the last 3 tests or 5? is there a way to make this selectable?

Give me some time to work over this.

I am very thankful! i have been baning my head trying to solve this what you gave me allready is worth ALOT, now i can actually use it, instead of it being nonsense. thanks so much

i dont know if you have seen my last uploaded file, but in it i found a way to select according to months. but that dident make sence since i had so many "empty" ones. dont know if that is any help

hi,

is this what you are trying to achieve

Punnam

Hey Punnam, not quite.

Misrasomendra uploaded a file (the last one he uploaded) it shows what i want to do, the only problem i have now is that, in some cases Colonnes (B to M) are the tests made at variouos dates, if you look at the dates you can see they span over a very long time, (2009 till now), and some are considered "old", if i want to look at the last 3 tests made what do i have to do. or the last X tests.

See the file, done for one graph.

Thanks so much

Okay i know this is a long stretch, can you explain how you did it? i need to implement this to the "real document"

And im trying to understand your method but cant see how you did it

And again thanks ALOT this is incredible

Thanks for the feedback. You are Welcome.

So here what I Did:

1. Step 1) Extracted data to pull data which is non blank both for date and value for each series.
2. Step 2) Created dynamic ranges for X & Y axis with OFFSET function named as Range (Y-Axis) and Xaxis for graph. (The same can be accessed through name manager)
3. Step 3) Insert a blank graph. Go to select data, add a new series, in values give the refrence oy Yaxis with sheet name e.g. Sheet1!Range. The change the horizontal axis to Sheet1!Xaxis.
4. Step 4) That's it.

did you use my defiened names? to create the "selectable tests" ?

so i can change number of tests (Last X) or can i delete them (the defined names)

No I did mine :
For X-Axis :XAxis1
For Y-Axis: YAxis1

Later for other you can use 2,3, and so on so that you can relate easily.

okay, thanks i will try to implement this so il need a couple of hours, lol not anything near as fast as you.

Gr8, write back if you need any explanation on this. If you assume this is solved than mark the thread as SOLVED.

Hey Misrasomendra, im having a bit trouble, (Step 1).

Im trying to fill all of the data into the cells for all the tests first but:

when i change your formula the cell apears blank. and if i try to drag it it is not correctly modified.

your formula to copy is so long im having trouble understanding each "part"

The formula are array formula and must entered through Ctrl+Shift+Enter not just enter. If executed successfully than you can see {} around formula in formula bar.

Thanks, that worked jesus crist there is so much to this excel that i dont understand.

Hey again,

when i have completed step 1 for all the tests, i am haveing a bit of a problem with creating a new graph, i created Xaxis2, and Yaxis2, im not sure of they are correct, but when i try to create a new graph it says somthing about refrences. (do the names i have made also funktion dynamically (Xaxis2/Yaxis2)?

Copy the formula of Xaxis1/Yaxis1 to sheet cell and adjust accordingly for 2nd graph.

yeah thats what i tried, but im not completly sure if im doing it correctly

IN A2, and B2 you can see the formula for Xaxis2/Yaxis2

i placed it there so i had easy edditing then copy it into the "define name"

See the file, I had editted the formula for XAxis2/Yaxis2 in name manager.

Hmm, what was my mistake? i cant figure out what you did differently

You were selecting E2:E5 in row and height component I selected upto 25.

okay, so for future reference lets say i sometime in the future pass 25, this wont be changed dynamically right? so i could put it to 400 or 4k etc to be sure that i dont run past the definition

Yes, put in a number which you expect to be the largest number will be.

okay thanks Misrasomendra, i am going off now so il check back on this tomorow, thanks for the help you realy been a gentleman, i hope i can figure the rest out on my own, and i think you just solved 3 of my threads with this help, i will [Solved] them tomorrow, and give you rep in every one of them. thanks!