Excel unique expanding table cross reference

[remeng]

Hi All;

I'm sure that this isn't uncommon, but I can't locate an applicable solution to my issue.

I have 2 tables [table1] and [table2]. [Table1] contains a list of models and their descriptions only. [table2] contains cost calculations that needs to expand based on changes to [Table1]. For example, if a model is added or removed in [Table1] it also needs to cause [Table2] to adjust. The two tables cannot be combined into one since there are a lot of different reasons that are outside the scope of my question.

[table1] should always have unique model values. If [table2] can pull the model column directly from [table1] automatically that would solve the problem quickly.

the =unique() function works on creating the dynamic range and can provide a workable solution, but I'd like to use [table2] instead since it will automatically add the formulas to the new row. This would make it a lot easier for a less excel experienced person to make either formula modifications to an entire column and allow for a bit more automation. The #Spill "error" is preventing me from using it since Microsoft decided to make life a little more difficult for everyone.

I have tried data connection from an internal table [table1]. While it will update the [table2] models, it will automatically delete the formula columns that are added to [table2] to process the data.

I have also tried to use data query from an internal table, but there are several problems with that as a long-term solution. The first is that the custom column formula isn't easy to access to make adjustments. Second, the formula structure in a power query isn't apples to apples with a standard Excel formula so an inexperienced person can't modify the formula without research. Third, the data query feature isn't really well known and understood for someone to understand what is going on with the data.

If there is a way to embed formulas into a pivot table, that might also work, but I haven't found a solution yet. I do know that you can reference a pivot table in a formula, but I'm not sure if that would solve the dynamic data row expansion and deletion from [table1] problem since I am back to using a formula outside [table2].

I can write a macro to process the data, but I have painfully learned that I am the only one in the company that understands and can create code. I'd like to avoid macros as much as possible.

Any ideas on other methods that I can explore?

Thank you for the help!

 

[SkipVought]

Hmmmm? No example?

"[table1] should always have unique model values. If [table2] can pull the model column directly from [table1] automatically that would solve the problem quickly."

Isn't there a model in table2?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[remeng]

Hi Skip,

[table2] models are entered by a manager (shudders) manually. Currently, I restricted the model options that can be selected to the model list in [table1] using an indirect() pull down list to the "Model" column cells. After the model is selected, formulas perform processes accordingly. Knowing my intended audience, a copy paste from one sheet to another is a bad idea.

I'd like to get away from that and auto expand using a range like unique() or something that will pull the equivalent line so that [table2] model column expands automatically so a manager doesn't need to go through 400+ model numbers in a pull down list to get their expected resulting data.

I hope that explained the issue a little better.

 

[SkipVought]

What happens in vagueness, stays in vagueness.

I devine, after reading your answer, that you need cascading lists based on previous selections. Am I on the right track? If it is, then this sort of process is common and doable.

So if the manager loads a row in [table 2] starting with the SELECTION of a model from a unique list of models from [table 1], and you're currently using FORMULAE, to fill some columns in [table 2] what the problem?

Is this going to become "20 Questions?"

Can we see an example based on a selected set of real data?

I think I have 16 left.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[remeng]

Hi Skip,

Unfortunately, I can't provide an example of this.

Here is the order of operations:

1) Engineer loads model numbers into [table1]
2) Engineer enters data about either all the models or a single model by part. For instance, if the part is used on all models, they would put the part number in and then select "All" to denote if it is used for all models. If it is for a specific model, they would enter the specific model that the part is used on. Other data is entered in several different locations, but it's the same idea.

Basically, the engineer is generating a BOM for parts that are used on all models and optional parts used on specific models.

3) Once all of the part data is loaded, a cost is assigned to the part. I have used the sumif() function to determine what model has what part assigned to it and what the cost associated with it is.

When All is selected, the total cost without options is calculated. When a particular model is selected, it sums all of the specific model parts and adds them to the "All" model costs.

4) The manager needs to know what the cost for the "All" model is, and the individual model costs are when specific parts are added. By using [table2] the manager currently can get the total cost for either "All" or a specific model. The problem is that they need to either manually select the model number or create a mess by coping and pasting the model list into [table2].

--

If there is a way to generate the same list in [tabe1] model = [table2] model without creating a mess with copy paste, that would be ideal.

 

[SkipVought]

Is your BOM a single level or multi-level BOM?

Multi-level BOMs take a recursive method to calculate--not at all simple.

I'm still NOT seeing what your challenge is very clearly. You have not done a good enought job, at least for me.

I'm down to 15.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[mintjulep]

What about parts that are used on "some" models?

What about next week, when a new model that doesn't use one of the "all" parts is created?

Using "all" as a model number is a horrible idea.

 

[mintjulep]

You need a table with

Model | part
Model | part
Model | part
Model | part

Then this becomes a simple power pivot solution.