Populating one sheet from another sheet dynamically??

[Canadian911Guy]

So, essentially here is what I do:

I enter data into SHEET 1 multiple times per day ... one line example here:
Capture.JPG

Sometimes "Report Only" is replaced by "Medical Aid" or "Lost Time".

WHAT I NEED TO HAPPEN:

When I enter data into SHEET 1 that is either a MEDICAL AID or a LOST TIME, I need certain of those cells I populate in SHEET 1 to copy to SHEET 2 (=+'SHEET 1'!A1) ... problem I am having is that A1-O1 will always be changing, once A1-O1 is filled with the data, I enter the new data in A2-O2 and so on.

How do I write the formula on SHEET 2 to capture only MEDICAL AID / LOST TIME entries that are made on SHEET 1 when the referenced cell(s) keep changing?

P.S.: I would upload the sanitized workbook, but it is over 9MB and won't attach.

Thanks in advance for all your help and suggestions to come!

[Canadian911Guy]

Did I break the internet?

[sandy666]

Attach a sample workbook not a picture. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Canadian911Guy]

Thank you Sandy, I didn't think of making a Mock-up workbook.

It is attached.

To restate what I need it to do:

1.) When I enter new information on "Tracking" sheet in the next empty row, I need that information to populate on the next empty row on "OI" sheet, but only if it is a "Medical Aid" or a "Lost Time". Also, sometimes a "Report Only" may change to a "Medical Aid" or "Lost Time" and then at that time of change the information from "Tracking" sheet would need to be populated on the "OI" sheet when it does change.

2.) When a "Medical Aid" or "Lost Time" entry changes to "Completed" on the "Tracking" sheet, I need the corresponding entry on the "OI" sheet to be struck-through as in the example.

I hope this makes sense.

THANKS!

[sandy666]

How about PowerQuery (add-in for Ex2010) ?

[Canadian911Guy]

That looks like a powerful little tool ... I have asked my IT department to install it so I can start playing with it!

Thank you!!

[sandy666]

"little tool" good joke

[reagan.kha]

Attached is the solution using formula and conditional formatting.

[Canadian911Guy]

Hi, thank you for the formula and help!

It works perfectly in the mock-up, but when I transfer the formula to the actual sheet I need to use it in, it doesn't work at all.
I have attached a picture of the actual "Tracking" sheet for reference.
I have also copied my modified sheet to the attached mock-up (RED OI TAB), with my copied formulas. Please ignore the file references in the formula, I forgot it would do that.

Did I make a mistake in copying?

[Canadian911Guy]

Here is a better mock-up of the copied formulas I made ...

[sandy666]

Here is simple example what you can do with PQ
I didn't add correct headers (too much type)
so...

• add new line in your source
• go to red tab and right click on the query table - refresh

Note:
Because it's very simple example, so...
In source in Type of Investigstion cannot be a blank cell
you can use space or back your source table up by handler
I'm too lazy to do it

And No, you can't change headers in query table. It should be done via PQ

any questions?

[Canadian911Guy]

Ok, I think I sanitized this original (Test Copy #2) properly ...

I want to thank "reagan.kha" for the formula that works perfectly in the mock-up but not in my original (Test Copy #2).
I transposed the formula from the mock-up and entered them into the OI Sheet of my original using "CTRL / SHIFT / ENTER" and then copied it down to cells A1000 - O1000.
The formula in the original OI Sheet does return data, but not the way I need it to and not the data I want.

To reiterate what I need the formula in the OI Sheet to do:

1.) When I enter new information on "Tracking" sheet in the next empty row, I need that information to populate on the next empty row on "OI" sheet, but only if it is a "Medical Aid" or a "Lost Time". Also, sometimes a "Report Only" may change to a "Medical Aid" or "Lost Time" and then at that time of change the information from "Tracking" sheet would need to be populated on the "OI" sheet when it does change.

Right now, it is copying some "Report Only", some Medical Aid" and some "Lost Time" ... I am not sure why because it works perfectly in the mock-up and only copies MEDICAL AIDS & LOST TIMES, exactly like I want it to ... but won't work in the original.

Any help would be appreciated.

Thanks!!

[sandy666]

change C5 to C1 and try again

[Canadian911Guy]

Tried, no change ...

[sandy666]

Sheet OI
Cell C2
Formula:
=IFERROR(INDEX(Tracking!C$1:C$1000,SMALL(IF(Tracking!$C$1:$C$1000="Medical Aid",ROW(Tracking!$C$1:$C$1000),IF(Tracking!$C$1:$C$1000="Lost Time",ROW(Tracking!$C$1:$C$1000))),ROW()-1)),"")
and of course CSE

[sandy666]

you can try this one
=IFERROR(INDEX(Tracking!A$1:A$1000,SMALL(IF(Tracking!$C$1:$C$1000="Medical Aid",ROW(Tracking!$C$1:$C$1000),IF(Tracking!$C$1:$C$1000="Lost Time",ROW(Tracking!$C$1:$C$1000))),ROW()-1)),"")
in A2 on sheet OI plus CSE, then drg it to the right and next down

but you need correct date (format) in column E (sheet OI) because there are zeroes what give you 00/01/1900 and some more columns with dates

[Canadian911Guy]

DEAR DEITY!! That did it! It works perfectly now!

THANK YOU, sandy!!

[sandy666]

You are welcome

Do you know why?

[Canadian911Guy]

Honestly, I do not know why it works ... I just know it does work exactly as I need it to now.

Care to explain why?

[sandy666]

the best way is:
index.jpg
if you click on row_num then hit F9 you will see row number and from this row index should show value array

ups. not enough english to explain it correctly
see here
INDEX()

Microsoft know english better than me

[sandy666]

[deleted by me]