Automatization of data entry (and return of data)

[Eirik Sævareid]

Dear all,

I am doing a study of product costs versus revenues for a set of products at
a company. It is a developed a "price model" which returns a product cost
based on input in three cells. I am going to carry out a study for about
200 products.

Then I wonder if it is possible to develop a macro or something that can do
this in a simple way (for me to avoid punching all the values). The input
data is stored in an excel sheet (one row for each product). The product
codes (which are input data in the price model) for a given product may be
stored in cells A2, B2, and C2 (for example) in a sheet called "data".
These data should then be entered into three cells in sheet "pricemodel"
(for instance B2, C2, D2), and a result comes in cell E2 in sheet
"pricemodel". This result should then be transferred to cell D2 in sheet
"data". Then the routine should continue with the next product (cell A3,
B3, C3 in sheet "data") until this has been done for all 200 products.

Is there any way to automatize this task (macro or another way) ?

Please revert if my explanation is insufficient.

All help will be greatly appreciated.

Best regards,
Eirik Saevareid

 

[Guest]

This is probably wrong because it is way too simple. And since no one else
replied I think I must be missing something. Hopefully it will at least serve
as feedback on how your post is being read and allow you to better clarify.

If my read is correct, this should be easily done with worksheet formula or
even copying and pasting for much of it. My interpretation was that you meant
to say "column" instead of "row" in this statement:

data is stored in an excel sheet (one row for each product).

As I have it, source data for the calculation derive from sheet "data"
starting in cells A2, B2 and C2 and carry on down the column. These data need
to be entered into cells B2, C2 and D2 of sheet "pricemodel" and should
progress down the sheet (i.e. B3, C3 and D3 should receive values from A3, B3
and C3 of sheet "data" and so on). Also, in column E starting in E2 of sheet
"pricemodel" you have a formula that does a price model calculation with the
product code data in columns B, C and D.

If the above is correct, then try entering this formula in cell B2 of sheet
"pricemodel": =data!A2
Then drag it to the right so that the formula autofills cells C2 and D2.
Cell E2 should then calculate the result assuming it contains a formula which
you created. Now drag the range B2:E2 downward until they fill the required
number of cells (down to approx. B202:E202).

Finally, in cell D2 of sheet "data" enter the formula: ='pricemodel'!E2
and drag it downward for the required range.

As I said, this seems way to simple so I figure I must be missing something.
Hopefully this will get the ball rolling at least.

Regards,
Greg

 

[Eirik Sævareid]

Hello,

Thank you for your answer. I am sorry about a bad explanation of the
problem.

It is correct as I wrote that the data input for the price calculation are
stored in one row for each product (i.e. row 2 for product A, row 3 for
product B, etc.). The price model is based on three input data (for each
product).

The clue here is that the Price Model is a quite complex spreadsheet. The
input data has to be entered into cells B2, C2, D2 in sheet "pricemodel".
When this is done, a result comes into cell E2 in the same sheet. This
result should then be returned into cell D2 in the sheet "data".

When this is done for the first product, cells B2, C2, and D2 in sheet
"pricemodel" should be emptied, and the values for the second product (cells
A3, B3, C3 in sheet "data") should be entered into cells B2, C2, D2 in sheet
"pricemodel", and the result for the second product should be returned to
cell D3 in sheet "data". This should then continue until it is done for all
about 200 products. I suppose some kind of macro may be needed.

The values have to be put into those three input cells (B2, C2, D2) in the
sheet "pricemodel", it is (from my point of view) not possible to copy these
cells downward in the sheet "pricemodel", because of a lot of underlying
formulas which is included in the price model.

Was this an acceptable clarification, and do anyone have a solution ?

Best regards,
Eirik

The clu

 

[Eirik Sævareid]

Is it possible to write you an email to better describe the problem ?

Best regards,
Eirik