Moving data based on column data

Hi,

I am trying to create a spreadsheet to use for reporting in machine problems on our site.

I have a worksheet where I want to enter data relating to all the calls on our 2 machines and depending on the system id entered, copy the data in the rows relating to that system id, to another part of the same worksheet.

For example, in the attached spreadsheet there are 2 system id listed along with data relating to callout date, time, etc. To the right of this are 2 further reports which are left blank, want I want to do is, as data is entered into the currently populated form, depending on which system id is used, all the data entered automatically populates either one of the currently empty forms, so that at the end of each month, each of the 2 forms only have data relating to each system, this way I can print out the either form depending on which system we want to check.

(I hope that all makes sense)

Cheers

Tony

Here you go Tony!

You can hide Columns J, M, and W if you want.

J automatically ranks each system ID separately, M and W count them up and countdown to 1, and everything else matches the rank with appropriate row #s and pulls it.

Attachment 259394

I love building these things.

Hi daffodil11,

Thank you very much for this, it works exactly as I was hoping. I am trying to get to grips with your formulas so that I can better understand what is going on but the only thing I can make sense of is you use of the OR logical statement in J2. Would you mind explaining how it all works as there is a chance that I will need to add a third machine to the report at some point.

Many thanks

Tony

Originally Posted by daffodil11

Here you go Tony!

You can hide Columns J, M, and W if you want.

J automatically ranks each system ID separately, M and W count them up and countdown to 1, and everything else matches the rank with appropriate row #s and pulls it.

Attachment 259394

I love building these things.

I slammed my finger in a car door yesterday so my typing is slow.

I'm putting together a little tutorial for you and will post it later today.

Thanks, I am very grateful.

Hope this answers some of your questions!


Attachment 259632

Thanks, I understand parts of it but the formulas are still complex at this stage. I am still trying to pick up what I can about the more complex capabilities of Excel.

I have modified some of the formulas and replaced the absolute system id number with cell locations so that if the serial numbers change, then all the formulas change with it. I have also added some button to print and to save to PDF.

I still can't believe just how powerful excel is.

Thanks again.

Tony

Hi daffodil11,

I have found 1 small issue that I am hoping you could assist with. If I leave the Callout Date and Callout Time field blank (which may sometimes be the case), the fields in the report get populated with a date of 00-Jan-00. Could you assist with having those cells only remaining blank if no data is entered?

Thanks

Tony

Originally Posted by daffodil11

Here you go Tony!

You can hide Columns J, M, and W if you want.

J automatically ranks each system ID separately, M and W count them up and countdown to 1, and everything else matches the rank with appropriate row #s and pulls it.

Attachment 259394

I love building these things.

Sure. In O2 just change the formula to:

=IF(IFERROR(INDEX($A$2:$J$30,MATCH(1,($M2=$J$2:$J$30)*(200943=$A$2:$A$30),0),COLUMN(B3)),"")=0,"",IFERROR(INDEX($A$2:$J$30,MATCH(1,($M2=$J$2:$J$30)*(200943=$A$2:$A$30),0),COLUMN(B3)),""))

Then re-enter it as an array using Ctrl+Shift+Enter and copy and paste it to down to O30 and over to P as well.

Thanks again mate, worked a charm.