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