Excel

Seve OB · 24-02-2021 3:11pm #1

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 · 24-02-2021 3:25pm

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 · 24-02-2021 4:00pm

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

Zenify · 09-03-2021 9:57pm

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 · 09-03-2021 11:05pm

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 · 09-03-2021 11:29pm

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 · 09-03-2021 11:36pm

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

Zenify · 10-03-2021 1:29pm

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

namenotavailablE · 10-03-2021 6:30pm

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 · 10-03-2021 6:39pm

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 · 10-03-2021 6:41pm

OK- let me take a look again.

namenotavailablE · 10-03-2021 6:49pm

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 · 10-03-2021 7:08pm

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 · 10-03-2021 9:29pm

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

Zenify · 10-03-2021 9:45pm

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

namenotavailablE · 10-03-2021 11:02pm

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 · 20-03-2021 5:02pm

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 · 20-03-2021 5:10pm

The following youtube tutorial gives a similar example to follow

Zenify · 20-03-2021 5:48pm

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 · 20-03-2021 8:32pm

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

Excel

Comments