Formatting data
 

Hi

When formatting the same type of data frequently, I
design a template with three sheets, the first to paste
the raw data, the second a temporary for the
fuctions/formulas for fomatting the data in the former
sheet and the last one to paste the temporary sheet as
values. Please see example below.

In the second sheet I write the formulas for more than
the rows of expected data. Say somtimes 200 rows as the
rows of data varies from 10 to 150.

My questions are,

1. Is this the most efficient way of doing this task?
If not what is a better solution.

2. How can I adapt my function/formula sheet to only
write fuction/formulas only for the number of rows in the
data sheet?


Data sheet

ID CustName ProductCode
12 Joe Bloggs FEN

Temporary sheet

ID ProductName
12 Fulltime English (This name is derived from the
ProductCode in the Data Sheet and a lookup table using
the Vllookup function)

Final sheet
Just copies the Temporary sheet as values using macros.

Thanks.

You do not say where you are copying from? I would use one sheet, fill in
the formulae where I want (as you do not input data into a formula cell),
format the other columns the way I want them, and then do Edit|Paste Special
and select to paste values only. The data you pasted will be formatted
correctly, and your formulae would do their bit and voila

"rn" wrote:

Hi

When formatting the same type of data frequently, I
design a template with three sheets, the first to paste
the raw data, the second a temporary for the
fuctions/formulas for fomatting the data in the former
sheet and the last one to paste the temporary sheet as
values. Please see example below.

In the second sheet I write the formulas for more than
the rows of expected data. Say somtimes 200 rows as the
rows of data varies from 10 to 150.

My questions are,

1. Is this the most efficient way of doing this task?
If not what is a better solution.

2. How can I adapt my function/formula sheet to only
write fuction/formulas only for the number of rows in the
data sheet?


Data sheet

ID CustName ProductCode
12 Joe Bloggs FEN

Temporary sheet

ID ProductName
12 Fulltime English (This name is derived from the
ProductCode in the Data Sheet and a lookup table using
the Vllookup function)

Final sheet
Just copies the Temporary sheet as values using macros.

Thanks.