Auto fill group data using info from another worksheet? - Formula Needed.

[BoldB]

Microsoft Excel 2010, Windows 7


Hi all,

Looking for some help with a formula to auto fill data from one worksheet to another by using a formula:

On our first worksheet we have sales data with codes

The sales data is per a row and in that row is a code such as (A32) (C32) depending on product spec sold.

We want another 8 columns of associated data to be put in the next columns on the sales sheet.

I understand the data has to be referred to somewhere so on worksheet two we have all the codes listed down a column (eg Column A) and then next to it we have the next 8 columns with data relating to that code.

So how can we ask excel to copy the relevant data from the reference worksheet (worksheet 2) into our sales data (worksheet 1)

I was hoping at this stage for a formula based solution.

I know about scripts and macros BUT have NO experience, and need a quick fix solution at this point.

Also because of discrepancies in data, I believe a macro would need extra if but maybe rules to function correctly, where as a formula I can drag or copy down will work best for now, as I can adjust to suit the discrepancies in data.


For example, This is worksheet 1:

[TABLE="class: grid, width: 736"]
<tbody>[TR]
[TD]RECORD[/TD]
[TD]CODE[/TD]
[TD]GRABBED 1[/TD]
[TD]GRABBED 2[/TD]
[TD]GRABBED 3[/TD]
[TD]GRABBED 4[/TD]
[TD]GRABBED 5[/TD]
[TD]GRABBED 6[/TD]
[TD]GRABBED 7[/TD]
[TD]GRABBED 8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]C32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]D32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]E32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]F32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]C32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]D32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]I32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]J32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]K32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]L32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]M32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]F32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]J32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]K32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And this is worksheet 2

[TABLE="class: grid, width: 576"]
<tbody>[TR]
[TD="width: 64"]CODES[/TD]
[TD="width: 64"]GRAB 1[/TD]
[TD="width: 64"]GRAB 2[/TD]
[TD="width: 64"]GRAB 3[/TD]
[TD="width: 64"]GRAB 4[/TD]
[TD="width: 64"]GRAB 5[/TD]
[TD="width: 64"]GRAB 6[/TD]
[TD="width: 64"]GRAB 7[/TD]
[TD="width: 64"]GRAB 8[/TD]
[/TR]
[TR]
[TD]C32[/TD]
[TD]24x16[/TD]
[TD]£4.63[/TD]
[TD]£7.45[/TD]
[TD][/TD]
[TD]65[/TD]
[TD]43[/TD]
[TD]3[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]D32[/TD]
[TD]30x20[/TD]
[TD]£6.49[/TD]
[TD]£12.55[/TD]
[TD][/TD]
[TD]78[/TD]
[TD]52[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]E32[/TD]
[TD]36x24[/TD]
[TD]£11.30[/TD]
[TD]£17.36[/TD]
[TD][/TD]
[TD]93[/TD]
[TD]62[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]F32[/TD]
[TD]48x32[/TD]
[TD]£16.04[/TD]
[TD]£22.10[/TD]
[TD][/TD]
[TD]125[/TD]
[TD]83[/TD]
[TD]5[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]G32[/TD]
[TD]54x36[/TD]
[TD]£22.11[/TD]
[TD]£28.17[/TD]
[TD][/TD]
[TD]140[/TD]
[TD]93[/TD]
[TD]5[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]H32[/TD]
[TD]3 x 16x8[/TD]
[TD]£6.91[/TD]
[TD]£9.74[/TD]
[TD][/TD]
[TD]43[/TD]
[TD]23[/TD]
[TD]7[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]I32[/TD]
[TD]3 x 24x12[/TD]
[TD]£10.76[/TD]
[TD]£16.83[/TD]
[TD][/TD]
[TD]64[/TD]
[TD]32[/TD]
[TD]7[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]J32[/TD]
[TD]3 x 28x14[/TD]
[TD]£12.71[/TD]
[TD]£18.78[/TD]
[TD][/TD]
[TD]74[/TD]
[TD]38[/TD]
[TD]7[/TD]
[TD]3.5[/TD]
[/TR]
[TR]
[TD]K32[/TD]
[TD]3 x 40x20[/TD]
[TD]£19.90[/TD]
[TD]£25.96[/TD]
[TD][/TD]
[TD]105[/TD]
[TD]52[/TD]
[TD]7[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]L32[/TD]
[TD]3 x 48x24[/TD]
[TD]£36.18[/TD]
[TD]£42.24[/TD]
[TD][/TD]
[TD]125[/TD]
[TD]63[/TD]
[TD]15[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]M32[/TD]
[TD]32x18[/TD]
[TD]£8.39[/TD]
[TD]£11.22[/TD]
[TD][/TD]
[TD]43[/TD]
[TD]22[/TD]
[TD]9[/TD]
[TD]1.5[/TD]
[/TR]
</tbody>[/TABLE]


