Hello Excel-Community,

I'm currently working on a file that analyses processes or to be exact analyses technical issues on facilities. It works with raw data which I paste into one sheet called "Raw Data"-sheet (obviously). The data contains information on who fixed the problem, what category the problem was, how urgent the problem was, etc.

My task is to filter out the raw data and make calculations to create metrics on how fast the problem was solved, if it was solved remotely or on-site, etc. These calculations are made on the "Calculations"-sheet (again, obviously :P).

For some of the calculations, I also need the entry sheet to fill in information such as working hours, national holidays etc. to exclude the duration of the process on those days or beside working hours.

Furthermore I have one "Monthly Dashboard"-sheet in which I paste PivotTables to display data from the "Raw Data" sheet and from the "Calculations"-sheet.

I hope I explained that clearly. Now to my issue:

To eliminate the "(blank)" columns in my pivottables, I made tables out of information from the Raw data and calculations. Because the table would expand depending on how much information I entered it wouldn't leave any info behind as it would be the case if I would create a list.

And my problem was solved. Almost... The raw data sheet was expanding when I pasted stuff into it but the calculations sheet which was linked to the raw data wasn't. I figured it would be because I didn't manually paste stuff into it.

In this case I would have the correct information in the pivottables that depended on the raw data but incorrect and incomplete info in the pivottables that depended on the calculations because, again, the range of the calculations sheet wasn't expanding.

To solve the problem, the calculations sheet would have to have the same amount of rows as the raw data sheet at all times. Is there any way to tell Excel that (via VBA for example)? Or do you have a alternative solution to my issue?

I hope my explaining makes sense! I'm not a native as you can tell and I apologize for any false English grammar.

Thank you very much in advance and feel free to ask any questions if something isn't clear.

Kind regards,
Adam