Display information (Cell A through E) in its entirity From Sheet 3 on Sheet 1

Hello,

Here is what I am trying to accomplish.

On Sheet 3 I have several entries on the page. In Column A I have a drop down list with several items in that drop down. Example "Internal Strategic Goals (ISG). When the user selects this option the column range from A to E changes color. In Coloum E I have the latests updates with a date in front of them example:
Row 2
2/1/16 - Information
1/25/16 - Previous information
1/22/16 - etc

However, I have a summary page that I only want to show columns A through E only if the item is a Internal Strategic Goal (ISG) with the latest update.

Keep in mind, I have additional columns that have the same Internal Strategic Goals (ISG), but they could have a date that is different for the latest entry.

Row 10
2/1/15 - Information
1/25/15 - Previous information
1/22/15 - etc

So in this case I would only want to see the first update from above since it has the latest date. Now if the entry changes to a date that is newer, it would replace the one above.

I only want to show the top two entries. If there are three entries in the sheet that all fall under on the same date. Maybe pick the top two entries only based on where they fall via the rows.

Hopefully this makes sense.

Attach a sample workbook. 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 use the paperclip icon to open the upload window.

View Pic

Here is a solution that involves a helper column (F8 and down) on Sheet 3 using the following formula:

Please Login or Register  to view this content.

The following formula which can be pasted into Executive Summary D7, then dragged across and down will then give the results requested in post #1:

Please Login or Register  to view this content.

Let me know if you have any questions.

Originally Posted by JeteMc

Here is a solution that involves a helper column (F8 and down) on Sheet 3 using the following formula:

Please Login or Register  to view this content.

The following formula which can be pasted into Executive Summary D7, then dragged across and down will then give the results requested in post #1:

Please Login or Register  to view this content.

Let me know if you have any questions.

It took me a few to understand what was going on here, but it looks like it working like a charm!

Except for one little issue:

It is pulling the correct data, newest date of entry. However, if there is more than one entry in the field, it is pulling all of the entry, and it is displaying it like so:

2/5/16 - Test Data2/1/16More test data2/1/16Yup more data

Is there a way to just pull the latest and greatest in that field?

Again thank you very much.

I have attached another version so you can see what I am seeing.

try below in C11
=IF(COLUMNS($A$1:A1)=5,INDEX('Sheet 3'!$F$8:$F$10,MATCH(LARGE('Sheet 3'!$F$8:$F$10,ROW($A1)),'Sheet 3'!$F$8:$F$10,0)),INDEX('Sheet 3'!A$8:A$10,MATCH(LARGE('Sheet 3'!$F$8:$F$10,ROW($A1)),'Sheet 3'!$F$8:$F$10,0)))

drag down and across, keep date formatting for G11 to G13


if you want to display data as it is then in your current worksheet select G11 to G13, go to home alignment and click wrap text

That was a good call to wrap the "date puller code" inside of the IFERROR function. I have tested increasing the range of both formulas to 1000 in the Clean2.xlsx file and I am getting the results that I would expect, i.e. different data for rows one, two and three. I don't see anything about the formulas that looks wrong, however it is referencing a sheet (SSB 2016) that is not in the Clean2.xlsx file, so I can't be sure what is happening. I expect that the file you are now working with is proprietary, however if it isn't I would suggest uploading a portion. If it is then perhaps running "Evaluate Formula" (Formula tab) might point out the problem.
Let me know if you have any questions.

The problem can be corrected by changing the formula so that it takes the row number into consideration as well as the date updated. Here is the array formula* that has been applied to the RIB on the Executive Summary sheet of the attached file:

Please Login or Register  to view this content.

*Note that to apply this formula to the other sections of the Executive Summary you'll need to activate it by holding the Ctrl, Shift and Enter keys down at the same time.
Here is a copy of Clean_Ver4 with the formula applied to RIB: Copy of Clean_Ver4.xlsx
Let me know if you have any questions.

Going to enter in a few more test pieces

Let me know the out come.

Sorry to hear about the problem. I just tested the Clean_Ver4.xlsx file by adding the following to DSB2016!E8: "3/9/16 - Entry after Last Entry" and everything worked as I would have expected. Let me ask however if the updates are added above the older information? When I tested by adding "3/9/16 - Entry after Last Entry" below "12/31/15 - Last Entry", in cell E8, the Executive Summary sheet didn't change. Perhaps someone can help find the source of the problem if you upload a sample of your current worksheet.

I will upload a version shortly..... It is only occurring with the one sheet. Very frustrating. I am wondering if this is even worth pursing... maybe just having a counter with a nice graph will work!