Populate cell on worksheet based values from another worksheet

[Mitzy_fto]

Hello all,

I'm new to the forum and when it comes to macros/vba within excel.

Wondering if anyone could help with an issue I'm having to resolve:

I have two worksheets, in one I have the following:

Weight from

Weight to

Price

0

500

100

501

1000

165

1001

2000

210

2001

3000

265

for over 3000, it's an additional 35 for every 1000.

In the other worksheet I need to automatically populate the cell called "price"
when I enter the weight in the cell "Weight" based on whats detailed in
the 1st worksheet.

I would apreciate any help you can give me.

Regards,

Joe

[pike]

Hi Mitzy_fto
Welcome to the forum
Depending on the sheet names and the columns... it will be along the lines of......

=LOOKUP(Sheet2!A1,Sheet1!A:A,Sheet1!C:C)

[Mitzy_fto]

Hi thanks for the reply,

I get an error when I try it...

I've attached a sample of the workbook for you. When I enter the weight in the Invoice sheet, based on the from/to weight contained on the Detail_1 sheet, I need the relevant price copied to
the price cell on the invoice sheet. But also, when we hit the 40000 threshold for every subsequent 10000 I need to take the last price and then add the 156.80 to it.

Thanks again,

Joe

[pike]

Hi Mitzy_fto
Had to add a zero for the ...

[Mitzy_fto]

Hi Pike,

Thanks for that, thats great. How would I go about doing the values above 40000?
I need to had the value in cell G16, for every 10000 in weight above the 40000
threshold.

i.e. Weight 44000 result would be 893.30 + 156.80, 55000 would be 893.30 + (2 x 156.80)
and so on.

Thanks again for you help so far. I'm extremelly greatfull

Joe

[pike]

Hi Mitzy_fto

something like
=LOOKUP(H2,Detail_1!B3:B14,Detail_1!G3:G14)+IF(H2>40000,(ROUNDUP(H2/10000,0)-4)*156.8,0)

[Mitzy_fto]

Hi Mitzy_fto

something like
=LOOKUP(H2,Detail_1!B3:B14,Detail_1!G3:G14)+IF(H2>40000,(ROUNDUP(H2/10000,0)-4)*156.8,0)

Hi, Thanks for the reply when I add the if statement I get;

The entered formula contains an error.

Thanks again for your help with this.

Joe

[pike]

Strange it works for me

=LOOKUP(H2,Detail_1!B3:B14,Detail_1!G3:G14)+IF(H2>40000,(ROUNDUP(H2/10000,0)-4)*156.8,0)

[snb]

Why using a table when math can give the result ?

=110 +integer(H2/1000)*55

[Mitzy_fto]

Why using a table when math can give the result ?

=110 +integer(H2/1000)*55

With that I just get #NAME?

They have changed the criteria on me...

It's now > 50000 and 53.42 for every 1000 now

is it just a case of changing the formula to:

+IF(H2>50000,(ROUNDUP(H2/1000,0)-4)*53.42,0)

Thanks,

Joe

[shg]

Most of those columns are unnecessary.

Please Login or Register  to view this content.

The formula in E3 is =VLOOKUP(D3, $A$3:$B$14, 2) + MAX(0, CEILING((D3 - 40000)/10000, 1) * $B$16)