Having trouble across 2 sheets...

mighty mouse

Board Regular
Joined
Mar 17, 2005
Messages
140
If any cell of column D in sheet 1 is marked with an "x", I'd like for the corresponding name (in column A) in the same row that the "x" is marked to appear in column A of sheet 2. I'd like for the that name, however, to appear in a list on sheet 2-so that if that name happens to be in cell A95 on sheet 2, for it to appear in cell A1 (or the next empty A cell) in sheet 2. Then if the next box from column D marked "x" is in cell D106 of sheet 1, the name from A106 of sheet 1 will appear in cell A2 (just an example) of sheet 2. Likewise, if there are any cells in column D of sheet 1 without an "x", I'd like for those corresponding names to go into list format of column B of sheet 2. Hope that makes sense-thanks in advance for any help!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Your description is confusing. Anyway paste these codes into macro window ( alt F11)
Code:
Sub Macro3()
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To x
If Cells(a, 4) = "x" Then
c = Worksheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Cells(1, 6) = "=iserror(match(" & Cells(a, 1) & ",sheet2!A1:A" & c & ",0))"
If Cells(1, 6) = False Then
Worksheets("sheet2").Cells(c + 1, 1) = Cells(a, 1)
End If
Else
b = Worksheets("sheet2").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("sheet2").Cells(b + 1, 2) = Cells(a, 1)
Next a
End Sub
run the macro and see if what it is doing is what you want.
if there is no x in col D col A gets listed insheet2 col B. if there is x, and if name in col a matches name in sheet 2 col a, it gets listed in sheet2 col A
Ravi
 

Watch MrExcel Video

Forum statistics

Threads
1,130,117
Messages
5,640,208
Members
417,131
Latest member
Seanr19871

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
Top