1

I have attached the sample sheet that I am working on for sourcing inventory from other plants to my plant-P1837.

P1837 tab requirement lists the Material No. and corresponding quantity needed. Global stock availability tab lists the availability of my required materials in all plants globally. Target tab contains the table which lists the Material No., Desc, P1837 required qty. and quantity of that material available in each plant so i can compare and source accordingly.

Appreciate your suggestions on getting this done as my requd. material list goes up to 400 lines sometimes.

Attachment

P1837 Requirements: P1837 Requirements

Global stock availability: Global stock availability

Target: Target

Improve this question
2
  • 1
    Can you please show us what you have tried and explain what the exact problem you are facing is?
    – cybernetic.nomad
    Commented Sep 11, 2023 at 20:54
  • seems like a pivot table using the data model feature would solve this
    – gns100
    Commented Sep 11, 2023 at 22:31

2 Answers 2

Reset to default
0

You will find in the attached file a sheet "Target-Solved". Just enter the required material number, rest will be dynamic.

Enter the material code (copy paste the required material codes) in the first column. The following columns are lookups and sumifs, using dynamic ranges.

Hope this will work for you.

Attachment

Improve this answer
2
  • 2
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Sep 12, 2023 at 8:33
  • 1
    Yasir, Thanks a ton. If you would be kind enough to explain the formula behind this, it would be of great help.
    – Shiv
    Commented Sep 13, 2023 at 5:39
1

The Formulas in Target-solved:

  1. Under Material description I used xlookup to bring the Mat.Descrip. from the Global Table. The lookup key is a dynamic range Req_Mat. (press ctrl+F3 to see/edit the dynamic range). If you entered a non-existing matetial code, it gives error message Material Num. Not in Global =XLOOKUP(Req_Mat,GlobalTable[Material],GlobalTable[Material description],"Material Num. Not in Global")

  2. Under P1837 Required, SUMIFS is used to bring you required qty (from Sheet 'P1837 Requirement') for the respective Material Codes (which is the dynamic range Req_Mat). =SUMIFS(Table_1[Total stock],Table_1[Material],Req_Mat)

  3. From Column D, there is a Transposed Unique list of other plants numbers. ="P" & TRANSPOSE( UNIQUE( 'GlobalStock Availability'!$D$2:$D$115 ) )

  4. From Cell D2, the range is dynamically plotted using SUMIFS function and array function which brings the total qty of the every material code for the respective column heading (Plant number). =SUMIFS(GlobalTable[Total stock],GlobalTable[Plant],REPLACE(D$1#,1,1,""),GlobalTable[Material],Req_Mat)

  5. Req_Mat is a dynamic range which contains the formula =OFFSET('Target-Solved'!$A$2,,,COUNTA('Target-Solved'!$A$2:$A$1048576),1) (press ctrl+F3 to see/edit the dynamic range).

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .