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

[Robzx]

Hi everyone

I am looking for a formula rather than VBA code or a macro to transfer data from a master list on sheet 1 to autopopulate in sheet 2 based on IDs and Headers.

In sheet 1, I would have data such as

	A	B	C	D	E	F	G
1	ID	Name	Description	Currency	Value	Pricing Year	Category
2	1.01	Test 1	Core Data	GBP	15000	2012	Capital
3	1.02	Test 2		CAD	20000	2013	Revenue
4	1.03	Test 3		USD	20000	2014	Capital
5	1.04	Test 4		USD	30000	2012	Revenue
6	1.05	Test 5		USD	40000	2015	Revenue
7	1.06	Test 6		USD	50000	2015	Revenue

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

I want to populate sheet 2 automatically based on the header and ID to create

	A	B	C
1	ID	Name	Value
2	1.01	Test 1	15000
3	1.02	Test 2	20000
4	1.03	Test 3	20000
5	1.04	Test 4	30000
6	1.05	Test 5	40000
7	1.06	Test 6	50000

<tbody>
</tbody>

I guess that it is done using INDEX and MATCH, but what is the correct syntax in the formula?

Thank you

 

[Fishboy]

Hi everyone

I am looking for a formula rather than VBA code or a macro to transfer data from a master list on sheet 1 to autopopulate in sheet 2 based on IDs and Headers.

In sheet 1, I would have data such as

	A	B	C	D	E	F	G
1	ID	Name	Description	Currency	Value	Pricing Year	Category
2	1.01	Test 1	Core Data	GBP	15000	2012	Capital
3	1.02	Test 2		CAD	20000	2013	Revenue
4	1.03	Test 3		USD	20000	2014	Capital
5	1.04	Test 4		USD	30000	2012	Revenue
6	1.05	Test 5		USD	40000	2015	Revenue
7	1.06	Test 6		USD	50000	2015	Revenue

<tbody>
</tbody>

I want to populate sheet 2 automatically based on the header and ID to create

	A	B	C
1	ID	Name	Value
2	1.01	Test 1	15000
3	1.02	Test 2	20000
4	1.03	Test 3	20000
5	1.04	Test 4	30000
6	1.05	Test 5	40000
7	1.06	Test 6	50000

<tbody>
</tbody>

I guess that it is done using INDEX and MATCH, but what is the correct syntax in the formula?

Thank you

Hi Robzx, welcome to the boards.

Based on your example data, you could use the following formula layout.

Assuming that on Sheet2 column A you have your ID's, in B2 you could enter this formula and drag-fill down the column to find all the corresponding names:

=INDEX(Sheet1!$B$2:$B$7,MATCH(Sheet2!A2,Sheet1!$A$2:$A$7,0))

=INDEX(Sheet1!$B$2:$B$7 is where you are expecting results from, in this case, the names from Sheet1 Column B

MATCH(Sheet2!A2 is the value you are using to lookup, in this case the ID from Sheet2 A2

Sheet1!$A$2:$A$7 is where you are searching for your lookup value.

If you then wanted to use this setup to find the values to go in Sheet2 column C, the formula would be updated to:

=INDEX(Sheet1!$E$2:$E$7,MATCH(Sheet2!A2,Sheet1!$A$2$:A$7,0))

Where Sheet1!$E$2:$E$7 is the column containing the required Value

I hope this helps.

 

[Robzx]

Thank you Fishboy, it helps greatly

 

[Fishboy]

Thank you Fishboy, it helps greatly

Happy to help

 

[BLB76]

FORMULA NEEDED;

I too need to copy data from one worksheet to another.

I need to copy/link three different fields on worksheet "Master!" to worksheet "Send2". I would like "Send2" to be automatically updated anytime the data on "Master1" changes.

The three fields on Master1 are "Mobile Text Address", "Business Email" and "Personal Email Address".

The three above fields may or may not have data in them.

When the data is copied/linked to "Send2" I would like to omit/eliminate blank fields.

Any assistance would be greatly appreciated. I have been reading and research for nearly a week and I am not making much progress.

THANKS IN ADVANCE FOR YOUR ASSISTANCE!!!!

 

[BLB76]

I have been able to get this following to work.

Can someone please tell me how to make the following 3 subs run as one?

THANK SO VERY MUCH FOR YOUR HELP!!!!!



Sub Business_Email()
Dim cell As Range, r As Long
r = 1
Application.ScreenUpdating = False
Sheets("Sheet4").Range("A2:A3000").ClearContents
For Each cell In Sheets("Sheet1").Range("A1:A3000")
If Len(cell) > 0 Then
Sheets("Sheet4").Range("A" & r).Value = cell.Value
r = r + 1
End If
Next cell
Application.ScreenUpdating = True
End Sub
-----------------------------------------

Sub Personal_Email()
Dim cell As Range, s As Long
s = 1
Application.ScreenUpdating = False
Sheets("Sheet4").Range("C2:C3000").ClearContents
For Each cell In Sheets("Sheet1").Range("C1:C1000")
If Len(cell) > 0 Then
Sheets("Sheet4").Range("C" & s).Value = cell.Value
s = s + 1
End If
Next cell
Application.ScreenUpdating = True
End Sub
---------------------------------------------


Sub Carrier()
Dim cell As Range, s As Long
t = 1
Application.ScreenUpdating = False
For Each cell In Sheets("Sheet1").Range("E1:E1000")
If Len(cell) > 0 Then
Sheets("Sheet4").Range("E" & t).Value = cell.Value
t = t + 1
End If
Next cell
Application.ScreenUpdating = True
End Sub