How do I stop a column calculation?

[Mark Jesse]

I have a Workbook worksheet with 4 sheets within. The intent is to have other users paste in data to Sheet1 and automatically get their results from a Pivot table on Sheet4. Sheet2 currently extracts data from Sheet1 and performs calculations, while Sheet3 has look up tables.
The cell formulas on Sheet2 were copied from cell 2 to 65536 via Ctr+Shift+(down arrow) and pasted. In the interest of keeping files size small, I'd like to be able to limit the column calculations to the last row of usable data on Sheet1. By doing a blank count starting from the bottom (Cell 65536) I can determine which is my last row. So, is there a way to define the last row of Sheet2 based on the data pasted in to Sheet1?

 

[MrKowz]

Welcome to the forums!

I'm not quite sure I follow what you are asking. How could you define the last row of Sheet2 based on Sheet1? Would you be able to provide a small sample with a desired output?

 

[Mark Jesse]

The formulas on Sheet2 are referencing the data from Sheet1 by row. So for instance, I'm need to reference the date on each line of data from Sheet1 to determine the month that an invoice will get paid (i.e. an invoice recieved after the 15th of a month will not get paid until the following calendar month). The data that will be copied into Sheet1 isn't consistent from use to use. So one user might copy in a couple hundred lines of data, while another might copy in a thousand lines. Sheet2 currently has formulas assigned from row 2 through 65536. Yes, a large amount of useless calculations and data. The rational is that I can't necessarily predict the largest amount of rows that potentially might be copied into Sheet1. So, I want to be able to accomidate any amount of copied lines into Sheet1, but I would also like to be able to limit the useless data from Sheet2. Note: I intend to supply the users with a template so each use would be from the template.

 

[MrKowz]

Are the formulas in sheet 2 consistent throughout the entire sheet (ie the same formulas on each row?)

 

[Mark Jesse]

Yes, I have appox. 5 columns performing different functions from column to column, but from top to bottom of each of the individual columns the functions are consistent but for the row reference.