Excel formula help

mogwai

mogwai

mogwai

Associate
Joined
27 Aug 2004
Posts
991
Hi,

I am trying to find the necessary formula to use that will allow me to connect an empty excel sheet to the products from the Prodcut list Excel file used to hold all the details about the products sold in our online shop. Basically I need to find the populated rows in the productid column and then repeat them with each currency cost of sale and sale price.

As the number of products change I cannot just select the column by clicking at the top as the range of products will need to repeat for each currency.

In the source spreadsheet the currencies are arranged horizontally and they need to be changed round to vertical

Source:
________| ______Sale Price_____|____ Cost of Sale____|
Product ID | USD | EUR | INR | CHF | USD | EUR | INR | CHF


Target:

Product ID | Currency Code | Cost of Sale | Sale Price
 
Last edited:
You need to do this in vba, you maybe able to do it in formulas but I wouldn't bother. It would be horrendous.

You basically need to write some code under the worksheet change event of your source sheet. As and when stuff changes or is added on that sheet it will be automatically updated on the target worksheet.

I've took a look at this and it can be done though it is not straightforward. Its a couple of hours work at least to do it properly.

You may be able to do what you need to do in pivot table too. Though I generally avoid them like the plague.
 
Last edited:
VBA? He needs a database ;)

Why companies/people insist on doing this sort of stuff in a spreadsheet is beyond me. I imagine barrier of entry is the primary reason.
 
I agree it should be done in a database.

I work for a very large financial services company (£100bns FUM) and you would not believe the short cuts they use to provide solutions in spreadsheets (when they are thinking a bit they may use Access) to save engaging IT and paying for a real solution.
 
I have given up with doing it in excel and will now be doing it in TSQL. As VBA / Macros just seem to be big pain in the backside.
 
You must log in or register to reply here.
Back
Top Bottom