Cannot use INDIRECT in the following formula

[DJ_DEF]

I have the following formula that works very nicely. It finds the column number of the first value greater than 0 going from right to left in the range.

=LOOKUP(2,1/(MASTER_PLAN!C3:BZ3>0),COLUMN(MASTER_PLAN!C3:BZ3))

I would like to use an INDIRECT in place of the first range. If I use it in the second range only, it works. if I use it in the first, I get an error.

Ideas?


Thanks in advance!

[Jonmo1]

Can you post what you tried that didn't work?
And describe which parts of the original formula are being replaced with cell references?

[Richard Buttrey]

Hi,

Would you share with us the formula with the Indirect that you are trying?

[Glenn Kennedy]

In what way are you wanting to use INDIRECT? Picking up the sheet name from another cell, or what?

[Jonmo1]

This works fine for me with the sheetname (MASTER_PLAN) written in A1

=LOOKUP(2,1/(INDIRECT("'"&A1&"'!C3:BZ3")>0),COLUMN(INDIRECT("'"&A1&"'!C3:BZ3")))

[DJ_DEF]

The following returns the range properly.

INDIRECT("MASTER_PLAN!C"&ROW(INDIRECT(FINDIT(LEFT(T5,5),TRAILER_PLAN)))&":"&IF(COLUMN(INDIRECT(AH5))-1<=26,CHAR(COLUMN(INDIRECT(AH5))-1+64),CHAR(INT((COLUMN(INDIRECT(AH5))-1-1)/26)+64)&CHAR((MOD(COLUMN(INDIRECT(AH5))-1-1,26))+65))&ROW(INDIRECT(FINDIT(LEFT(T5,5),TRAILER_PLAN))))

FYI - FINDIT is a user defined function that I found online. It returns a cell address

So if I take the above and paste into the latter part of the above formula:

=LOOKUP(2,1/(MASTER_PLAN!C3:BZ3>0),COLUMN(INDIRECT("MASTER_PLAN!C"&ROW(INDIRECT(FINDIT(LEFT(T5,5),TRAILER_PLAN)))&":"&IF(COLUMN(INDIRECT(AH5))-1<=26,CHAR(COLUMN(INDIRECT(AH5))-1+64),CHAR(INT((COLUMN(INDIRECT(AH5))-1-1)/26)+64)&CHAR((MOD(COLUMN(INDIRECT(AH5))-1-1,26))+65))&ROW(INDIRECT(FINDIT(LEFT(T5,5),TRAILER_PLAN))))))

I get the desired result "65"

When I use the same INDIRECT and replace the first range i always get an error. (That happens whether the INDIRECT is used or nor for the second half of the formula.

=LOOKUP(2,1/(INDIRECT("MASTER_PLAN!C"&ROW(INDIRECT(FINDIT(LEFT(T5,5),TRAILER_PLAN)))&":"&IF(COLUMN(INDIRECT(AH5))-1<=26,CHAR(COLUMN(INDIRECT(AH5))-1+64),CHAR(INT((COLUMN(INDIRECT(AH5))-1-1)/26)+64)&CHAR((MOD(COLUMN(INDIRECT(AH5))-1-1,26))+65))&ROW(INDIRECT(FINDIT(LEFT(T5,5),TRAILER_PLAN))))>0),COLUMN(INDIRECT("MASTER_PLAN!C"&ROW(INDIRECT(FINDIT(LEFT(T5,5),TRAILER_PLAN)))&":"&IF(COLUMN(INDIRECT(AH5))-1<=26,CHAR(COLUMN(INDIRECT(AH5))-1+64),CHAR(INT((COLUMN(INDIRECT(AH5))-1-1)/26)+64)&CHAR((MOD(COLUMN(INDIRECT(AH5))-1-1,26))+65))&ROW(INDIRECT(FINDIT(LEFT(T5,5),TRAILER_PLAN))))))

I get #N/A

The original working query:

=LOOKUP(2,1/(MASTER_PLAN!C3:BZ3>0),COLUMN(MASTER_PLAN!C3:BZ3)) Result is 65

Thanks

[Glenn Kennedy]

Likewise, except that I put it in B7!!

=LOOKUP(2,1/(INDIRECT("'"&B7&"'!C3:BZ3")>0),COLUMN(INDIRECT("'"&B7&"'!C3:BZ3")))

[Glenn Kennedy]

OMG!!! No-one is going to want to try to wade their way through that lot without seeing it in context!! Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[DJ_DEF]

Sorry. Sure Thing

[Glenn Kennedy]

What is this rather (!!) complicated formula trying to do for you?

[Jonmo1]

Wow, That's quite a formula there. There's probably a much easier way.
Honestly, you probably don't need indirect to begin with.

You pretty much only need indirect when it's the Sheet's NAME that needs to be varied by a cell reference.
But it seems in reality you want to vary the beginning and ending Columns to do the lookup with.
Right?

Can you describe with words, how this part
ROW(INDIRECT(FINDIT(LEFT(T5,5),TRAILER_PLAN)))
Finds the beginning Column letter?
Perhaps share the code for the FINDIT udf, and what is Trailer_Plan.. A named range?

And what exactly is in AH5, how does that help to find the last column ?

[DJ_DEF]

FINDIT is
Function FINDIT(v As Variant, r As Range) As String
FINDIT = ""
For Each rr In r
If rr.Value = v Then
FINDIT = rr.Address
Exit Function
End If
Next
End Function

The beginning column letter is always "C". I then find the last column before the next truck departure. CA3SCHEDULE.png
The yellow in the picture is 0 and the green are 1. In the long run I am trying to look up the time from the last one before the next departure. That's why I go from right to left.

AH5 which equals CA3 is cell where the next truck is departing.

[Glenn Kennedy]

Nice, but where's the spreadsheet?

[Jonmo1]

Is CA3 manually typed into AH5, or is that a formula as well?
If it's a formula, what's that formula?

This is leading somewhere, so go with it..


And an actual workbook would be nice as Glenn said.

[DJ_DEF]

EQUIPMENT_UTILIZATION_SAMPLE.xlsm

The file is attached. I removed nearly everything to get it down to the right size.

Thanks again.

[Jonmo1]

OK, i think a completely new approach is called for here.

Is the overall goal to make a range that is on the row where Findit finds the value.
And begins in column C and ends in the last column <>0 of that row found by findit?

Then use that range in the initial lookup formula
=LOOKUP(2,1/(MASTER_PLAN!C3:BZ3>0),COLUMN(MASTER_PLAN!C3:BZ3))

Is that about right?

[Jonmo1]

Here's a stab...

Change your FINDIT Code to

Please Login or Register  to view this content.

Then use
=LOOKUP(2,1/(MASTER_PLAN!C3:FINDIT(LEFT(T5,5),TRAILER_PLAN)>0),COLUMN(MASTER_PLAN!C3:FINDIT(LEFT(T5,5),TRAILER_PLAN)))

[Jonmo1]

Correction, the formula would be

=LOOKUP(2,1/(INDEX(MASTER_PLAN!$C:$C,ROW(FINDIT(LEFT(T5,5),TRAILER_PLAN))):FINDIT(LEFT(T5,5),TRAILER_PLAN)>0),COLUMN(INDEX(MASTER_PLAN!$C:$C,ROW(FINDIT(LEFT(T5,5),TRAILER_PLAN))):FINDIT(LEFT(T5,5),TRAILER_PLAN)))

[Jonmo1]

You're welcome.
And thanks for the Credit.