6 Replies Latest reply on Feb 13, 2014 2:12 AM by Willi Adelberger

    Help needed with referencing single Excel cells and formatting resulting text

    UpStyleDesign Level 1

      In InDesign CS5 I am putting together a 20pp catalogue of about 200 products. The plan is to have the product information, SKU code, quantity etc fixed, but have the prices (there are two i.e. pack price and individual price) being linked to an Excel spreadsheet. This is so that the prices can be updated in the Excel file and the InDesign file will pull the new prices through. In case you are wondering why I don't pull the whole set of information through, this is because there are a lot of copywriting changes done to the information once it's in InDesign - it's only going to be the prices that will be updated.

       

      I am planning on having two single cell tables in their own text frame, duly formatted with cell style, table style and paragraph style for the two price variables. This I am then going to have to repeat 200 times making sure I link to the next row down in Excel. This is going to be a hideous task but I see know way of modifying the cell in InDesign to point it to the next row in Excel. That's my first problem.

       

      My second problem is this. In the Excel sheet, the prices are formatted as UK currency and are therefore like this...

       

      £2.00

      £0.40

      £1.43

      £9.99

      £0.99

      £0.09

       

      What I will require is once I import that data (and refresh the data via a newly saved Excel file) is that the prices end up like this...

       

      £2.00

      40p

      £1.43

      £9.99

      99p

      9p

       

      So if the value is lower than £1.00 it needs a trailing 'p' added  and the leading zero and '£' sign stripped off. If the value is lower than £0.10 it also needs the zero after the decimal point stripping off.

       

      Then formatting wise, the '£' sign needs to be superscripted and the same for the 'p'. This I am assuming could be done via GREP?

       

      In summary, can anyone help with the first task of referecing the Excel cells on a cell by cell basis, given that it is the same cell column each time, but the next row down, and also point me in the right direction of the price formattting issues.

       

      Any help will be gratefully received.