End(xlUP) ?

[MGumbrell]

I have "Sheet1" that contains a range "W14"

I have "Sheet2" that contains a range "A:A".
Within Range "A:A" is sequential numbering with the last one being the most up to date and always being added to.

I need "Sheet1" Range "W14" to be populated with the with the last entry at the bottom of "Sheet2" Range "A:A" When "Sheet1" is initialized.

I belive that I need to locate the last empty cell form the bottom of column A upwards End(xlUp) + 1 to give me the first populated cell that can then be referenced in "Sheet1" range "W14"

I hope that I have explained that clearly.
Thanks, Matt

 

[Bilbo_Baggins_Esq]

MAX(Sheet2!A:A) will find the Maximum value in A:A, assuming the last entry has the latest time in the column.

MATCH(MAX(Sheet2!A:A),Sheet2!A:A,1) would return the row number for the maximum value.

 

[Bilbo_Baggins_Esq]

Are you needing the value in Sheet 2 column A, or a different column based off the maximum value in sheet 2 column A?

 

[MGumbrell]

Bilbo

I didn't explain myself very well so I have attached a file that I am hoping will explain things better. Having said that your response has shown me other ways to consider.

From the attacment you will probably notice that my enquiry has grown. Its a bit of a project that I am trying to break down into segments.

 

[Bilbo_Baggins_Esq]

Let me see if I have this understood.

On Open, the workbook needs to find the maximum value in Sheet2!A:A ("Ref") and make a new entry at the end of the list with one greater number than the existing maximum

Then, that new maximum needs to populate into cell Sheet1!B2

Then the user will Populate B4:B5

Upon completion of the user entries, the values in B4:B5 are written to sheet2 in columns B and C adjacent to the new maximum "Ref".

Then the user input section is cleared on Sheet1

is that right?

 

[Bilbo_Baggins_Esq]

Well, as an exercise, I did come up with a formula you can place in B2, and then automate the rest with code.

{=CONCATENATE(IF(LEN(MIN(IF(ISBLANK(Sheet2!A:A)=TRUE,ROW(Sheet2!A:A),""))-2)=1,"PB000",IF(LEN(MIN(IF(ISBLANK(Sheet2!A:A)=TRUE,ROW(Sheet2!A:A),""))-2)=2,"PB00",IF(LEN(MIN(IF(ISBLANK(Sheet2!A:A)=TRUE,ROW(Sheet2!A:A),""))-2)=3,"PB0","PB"))),MIN(IF(ISBLANK(Sheet2!A:A)=TRUE,ROW(Sheet2!A:A),""))-2)}

note this is an Array formula so when you press enter, it needs to be CTRL+SHIFT+ENTER

Of course, this assumes there would be no breaks in the numbering sequence in "Ref" and there is a column heading.

 

[MGumbrell]

Thank You

There will be no break in the numbering sequence as I don't intend the user to view this sheet.

That is some formula. No chance of me stumbling on this one through trial and error.

Regards, Matt