SOLVED

Help on spill formula

Brass Contributor

Hi,

May I have your help on a spilled formula?

I have a table of data which was combined with two spilled areas (in the picture, the red and purple), then I would like to create a spill formula (column CB) next to the spilled list (column BZ & CA) which satisfied two conditions. Since the formula needs to satisfy both conditions, it depends on whether "CAPEX" or "CapAdd", it will need to lookup to different columns in the purple spilled table.

Please help me. Thanks, appreciated!

Table.png                 Lookup.png

 

9 Replies

@tanacal Not sure that this is the best way to do it but the attached file contains a working example of what you are trying to achieve. I've set-up two spilled arrays and from those created on single spilled array as you described. See if you can get this to work with your own data.

Screenshot 2023-03-23 at 06.38.05.png

 

@Riny_van_Eekelen 

 

Thanks for your suggestion, appreciated!

 

I think I did not explain well on my inquiries. Actually, I would like to the lookup value from table (specifically the info in the purple area) to the list. The column R is the perspective result. 

 

Since I am new to the community, I did not know I can attach file to my message until you sent me your solution. I have attached a sample data for my question this time. 

 

Can you please help me again? Thanks, 

 

 

@tanacal Sorry, but I don't understand your question.

I would like to have a spilled formula in cell S2 which will come back with the same result as in column R.
Thanks for your help!

@tanacal You have so many formulas that create arrays of arrays of arrays. I have difficulties in following what are trying to achieve. Sorry, but perhaps someone else understands what you are dealing with.

Thanks for your help!

@tanacal 

I had a look at the workbook before it was pulled.  It seemed like the result you're looking for came from summarizing two different tables.  It might help if you can upload a version of the workbook that contains the two tables and the desired results (Even if you have to enter them manually) for the overall project. Depending on what the expected results are, I think some of the intermediate steps can be eliminated.

best response confirmed by tanacal (Brass Contributor)
Solution
Thanks for your attention and help. For sure, I would like to streamline the processes. Can you please help me? I have sent you a message privately. Can you please let me know if you can offer yr help.

Thanks, Patrick. You nailed it!!

 

 

1 best response

Accepted Solutions
best response confirmed by tanacal (Brass Contributor)
Solution
Thanks for your attention and help. For sure, I would like to streamline the processes. Can you please help me? I have sent you a message privately. Can you please let me know if you can offer yr help.

View solution in original post