SOLVED

Dynamic array and hard data.

Frequent Contributor

Hi,
I have create a dynamic array and then add a value column in the left that array. The column contains 2 values "Product 1" and "Product 2" that is merged cells, they are hard data. The issue is when dataset is added, the array is updated but the hard data is not moved respectively.
Maybe, have some ways to approach that.

I have added an image and the samplefile below.
Can you give me some advices.
Thank you.

small_village_0-1664597734196.png

 

 

2 Replies
best response confirmed by small_village (Frequent Contributor)
Solution

@small_village I feel like you were on the right track.  Merged cells are quite static and, as such, shouldn't be paired with dynamic arrays where the range will constantly change.  

 

Since your dynamic array is based off the pivot table, I used a label filter to remove any id's containing product.  Instead, I used the subtotal (as I noticed when I added additional rows to the source of the pivot table, obviously the values of product 1 & product 2, etc, would not update to reflect the new rows).  

 

DexterG_III_0-1664602004218.png

 

For the formatting, I used your approach with slightly different formulas:

DexterG_III_1-1664602194739.png

 

Workbook is attached for your reference.

 

Hope this helps.  

Dexter

@DexterG_III 

Thank you very much.
The OFFSET function is as key for the formula.
It actually worked.

Regards,

Phan.