SpirePeter
New Member
- Joined
- Oct 16, 2022
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
I have created two custom formulas that each return a two dimensional array that result in dynamic arrays that spill to adjacent fields.
The following examples are a mockup of my actual formulas as they are large.
Dynamic array 1 is on it's own sheet called "Orders" and looks like this:
Custom formula is in cell A2 and spills to A2:B4
On another sheet, "Inventory", I have Dynamic array 2.
Custom formula is in A2 and spills to A2:B3
I want a way, likely using something like vlookup, xlookup, index etc to use the PartNo in the Orders sheet to determine the Onhand from the Inventory sheet.
The result would be this:
Ideally, the results in C would also expand down dynamically, when the list of orders returned grows.
But I could manage if I had to manually extend the formula when "Orders" has been refreshed.
The following examples are a mockup of my actual formulas as they are large.
Dynamic array 1 is on it's own sheet called "Orders" and looks like this:
Custom formula is in cell A2 and spills to A2:B4
On another sheet, "Inventory", I have Dynamic array 2.
Custom formula is in A2 and spills to A2:B3
I want a way, likely using something like vlookup, xlookup, index etc to use the PartNo in the Orders sheet to determine the Onhand from the Inventory sheet.
The result would be this:
Ideally, the results in C would also expand down dynamically, when the list of orders returned grows.
But I could manage if I had to manually extend the formula when "Orders" has been refreshed.