Excel formula help

mogwai · 28 Jun 2014 at 20:55

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

dazzerd · 29 Jun 2014 at 20:02

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.

Gornall · 30 Jun 2014 at 14:46

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.

dazzerd · 30 Jun 2014 at 20:57

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.

mogwai · 30 Jun 2014 at 21:04

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.

Gornall · 1 Jul 2014 at 12:12

mogwai said:
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.

Good choice, if you need any help with the TSQL email is in trust

mogwai · 1 Jul 2014 at 12:19

Gornall said:
Good choice, if you need any help with the TSQL email is in trust

Thanks for the offer. I am ok with TSQL as my main role is a SQL DBA