Getting data in sporadic worksheet

[d888nc]

I have an issue where I need to report information within a worksheet that is on another worksheet. The problem I have is the referenced worksheet does not have continues information within it columns. This gets hard to read especially when you have over 70 columns. I have been tasked to make this information easier to read and all they are concerned with is the latest information. AS I’m sure you have already determined we are using Excel as a tracking database to show when patches have been installed.

Example:

A B C D E F G
1 Date 36
2 Date 54
3 Date 62
4 Date 16
5 Date 41
6 Date 12
7 Date 16


So, on my “easier to read” worksheet I need to display a specific column with the latest date it was entered.

Example:

D Date
C Date
etc…

The problem I am having is notice how column C has multiple entries, how do I ensure I get the last entry (assuming the date is descending)??

Any help would be greatly appreciated.
Thanks,
Mike

[Mallycat]

Your post is hard to follow because the columns don't line up and I can't see what you are talking about.

As a general rule when trying to do something like this, I tend to create a new sheet in my destination book, then in cell A1 of the destination new sheet, press =, then point to cell A1 of the source sheet. Make sure it is not an absolute reference (ie A1, not $A$1) and then copy the cell down and across until you have an exact copy of the source sheet. You will have 0s in the blank cells. This makes it easier to delete the rows and columns that you don't need.

By doing this, you can end up with a subset of your original sheet within your new sheet.

It sounds like one column has multiple data in it (I can't actually see this). Try looking at the left and right functions to create 2 columns

ie =left(A1,5) will give you the first 5 characters from cell A1
=right(A1,5) will give you the last 5 characters.
=FIND(" ",A1) will find the location of the space in cell A1
=LEFT(A1,D3-1) will give you all info to the left of the space
=RIGHT(A1,LEN(A1)-5) will give you all info to the right of the space.

Hope this is what you need.

Matt