Lookup across worksheets with variable rightmost cell in each not working

[Missing_Link]

Hi,

I am using a formula as follows to pull out the rightmost result in the second row in the column range F to Z:

=LOOKUP(2,1/('ABC1'!F2:Z2<>""),'ABC1'!F2:Z2)

This works as expected, but I have a number of worksheets, and I'm looking to pull out the rightmost result in the second row in the column range F to Z from each worksheet into a single sheet.

The above works fine for a sheet specifically named ABC1.

I have a lot of worksheets to pull the value from so I created a list of worksheets to build a formula against. e.g:

Sheet Ref
ABC1
ABC2
ABC3

However, as soon as I replace ABC1 with a cell value (assume "ABC1" is A2) in both parts of the formula, it doesn't work.

I've tried INDIRECT, XLOOKUP, etc. but I think I should be able to do what I need with a simple lookup.

The formula only returns the actual lookup location text: ABC1'!F2:Z2 and not the value in that location.

Could anyone help with the correct formatting of this formula please ?. I am familiar with the use of & and " but I just can't see the issue with this one...

All worksheets are in the same workbook.

Many thanks,


ML

 

[Fluff]

i & welcome to MrExcel.

Are all the sheets you are interested in consecutive?

 

[Missing_Link]

i & welcome to MrExcel.

Are all the sheets you are interested in consecutive?

Thank you!

Yes, all sheets are consecutive, have no spaces in the worksheet names. Also, the values in each worksheet being retrieved are text.

I hope this helps. Usually I don't get stuck with this kind of a problem, but I just can't see the issue!

 

[Fluff]

Ok, how about

=BYROW(VSTACK(Sheet1:Sheet4!F2:Z2),LAMBDA(br,TAKE(FILTER(br,br<>"",""),,-1)))

 

[Missing_Link]

Ok, how about

=BYROW(VSTACK(Sheet1:Sheet4!F2:Z2),LAMBDA(br,TAKE(FILTER(br,br<>"",""),,-1)))

Thanks for your help, I will take a look at this.

I had originally envisaged a single Excel table with one row per worksheet name, and building out a formula which just referenced the worksheet name in each row. I already have a macro which has listed all of the worksheets.

 

[Missing_Link]

Hi @Fluff ,

A slightly amended worksheet referemce and it worked perfectly - thank you!

The formula below pull the rightmost value on row 2 in consecutive worksheets 1 to 100 in the same spreadsheet:

=BYROW(VSTACK('ABC001:ABC100'!F2:Z2),LAMBDA(br,TAKE(FILTER(br,br<>"",""),,-1)))

 

[Fluff]

Glad to help & thanks for the feedback.