Offset mixed reference formula on dynamic range

[PingTing]

I have a range (N6:AS6) where I search for the header "Planned". Each time I find the heading, I want to input a mixed reference formula in row 8 to 208 of that column. I have figured out how to find the header and put in the formulas, but I can't get the mixed reference part of the formula to work.

Here is my code:

Please Login or Register  to view this content.

So if you look at the picture, in cell V8, it should have the formula =XLOOKUP(N$5,$N$1008:$ADB$1008,$V1011:$ADJ1011) and then in cell AG8, it should be =XLOOKUP(Y$5,$N$1008:$ADB$1008,$V1011:$ADJ1011). So I'm not getting the dynamic column part to work. What am I missing?

Screenshot 2021-09-29 172021.jpg

[TMS]

Not sure what you'r expecting from us here. A picture is not generally very helpful BUT if you are going to post a picture it would be useful to make it a size that we can actually read.

Please see the yellow banner at the top of the page.

[PingTing]

Sorry Trevor. I have attached sample workbook now. Thanks.

[TMS]

Try:

Please Login or Register  to view this content.

[PingTing]

Thanks, Trevor. That worked perfectly. One question - I see you commented out the n in "Next 'n". Did you mean to do that or was that a mistake? It worked with it commented out though.

[TMS]

You're welcome. Thanks for the rep.

I see you commented out the n in "Next 'n".

Yes. You don't actually need to specify the variable on the Next statement. You can just use "Next" . And I seem to recall reading somewhere, sometime, a long time ago, that it was more efficient to leave it out. By commenting it out, you have the best of both worlds; efficiency plus you still have a reference for the loop.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[PingTing]

Marked as Solved. Thanks again. Just brilliant!!!

[PingTing]

One thing, Trevor. How do I run it from another sheet? In my working workbook, I have to run it from a different sheet (via a button).

So in my test book attached here, I tried adding Sheet1 to all the ranges, and then running it from Sheet2. But then it didn't work. How can I get around that?

Here's the code now;

Please Login or Register  to view this content.

I get the error "Run-time error '1004': Method 'Range' of object '_Worksheet' failed"

[TMS]

I'm not in a position to look at the workbook or test your code. However, it may help to change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

Note that Sheet1 is not necessarily the same as Sheets("Sheet1")

[PingTing]

Thanks, Trevor. Worked perfectly again. But then a new error came up (only in the working workbook).

I got the error "Run-time error '1004': Application-defined or object-defined error".

Am I missing something again?

Test workbook code where everything works fine (runs from either sheet):

Please Login or Register  to view this content.

Working workbook code (doesn't work at all):

Please Login or Register  to view this content.

[TMS]

You've added an extra IF to the formula. It looks, to me, as if it's missing a close bracket at the end (before the double quotes). Does it compile?

[PingTing]

Well then. Don't I feel like an idiot... So embarrassing. Thanks, again. This has been such a great help.

[TMS]

Don't I feel like an idiot...

You may think/say that, I could not possibly comment.