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
"Greg Wilson" <(E-Mail Removed)> skrev i melding
news:CE69039C-65C9-46BC-B83D-(E-Mail Removed)...
> 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" wrote:
>
> > 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
> >
> >
> >
Similar Threads
