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

R

Robzx

New Member
Joined
Jun 5, 2015
Messages
25
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

[TABLE="class: grid, width: 512, align: left"]
<colgroup><col style="width:48pt" span="8" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]
[/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[TD="width: 64"]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Description[/TD]
[TD]Currency[/TD]
[TD]Value[/TD]
[TD]Pricing Year[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1.01[/TD]
[TD]Test 1[/TD]
[TD]Core Data[/TD]
[TD]GBP[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]2012[/TD]
[TD]Capital[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1.02[/TD]
[TD]Test 2[/TD]
[TD] [/TD]
[TD]CAD[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]2013[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1.03[/TD]
[TD]Test 3[/TD]
[TD] [/TD]
[TD]USD[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]2014[/TD]
[TD]Capital[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1.04[/TD]
[TD]Test 4[/TD]
[TD] [/TD]
[TD]USD[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]2012[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1.05[/TD]
[TD]Test 5[/TD]
[TD] [/TD]
[TD]USD[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]2015[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1.06[/TD]
[TD]Test 6[/TD]
[TD] [/TD]
[TD]USD[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]2015[/TD]
[TD]Revenue[/TD]
[/TR]
</tbody>[/TABLE]















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

[TABLE="class: grid, width: 256, align: left"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1.01[/TD]
[TD]Test 1[/TD]
[TD="align: right"]15000[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1.02[/TD]
[TD]Test 2[/TD]
[TD="align: right"]20000[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1.03[/TD]
[TD]Test 3[/TD]
[TD="align: right"]20000[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1.04[/TD]
[TD]Test 4[/TD]
[TD="align: right"]30000[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1.05[/TD]
[TD]Test 5[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1.06[/TD]
[TD]Test 6[/TD]
[TD="align: right"]50000[/TD]
[/TR]
</tbody>[/TABLE]














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

Thank you
 
Last edited:
Sort by date Sort by votes
Robzx said:
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

[TABLE="class: grid, width: 512, align: left"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[TD="width: 64"]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Description[/TD]
[TD]Currency[/TD]
[TD]Value[/TD]
[TD]Pricing Year[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1.01[/TD]
[TD]Test 1[/TD]
[TD]Core Data[/TD]
[TD]GBP[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]2012[/TD]
[TD]Capital[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1.02[/TD]
[TD]Test 2[/TD]
[TD][/TD]
[TD]CAD[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]2013[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1.03[/TD]
[TD]Test 3[/TD]
[TD][/TD]
[TD]USD[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]2014[/TD]
[TD]Capital[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1.04[/TD]
[TD]Test 4[/TD]
[TD][/TD]
[TD]USD[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]2012[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1.05[/TD]
[TD]Test 5[/TD]
[TD][/TD]
[TD]USD[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]2015[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1.06[/TD]
[TD]Test 6[/TD]
[TD][/TD]
[TD]USD[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]2015[/TD]
[TD]Revenue[/TD]
[/TR]
</tbody>[/TABLE]















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

[TABLE="class: grid, width: 256, align: left"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1.01[/TD]
[TD]Test 1[/TD]
[TD="align: right"]15000[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1.02[/TD]
[TD]Test 2[/TD]
[TD="align: right"]20000[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1.03[/TD]
[TD]Test 3[/TD]
[TD="align: right"]20000[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1.04[/TD]
[TD]Test 4[/TD]
[TD="align: right"]30000[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1.05[/TD]
[TD]Test 5[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1.06[/TD]
[TD]Test 6[/TD]
[TD="align: right"]50000[/TD]
[/TR]
</tbody>[/TABLE]














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

Thank you
Click to expand...
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.
 
Upvote 0
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!!!!
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
How to get excel to auto-outline into groups using Excel Macro?
Replies
0
Views
282
Andy77
A
S
  • Solved
Auto Numbering in excel
Replies
6
Views
228
Joe4
Joe4
S
  • Solved
need spill formula
2
Replies
10
Views
478
hagia_sofia
hagia_sofia
N
  • Solved
Formula to consolidate Data and remove entries with no data in that year
2
Replies
10
Views
418
Newbie73
N
T
  • Solved
Fill a table cell with data from another table based on the adjacent table cell value
Replies
5
Views
167
Peter_SSs
Peter_SSs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top