Formula drag across right is not working!

[Anbuselvam K]

Dear Excel Genius

The below formula in the excel table, drag across right is not updating correctly.

=LET(q,Consumption[Consumption RM01],p,Purchase[Price RM01],s,Purchase[Qty RM01],sa,SCAN(0,s,LAMBDA(a,v,SUM(a,v)))-s,qa,SUM(q)-sa,
by,BYROW(SCAN(0,q,LAMBDA(a,v,a+v)),LAMBDA(v,LET(qa,v-sa,SUM(IF(qa>0,IF(qa>s,s,qa))*p)))),INDEX(by-IFERROR(SMALL(by,SEQUENCE(ROWS(q),,0)),),ROWS(HU$8:[@[Cost of RM01]])))

See the attached sheet cell HU8 Formula, I want to drag the formula across the right side till LP8 (100 columns)

The problem is Purchase[Price RM01] & Purchase[Qty RM01] is not updating as it is not positioned in the line. it is located in the alternate column.

How do I drag across with proper formula updation?

I am expecting your help. I really appreciate any help you can provide.

 

[p45cal]

Difficult with table structured references which you'll want to keep.
Any chance of rearranging the columns of the Purchase table from this:
Price RM01 Qty RM01 Price RM02 Qty RM02 Price RM03 Qty RM03 Price RM04 Qty RM04 Price RM05 Qty RM05
to the likes of this:
Price RM01 Price RM02 Price RM03 Price RM04 Price RM05… Qty RM01 Qty RM02 Qty RM03 Qty RM04 Qty RM05…
Then you should be able to copy across

 

[Anbuselvam K]

Dear @p45cal Thanks a lot for your valuable reply.

I have added Price first then Qty to achieve the formula to drag across the right side.


But entering the purchase price and quantity is getting confused as the respective columns are located 100 columns distance.

Is there any possibility to filter horizontally to get the respective price and the quantity in the next column for all the RM?

1) You can change the structure for easy entry and get the same cost in the cost of all the RM

2) Is there any way to avoid the table of Costs of RM01... RM100? and I can get the Sum of (RM01 to RM100) RM Cost per KG in Column "I" with one formula.

Expecting Your support.

 

[p45cal]

I'm having difficulty working out what you want.
Where does your data start, how do you want it to end up and where?
There seem to be far too many tables.
How does the data get into Excel?

 

[Anbuselvam K]

RM Purchase sheet having the purchase of the raw materials (Date wise Price & Qty)
Temporary and Permanent sheets are having the formulations of products (Test) with % of raw materials.
Production Sheet,
Table Production, Production KG in Column "G", Column U to DP % of raw materials uses in the particular products.
Table Consumption, Column DU to HP are the raw materials used in kg. (Production KG * % of RM)
The final table Cost, Column HU to LP finds each production cost by FIFO method.

Note: We are buying 100 raw materials ==> Producing many products using the raw materials with Different mixing proportions ==> selling to the customers

We want to know the cost of each production to find out the profit (Cost must be FIFO Method Only)