On Worksheet 1, we want it to insert the cells next to the corresponding code in worksheet 2 into the next 8 columns (so CELL C2 on worksheet 1 would be auto filled with the data from CELL B2 as the code is "C32", CELL C5 in worksheet 1 would have the same date autofilled as the code is also "C32" etc..).

First post so I hope I've posted this correctly with enough information. Any help will be much appreciated.

Kind Regards
Steven

 

[BoldB]

Hi,

Is there a formula that can look to see if there is a space in the data in a cell and if there is not, insert a space 3 digits from the end.

So the postcodes TA1 2BN and LS12 8PO would be left as they are and TA12BN and LS128PO would become TA1 2BN and LS12 8PO?

So the table would end up like this:

[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD]Data [/TD]
[TD="width: 64"]Formula [/TD]
[/TR]
[TR]
[TD]TA1 2BN[/TD]
[TD]TA1 2BN[/TD]
[/TR]
[TR]
[TD]LS12 8PO[/TD]
[TD]LS12 8PO
[/TD]
[/TR]
[TR]
[TD]TA12BN[/TD]
[TD]TA1 2BN[/TD]
[/TR]
[TR]
[TD]LS128PO[/TD]
[TD]LS12 8PO[/TD]
[/TR]
</tbody>[/TABLE]


Kind Regards
Steven[TABLE="width: 61"]
<tbody>[TR]
[TD="class: xl76"][/TD]
[/TR]
</tbody>[/TABLE]

 

[VoG]

Try

=IF(ISNUMBER(FIND(" ",A2)),A2,LEFT(A2,LEN(A2)-3)&" "&RIGHT(A2,3))

 

[BoldB]

Thanks Again VoG. Your a genius!!

 

[BoldB]

Hi VoG,

Can you help with this problem:

I have used the INDEX formula from you that converts currency for me for my DHL CSV upload which is giving me the following results

FORMULA in COLUMN B:
=INDEX('PRICE CONVERSIONS'!$B$1:$B$50000,MATCH($Q1,'PRICE CONVERSIONS'!$D$1:$D$50000,0))

Result:
[TABLE="class: cms_table_grid, width: 142"]
<tbody>[TR]
[TD]DATA[/TD]
[TD]FORMULA[/TD]
[/TR]
[TR]
[TD]EUR 119.99[/TD]
[TD="align: right"]£95.99[/TD]
[/TR]
[TR]
[TD]US $94.99[/TD]
[TD="align: right"]£59.37[/TD]
[/TR]
[TR]
[TD]AU $289.99[/TD]
[TD="align: right"]£187.09[/TD]
[/TR]
[TR]
[TD]AU $184.99[/TD]
[TD="align: right"]£119.35[/TD]
[/TR]
[TR]
[TD]AU $89.99[/TD]
[TD="align: right"]£58.06[/TD]
[/TR]
[TR]
[TD]US $159.99[/TD]
[TD="align: right"]£99.99[/TD]
[/TR]
[TR]
[TD="align: right"]£99.99[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]AU $99.99[/TD]
[TD="align: right"]£64.51[/TD]
[/TR]
[TR]
[TD="align: right"]£69.99[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]


And this for example is a line of the data from the "PRICE CONVERSIONS" worksheet which gives the result for row 2 in the above table:

[TABLE="class: cms_table_grid, width: 256"]
<tbody>[TR]
[TD="align: right"]119.99[/TD]
[TD="align: right"]£95.99[/TD]
[TD]EUR[/TD]
[TD]EUR 119.99[/TD]
[/TR]
</tbody>[/TABLE]


As you can see if the price is in £ then the formula returns #N/A

Is there an adjustment I can make to the formula that would ignore the formula if the price is in £ and simply enter the figure again the the B column as it appears in column A (maybe an IF function in the formula or something like that)

Thanks

Kind Regards
Steven

 

[VoG]

I suggest that you start a new thread for this.

 

[BoldB]

No problem Vog