Row offset formula assistance.

[milo1984]

Hi

I need to adjust the below formula to offset the rows in my two tabs 'Booking In' & 'Booking Out ITFC' in my new sheet in the next available blank row. at the moment, it is inserting it into the corresponding row in the new sheet so not in chronological order when new data is added.

Please Login or Register  to view this content.

I would be very grateful for any assistance possible.

Many thanks in advance.

[Andrew-R]

By the looks of it, at the moment your formula will list all of the entries on the Booking In sheet, and then all of the entries on the Booking Out sheet - are you saying that you want to intersperse them so that they're all listed in chronological order?

[NBVC]

Unless you have some sort of date/time column which allows you to determine the chronological order of entry, then you can't do what you want with a formula... if I understood correctly.

[milo1984]

Hi Andrew

Many thanks for your response.

At the moment, it takes the entries in Booking In and Out sheets and lists them in a new sheet (where the formula is located). I have attached a trial workbook to show what is happening. The entries in Booking In are inserting in the list instead of attaching onto the end.

Trial workbook.xlsx

Many thanks once again in advance.

[milo1984]

Hi NBVC. There will be a date in the booking in/ out sheets which will be offset when the barcode is entered. I have got the same formula for Column B and C...

I hope that this answers your point.

[Andrew-R]

Milo,

So, on your trial sheet, if I changed the date on the 2nd booking-in entry to 30/8/12 you'd want the history sheet to list the 1st booking-in entry, then the booking out entry and then the 2nd booking-in entry, is that correct?

Any formula would, as NBVC points out, have to work on that date column. So even if we modified your formula in the trial workbook to look at dates the output would still be exactly as it is with the inputs you've provided, as there's no data held on which order the entries were made in.

If you do want a log of changes made to either sheet then you'd almost certainly be better off using a macro - would that be acceptable?

[NBVC]

If we can assume that you want to chronologically order the barcodes (hopefully), then to avoid VBA we can use a helper column or 2.

So, say in History sheet, choose an empty column like N, and in N2 put your formula from column A:

Please Login or Register  to view this content.

copied down

and in adjacent column enter:

Please Login or Register  to view this content.

copied down.

Then in A2 use formula:

Please Login or Register  to view this content.

in B2:

Please Login or Register  to view this content.

and in C2:

Please Login or Register  to view this content.

each copied down.

Does that work for you?

[milo1984]

Hi

That is fantastic. Thank you so much for your help. and in such a short space of time.

I really appreciate it.

[milo1984]

Hi Andrew and NBVC

Having tried your method, I have come across a reason for me not to be able to use it.

The purpose of this is to track the movements of barcodes. The same barcodes will be booked in and out so putting them in order of barcode number won't work. I changed the small to now() in VBA so that it tracks changes by date and then doing a vlookup on the barcode to return the date into the History tab. This is not working because it is finding the first date and returning it for all the duplicate records.

I don't mind if we need to use VBA to do this. I could only find subs though. I need a private sub worksheet_change etc....

Is this possible?

Many thanks for your help once again.

[NBVC]

Yeah, I think your best bet is the event macro, but unfortunately VBA is out of my scope on these forums.. I will have to defer it to a VBA expert....

[milo1984]

Thank you very much for your earlier assistance.

I will still try and adapt your formula. There must be a way to make it work.