Using an "indirect" formula without a cell reference

[crystalneedshelpplzthnx]

Hello,

I'm not sure if something like this is possible, but if "Indirect" is possible, I'm sure there is a way to achieve my goal. Here is my dilemma.

There is a formula I need to use: (formula 1)
=T:T&" "&IF(AA:AA<16,1,IF(AND(AA:AA>15,AA:AA<31),2,3))


Example output would be:
AM DIY 3.2OZ DÉC 1


I am able to achieve formula 1 by using another formula (it ends up looking just like formula 1): (formula 0)
="T:T&"" ""&IF(AA:AA<"&AU3&",1,2)"
AU3 changes depending on certain criteria


And then I line it up where the Example output would be using this formula (it ends up looking just like formula 1): (formula 2)
="="&VLOOKUP(T3,AE:AZ,22,0)


How do I make formula 2's output look like the output of formula 1?

Note:

I'm including pictures because the file it too large.

https://www.amazon.com/clouddrive/share/qRrFtZmS53r9NsvVO5gJ1K0nixHGR6GpYSAvGiUBOpi

Thank you!

 

[Behelith]

I changed the values from cells AZ3 to AZ19 from formulas to the results of those formulas. Then the lookup in CA can find them.

Updates sheet here : https://www.behelith.net/Download/Indirect with no cell reference.xlsx

Let me know if that works out.

 

[crystalneedshelpplzthnx]

I changed the values from cells AZ3 to AZ19 from formulas to the results of those formulas. Then the lookup in CA can find them.

Updates sheet here : https://www.behelith.net/Download/Indirect with no cell reference.xlsx

Let me know if that works out.

It is definitely doing something different because this IS how I want AC to look. But for the formula to be acting correctly AC should equal W. In W I am doing to formula the long way. The formula changes for each group change in T, depending on that particular categories breakdown (Which is listed AE - AY).

Depending on how many items can fit on a shelf if how the categories breakdown. So for instance:


FA 2OZ REG


has 4 shelves. on the first shelf, 18 items can fit, on the 2nd shelf, 20 items can fit, on the 4th shelf, 20 items can fit

So if this changes I don't want to have to find it in columns S-W to update the formula manually in column W. I want to change the criteria in columns AI-AM and everything else updates.

Crystal

 

[crystalneedshelpplzthnx]

As the problem currently sits, I'd copy AC:AC and paste value W:W.

This will allow me to avoid updating W:W group by group, or one by one.

The current solution does not update W:W automatically as I was attempting to.

Thnx for all the help! Much Appreciated!

Crystal