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:
<tbody>
</tbody>
And this is worksheet 2
<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
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