Boards are fundraising to help the people of Ukraine via the Red Cross at this horrific time. Please donate and share if you can, you will find the link here. Many thanks.

# Excel

• 24-02-2021 2:11pm
Registered Users Posts: 14,252 ✭✭✭✭

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%.

• Registered Users Posts: 359 ✭✭

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.)

• Registered Users Posts: 14,252 ✭✭✭✭

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

• Registered Users Posts: 776 ✭✭✭

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)

• Registered Users Posts: 58,781 ✭✭✭✭

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.

• Registered Users Posts: 776 ✭✭✭

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

• Registered Users Posts: 776 ✭✭✭

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

• Registered Users Posts: 776 ✭✭✭

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

• Registered Users Posts: 58,781 ✭✭✭✭

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.

• Registered Users Posts: 776 ✭✭✭

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.

• Registered Users Posts: 58,781 ✭✭✭✭

OK- let me take a look again.

• Registered Users Posts: 58,781 ✭✭✭✭

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)

• Registered Users Posts: 776 ✭✭✭

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!

• Registered Users Posts: 776 ✭✭✭

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

• Registered Users Posts: 776 ✭✭✭

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

• Registered Users Posts: 58,781 ✭✭✭✭

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"))))

• Registered Users Posts: 776 ✭✭✭

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.

• Registered Users Posts: 1,208 ✭✭✭

The following youtube tutorial gives a similar example to follow

• Registered Users Posts: 776 ✭✭✭

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.

• Registered Users Posts: 1,208 ✭✭✭

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