Wondering if anyone can help me out. I have read various lookup support and help topics but none specific to the situation below:
We have an expense table we periodically add our business costs to. We would like to create a forecasting table that pulls in data from the expense table dynamically.
We filter our Forecast table by a particular “Project”. We want to see the total costs by “Component”. We then create dynamically calculated fields in the new table e.g. Gross and Net Costs.
We have been able to create the table by hard coding the “Components” from the Expense table and using column formulas successfully.
We have not been able to get the Components to be dynamically populated in the new table.
We have tried lookup formula but we do not get a unique distinct component per row.
We have tried creating a lookup table that does give us distinct components per row but we are unable to filter by project and our column formulas for some reason would no longer work displaying zero when introducing a Lookup Table generated from a Column in the parent Expense Table.
Hi @Clarence_Sittampalam, welcome to the community. Can you take a look at this example and add to it so we can better understand what is working and not working for you.
We would like the “Component” column in the “Component” table to be dynamically created.
So it should only show unique Components listed in the Expense Table under a particular project that we want in scope for example: components under “Project A”.
The remaining columns in the component table can then be dynamically calculated from this e.g. Total Inventory Costs (Total costs where sub-component is Inventory, Total Units, Freight Costs (total costs where subcomponent = Freight), Gross Costs per unit (Total Costs/Unit) and Net Cost (Inventory Costs/Unit)
Thanks so much for your help. I was just updating the original table so it is clearer what we are after and included some of the calculations we used in our table.
The 1st example shared appears to be using a select list for the second table that feeds the main expense table.
In our scenario we have more than one project and want to have separate table as a forecast for each Project that we can add additional columns that don’t belong in the main expense table. We would need a solution to display the unqiue list of components for each project that we could then build out the remaining field from. Hoping there was a formula that could lookup the expense table and by project list unique component names into individual rows (like the select list).
I will look through the second set of examples now incase - apologies if the solution is already there.
Thanks again for support on this. Just looked at the examples, but don’t believe they work for our particular scenario. Working this through hopefully explaining problem better:
Our Requirements:
Require more than one Forecast Table displaying unique Components filtered by Project. Examples appear to work where there is one Project.
In the Forecast Table we introduce new cost calculations that are formula based not summaries of a single column. See below:
In the Expense Table we have an Expense Type (Freight, Inventory, Samples etc)
Project A, Component XYZ, Freight, £30.
Project A Component XYX, Inventory, £50
Project A Component XYX, Samples, £100
Project A, Component XYZ, Freight, £40.
Project A Component XYX, Inventory, £20
In the Forecast Table calculate overall Freight Cost by component and Inventory Cost by component etc. We then produce a single line item for the component:
Component XYZ, Total Freight £70, Total Inventory £70.
Right now we simply hard code the Component item in the table but prefer if this could be listed in the table using a simple formula.
