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:

RECORD	CODE	GRABBED 1	GRABBED 2	GRABBED 3	GRABBED 4	GRABBED 5	GRABBED 6	GRABBED 7	GRABBED 8
1	C32
2	D32
3	E32
4	F32
5	C32
6	D32
7	I32
8	J32
9	K32
10	L32
11	M32
12	F32
13	J32
14	K32

<tbody>
</tbody>

And this is worksheet 2

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

<tbody>
</tbody>

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

 

[VoG]

You should be able to use VLOOKUP for this - see Excel VLookup Function Examples

 

[jamtay317]

this is assuming that your spreadsheet codes starts in A1
=INDEX(Sheet2!$B$2:$I$12,MATCH($B2,Sheet2!$A$2:$A$12,0),MATCH(H$1,Sheet2!$B$1:$I$1,0))
you will also have to change the column headers so that they match

 

[jamtay317]

RECORD	CODE	GRAB 1	GRAB 2	GRAB 3	GRAB 4	GRAB 5	GRAB 6	GRAB 7	GRAB 8
1	C32	24x16	£4.63	£7.45	0	65	43	3	1.5
2	D32	30x20	£6.49	£12.55	0	78	52	3	2
3	E32	36x24	£11.30	£17.36	0	93	62	5	5
4	F32	48x32	£16.04	£22.10	0	125	83	5	8
5	C32	24x16	£4.63	£7.45	0	65	43	3	1.5
6	D32	30x20	£6.49	£12.55	0	78	52	3	2
7	I32	3 x 24x12	£10.76	£16.83	0	64	32	7	2.5
8	J32	3 x 28x14	£12.71	£18.78	0	74	38	7	3.5
9	K32	3 x 40x20	£19.90	£25.96	0	105	52	7	7
10	L32	3 x 48x24	£36.18	£42.24	0	125	63	15	20
11	M32	32x18	£8.39	£11.22	0	43	22	9	1.5
12	F32	48x32	£16.04	£22.10	0	125	83	5	8
13	J32	3 x 28x14	£12.71	£18.78	0	74	38	7	3.5
14	K32	3 x 40x20	£19.90	£25.96	0	105	52	7	7

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[BoldB]

Hi jamtay317,

Thanks, that worked perfectly

Looks like you know your stuff so if you could help me with this formula, would be of great help:

Our sales codes are as follows depending on what we sell:

Z3053[P31](00A)
0643(R)-M32
BM4010[M32](06S)

<tbody>
</tbody>

Is there a formula that would give us the code at the start IE anything before the Square "[" or Rounded "(" bracket in a the cell next to it so we would end up with this:

Z3053[P31](00A)	Z3053
0643(R)-M32	0643
BM4010[M32](06S)	BM4010

<tbody>
</tbody>

Kind Regards
Steven

 

[VoG]

Try

=IF(ISNUMBER(FIND("[",A1)),LEFT(A1,FIND("[",A1)-1),LEFT(A1,FIND("(",A1)-1))

 

[BoldB]

Hi VoG,

Works Perfect, Thank you very much

Kind Regards
Steven

 

[BoldB]

Hi,

Lastly (hopefully) can anybody help with this formula:

With the same data as last time:

Z3053[P31](00A)
0643(R)-M32
BM4010[M32](06S)
G4144[AA32](00A)

<colgroup><col><col></colgroup><tbody>
</tbody>

We require the code from the cells (P31, M32, AA32 etc..) to be placed in the cell next to it so we end up with this:

Z3053[P31](00A)	P31
0643(R)-M32	M32
BM4010[M32](06S)	M32
G4144[AA32](00A)	AA32

<colgroup><col><col></colgroup><tbody>
</tbody>

The code is only ever after the "-" or within the "[]".

Kind Regards
Steven

 

[VoG]

Try

=IF(ISNUMBER(FIND("[",A1)),MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1),RIGHT(A1,LEN(A1)-FIND("-",A1)))

 

[BoldB]

Cheers VoG, Perfect again