Transferring the Last Piece of Data in a Column from One Worksheet to Another?

**rafolio** · 07-20-2010, 12:49 PM

Hi,

I have a little problem:

I have an excel document containing 2 worksheets. One is called 'Purchase Order File', and one is called 'Purchase Order Note'.

'Purchase Order File' is a simply a list of orders made by a business, the most recent of which is at the bottom of the list. This sheet contains values such as 'description', 'price, etc in columns for each order.

The 'Purchase Order Note' worksheet is a formatted order sheet, with the company logo, etc, which is updated each time a new order is made and printed for the company records.

What I would like to do is to take the most recent entries in each column of the 'Purchase Order File' workbook (i.e the bottom values on the page) and transfer them to cells in the 'Purchase Order Note' workbook.

I have found a formula which almost does what I want:

=INDEX(A:A,MATCH(REPT("z",255),A:A))

This formula finds the last text value in column A, I would like to know how to find the last text value in column A, in another worksheet....

Any help is much appreciated!

Thanks

**6StringJazzer** · 07-20-2010, 01:41 PM

This uses the formula you provided...

=INDEX('Purchase Order File'!A:A,MATCH(REPT("z",255),'Purchase Order File'!A:A))

This is a straightforward translation of your formula so I haven't tested in on a mockup workbook. But I have no idea what you are doing with matching 255 z's. As near as I can tell, this formula will either return an error if the MATCH fails, or 255 z's. Doesn't sound useful.

If this doesn't do what you want, post your workbook if it doesn't contain proprietary/private data.

**Marcol** · 07-20-2010, 01:53 PM

Maybe in the sheet you want to query in say cell A1

Please Login or Register  to view this content.

Provided the workbook has been saved this will return the sheets name.

Then using a UDF

Please Login or Register  to view this content.

Entered as

Please Login or Register  to view this content.

A bit convoluted but it seems to work.

Hope this helps

**rafolio** · 07-20-2010, 06:52 PM

Thanks for the quick replies guys.

6String - I have absolutely no idea what z 255's are, I just copied from a help website I found. Please tell me to remove them if you don't think they're required!

Marcol - is it really that complicated?! I really hope not as I am far from an expert with these things. Simply put, all i want to do is to take the piece of data at the bottom of a column in Sheet 1, and copy it into a specific cell in Sheet 2.. The difficulty for me is that the data at the bottom of the column has a different row number each time a value is added. Hope this makes sense?

Thanks again, it is much appreciated

**Marcol** · 07-20-2010, 07:34 PM

It's not really complicated, just novel.

Have you tried the demo workbook?

Try adding or deducting from column D in sheet2 and see the results on all sheets.

Once setup it needs no changing. The function

Please Login or Register  to view this content.

is just a way to allow easy selection of the required sheet name.

You could just use the UDF (User Defined Function) and enter it so

Please Login or Register  to view this content.

Cheers

**rafolio** · 07-21-2010, 12:17 PM

Thanks Marcol, I've tried your example and it seems to work fine.

I understand the =LastValue(D:D,"MySheetName") part, but im not sure why =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) is required? Also can this be done without the file being saved first?

Lastly, what if my cell contains text rather than data?

thanks again

**6StringJazzer** · 07-22-2010, 12:46 PM

Originally Posted by 6StringJazzer

This uses the formula you provided...

=INDEX('Purchase Order File'!A:A,MATCH(REPT("z",255),'Purchase Order File'!A:A))

This is a straightforward translation of your formula so I haven't tested in on a mockup workbook. But I have no idea what you are doing with matching 255 z's. As near as I can tell, this formula will either return an error if the MATCH fails, or 255 z's. Doesn't sound useful.

I have done a little research on this and the 255 z's does work. Here's how. It's "clever."

First, this formula is only useful for searching a column of text data. MATCH has a third argument, which indicates how you want it to match (exact match, greatest value less than sought, lowest value greater than sought). Here is it omitted, so it takes the default of finding the greatest value that is less than the match value provided as the first argument. However, there seems to be a twist. Using that default, MATCH expects the data to be in an ascending sort order. Microsoft does not document how it behaves when the data is not sorted. Because a string of 255 z's is pretty much the highest-value string possible, MATCH will never find a value greater than that so it seems to bottom out on the last row if the data is not sorted.

For numeric data, 9.99999999999999E+307 would seem to work the same way.

You may want to try this to see whether you prefer this to the one you've already tried. I've experimented and it seems to work fine without the need for any VBA.

**Marcol** · 07-22-2010, 01:35 PM

See this link

http://www.exceltip.com/st/Cell_Func..._Path/180.html

1/. The advantage in using and referencing this formula is that if you change the sheet name the UDF will update to suit, it can go in any free cell you wish, in the sheet you want to find the last value.
The workbook must be saved for this to work, because a filename doesn't exist until it has.

If you just use the UDF so

Please Login or Register  to view this content.

If you rename "Sheet1" to "MySheetName"
Then the formula must be changed to

Please Login or Register  to view this content.

If you put the sheet name in say A1 anywhere in the workbook then use the UDF so

Please Login or Register  to view this content.

the UDF will update, but if you now rename the sheet then you must match A1 to the new name.

By using

Please Login or Register  to view this content.

Updates will be automatic.

It is easier to try the permutations than explain them in words.

2/. It doesn't matter what last value in the column is, the UDF will return it.
Date, Time, Currency, etc. ... formatting will not however be matched,

3/. If however you have formatted values, try this

Please Login or Register  to view this content.

Use with

Please Login or Register  to view this content.

to get maximun benefit

Hope this helps

Transferring the Last Piece of Data in a Column from One Worksheet to Another?

LinkBack

Thread Tools

Rate This Thread

Display

Transferring the Last Piece of Data in a Column from One Worksheet to Another?

Re: Transferring the Last Piece of Data in a Column from One Worksheet to Another?

Re: Transferring the Last Piece of Data in a Column from One Worksheet to Another?

Re: Transferring the Last Piece of Data in a Column from One Worksheet to Another?

Re: Transferring the Last Piece of Data in a Column from One Worksheet to Another?

Re: Transferring the Last Piece of Data in a Column from One Worksheet to Another?

Re: Transferring the Last Piece of Data in a Column from One Worksheet to Another?

Re: Transferring the Last Piece of Data in a Column from One Worksheet to Another?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions