[h=2][/h]
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 codes and numbers.
The data is in multiple rows and in that row is a combination of codes such as A+E+T or A+E+F depending on product.
We want another 4 columns of associated data to be put in the next columns on sheet 1.
I understand the data has to be referred to somewhere so on worksheet 2 we have all the codes listed down columns (eg Column A, Column B, and Column C) and then next to it we have the next 4 columns with data relating to those combinations of codes.
So how can we ask excel to copy the relevant data from the reference worksheet (worksheet 2) into our data (worksheet 1)
I was hoping at this stage for a formula based solution.
For example, This is worksheet 1:
<colgroup><col width="93" span="9" style="width:70pt"> </colgroup><tbody>
</tbody>And this is worksheet 2
<colgroup><col span="6"><col></colgroup><tbody>
</tbody>
On Worksheet 1, we want it to insert the cells next to the corresponding code in worksheet 2 into the next 4 columns (so CELL D2 on worksheet 1 would be auto filled with the data from CELL D2 as the code is "1000", CELL E2 in worksheet 1 would have the same date autofilled as the code is also "E2" etc..).
I thought about doing a concat and then a vlookup but thinking there should be a formula?
First post so I hope I've posted this correctly with enough information. Any help will be much appreciated.
Kind Regards
Jessica
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 codes and numbers.
The data is in multiple rows and in that row is a combination of codes such as A+E+T or A+E+F depending on product.
We want another 4 columns of associated data to be put in the next columns on sheet 1.
I understand the data has to be referred to somewhere so on worksheet 2 we have all the codes listed down columns (eg Column A, Column B, and Column C) and then next to it we have the next 4 columns with data relating to those combinations of codes.
So how can we ask excel to copy the relevant data from the reference worksheet (worksheet 2) into our data (worksheet 1)
I was hoping at this stage for a formula based solution.
For example, This is worksheet 1:
| A | B | C | D | E | F | G | H | I |
| A | E | F | 0 | 0 | 0 | $0 | $0.00 | $0.00 |
| A | E | T | 0 | 0 | 0 | $0 | $0.00 | $0.00 |
| A | S | F | 0 | 0 | 0 | $0 | $0.00 | $0.00 |
| A | S | T | 0 | 0 | 0 | $0 | $0.00 | $0.00 |
<colgroup><col width="93" span="9" style="width:70pt"> </colgroup><tbody>
</tbody>
| A | B | C | D | E | F | G |
| A | E | F | 1000 | 200 | 100 | $1,000.00 |
| A | E | T | 5000 | 800 | 300 | $1,500.00 |
| A | S | F | 3000 | 300 | 100 | $2,000.00 |
| A | S | T | 3000 | 300 | 100 | $2,500.00 |
| N | E | F | 650 | 40 | 20 | $3,000.00 |
| N | E | T | 2200 | 220 | 20 | $3,500.00 |
| N | S | F | 900 | 60 | 20 | $4,500.00 |
| N | S | T | 600 | 50 | 20 | $5,000.00 |
<colgroup><col span="6"><col></colgroup><tbody>
</tbody>
On Worksheet 1, we want it to insert the cells next to the corresponding code in worksheet 2 into the next 4 columns (so CELL D2 on worksheet 1 would be auto filled with the data from CELL D2 as the code is "1000", CELL E2 in worksheet 1 would have the same date autofilled as the code is also "E2" etc..).
I thought about doing a concat and then a vlookup but thinking there should be a formula?
First post so I hope I've posted this correctly with enough information. Any help will be much appreciated.
Kind Regards
Jessica
