Dynamic Range linking to Pivot

[Sri.n]

Hello..

I have been working on a data set creating an Forecast model. I will get the historical numbers from system and update in Col A to F post that J to O is the pivot linked. Starting from Col Q I have applied formula to calculate the forecast. When ever there is a new item Product or Sub Product added Col Q to T gets disturbs and I will to re work on complete data.

Is there any way that I can make it Dynamic as once the pivot is refreshed and new Sub products added still the function pick correctly along with the totals.

I have attached sample working file for reference.

Hope all are safe

Thank you

[JeteMc]

Perhaps this will help.
Note that I am assuming that the values in the Monday section of the pivot table should reference the Monday Totals.
The range Q5:T100 is populated using:

Please Login or Register  to view this content.

Note that when values are manually placed in the rows corresponding to the daily totals the values will overwrite the formula so that when the pivot table is refreshed it will be necessary to copy the formulas from Q5:T5 down again.
Note that until the values are manually placed in the rows corresponding to the daily totals the formula will yield an error.
Let us know if you have any questions.

[Sri.n]

Thank you so much for the function and work good. for some reason when I replicate the same in my actual file I am getting #DIV I will check and get back if I have any question.

Thanks again.

[Sri.n]

Hello JeteMc,

Could you please help to amend the formula as per the sheet? I am unable to fix it. Not sure where I am going wrong here.

Attached Book for reference.

[JeteMc]

I do not see any data in the file attached to post #4.

[Sri.n]

I have added some data to the file Col S to T is what I need your formula to be updated. A to Q is not the pivot table but in my actual file its a pivot.

[JeteMc]

File attached to post #6 seems to be the same as the file attached to post #4. The spreadsheets are mainly covered up by a picture that displays Code 42 in the top left corner and seems to be giving information about the progress of a backup. When the picture is moved there is no data underneath.

[Sri.n]

My Apologies Attached wrong file my bad. Please find attached correct file now.

[oeldere]

when I replicate the same in my actual file I am getting #DIV I will check and get back if I have any question.

In your file in #8 there is no #div in column S and T.


Microsoft Excel shows the #DIV/0! error when a number is divided by zero (0)

[JeteMc]

The following formula, placed in cell S3 and then copied over and down to cell V12, replicates the previous values in that range of cells :

Please Login or Register  to view this content.

Let us know if you have any questions.

[Sri.n]

Wonderful.. Thank you so much that works well.

Thank you very much

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.