Excel

Seve OB

Any Excel gurus out there who might be able to help me please.
I’m looking for a formula where one cell will show a fixed value eg d1 9%
Cell f1 will say Gross @ (d1)

Can’t figure out what way to write the formula in f1 without just writing in the 9%.

radia

Depending on how the cell with the percentage in it (D1) is formatted, try putting either of the following in F1:

="Gross @ "&D1
or
="Gross @ "&TEXT(D1,"0.0%")

In the second example, you can tailor the number of decimal places to suit the display you want. ("0%", "0.0%", "0.00%" etc.)

Seve OB

Thanks for that. I was very close. Missing the first “ and the word text

Zenify

I hope you don't mind me hijacking this thread. I too am looking for an excel guru. I have a vlookup formula that is pulling a lot of its conditions from other formulas. It has been working great for an invoice system I made for my wife's business, which pulls information from a price list. She has now added in another variable so I'm wondering is it possible to move the vlookup answer down a cell based on the number in another cell? let's say if the cell says 1 the vlookup answer moves down 1 cell?

It's a handy invoice file as all my wife has to do is select the product code and quantity and everything else is automatic.

If you want to look at the pictures. In colum G of the price list it used to just be handmade paper but now it also has a variable of pressed cotton card (my vlookup does not return a value for pressed cotton card)

namenotavailablE

Would it be possible to upload a mock-up/ non-sensitive of the workbook and I could have a look. I think it might be possible using a mix of INDEX and MATCH to get the second result. Alternatively, if you coded the new options with something like 101A, 102A, 103A you could use a second VLOOKUP using the new codes.

Zenify

namenotavailabl wrote: »

Would it be possible to upload a mock-up/ non-sensitive of the workbook and I could have a look. I think it might be possible using a mix of INDEX and MATCH to get the second result. Alternatively, if you coded the new options with something like 101A, 102A, 103A you could use a second VLOOKUP using the new codes.

Thanks for the help

Zenify

before quantity(colum E) I would insert a selector with the options (handmade paper, pressed cotton)

Zenify

I have a solution that isn't perfect that I can use if nobody has a perfect fix?

namenotavailablE

I have a not great solution but it returns the correct price (I believe).

In cell G23 on the 'Invoice invitation' sheet, the following formula will give the price of the digital print version of the product:

=INDEX(Pricing!$H$19:$H$91,MATCH(A22,Pricing!$C$19:$C$91,0)+1)

You'd need to manually copy that into the other cells for the other products which is slightly messy but it seems to works as required.

Zenify

namenotavailabl wrote: »

I have a not great solution but it returns the correct price (I believe).

In cell G23 on the 'Invoice invitation' sheet, the following formula will give the price of the digital print version of the product:

=INDEX(Pricing!$H$19:$H$91,MATCH(A22,Pricing!$C$19:$C$91,0)+1)

You'd need to manually copy that into the other cells for the other products which is slightly messy but it seems to works as required.

That doesn't adjust to the quanity amount. if you change the quanity on my sheet to 76 or 101 you will see the price change.

namenotavailablE

OK- let me take a look again.

namenotavailablE

Try this:

=INDEX(CHOOSE(K22-4,Pricing!$H$19:$H$91,Pricing!$I$19:$I$91,Pricing!$J$19:$J$91,Pricing!$K$19:$K$91),MATCH(A22,Pricing!$C$19:$C$91,0)+1)

Zenify

AMAZING! I edited it slightly to make the +1 at the end change based on the paper selected. But that's it. You truly are an excel guru!

Zenify

Any idea why it doesn't work if there is just 1 selected in quanity?

Zenify

it's not that important as we rarely have anything at 1 single item.

namenotavailablE

The formula in column K needs to be slightly modified (it's the last test that was throwing an error). In cell K22, this is the new formula:

=IF(F22>Pricing!$K$17-1, "8", IF(F22>Pricing!$J$17-1, "7", IF(F22>Pricing!$I$17-1, "6", IF(F22>=1, "5"))))

Zenify

I have another question for the excel gods of boards. On the excel file posted earlier it has a price list in euro. We want to make a price list in usd. Is it possible to dynamically copy the price list into another sheet and then adjust the figures based on a percentage?

Rather than editing 2 price lists we could just edit the eur one.

cunavalos

The following youtube tutorial gives a similar example to follow

Zenify

cunavalos wrote: »

The following youtube tutorial gives a similar example to follow

That's not really what I'm looking for. The percentage for currency is not the hard part. It's dynamically copying a large amount of cells and being able to edit them ontop of the original figures.

cunavalos

Gotcha, using the above way of obtaining live currency conversion data and creating separate worksheets for USD and GBP it is possible to create separate invoice invitation worksheets for USD and GBP

The Pricing USD and Pricing GBP worksheets in the attached excel file will obtain their data dynamically from the Euro pricelist and currency conversion checklist.

The only issue is that if you add extra lines to the Euro pricelist and not just adjust prices in the existing cells you will have to update the GBP and USD pricelist to reflect these changes