Lookup across worksheets with variable rightmost cell in each not working

Missing_Link

New Member
Joined
May 15, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
i & welcome to MrExcel.

Are all the sheets you are interested in consecutive?
 
Upvote 0
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!
 
Upvote 0
Ok, how about
Excel Formula:
=BYROW(VSTACK(Sheet1:Sheet4!F2:Z2),LAMBDA(br,TAKE(FILTER(br,br<>"",""),,-1)))
 
Upvote 0
Solution
Ok, how about
Excel Formula:
=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.
 
Upvote 0
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)))
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top