Automatic Entry

[Dimitriy]

Hey Everybody,

I am trying to figure out a way to automatically enter data into more than one sheet. The data should be entered in the second sheet only if it meets specific criteria.

Please see the attached sample file. The ID column on the Main Sheet will be predetermined. If Attribute(Column B) says "secondary" then the ID number from column A needs to be copied onto the first empty row of the Secondary sheet.

As you can see i can't just use formulas, because there can't be blank rows in the secondary sheet (if criterion is not met).

Thanks in advance for your help!

[MaximS]

Hi Dimitriy,

Try below code which is based on your sample:

[VBA]
Sub Check_Me()
Dim wb As Workbook
Dim Ms As Worksheet
Dim Se As Worksheet
Dim L1 As Long, L2 As Long
Dim i As Long

Set wb = ThisWorkbook
Set Ms = wb.Worksheets("Main Sheet")
Set Se = wb.Worksheets("Secondary")
L1 = Ms.Range("B" & Rows.Count).End(xlUp).Row
L2 = Se.Range("A" & Rows.Count).End(xlUp).Row
For i = L1 To 2 Step -1
If Trim(Ms.Range("B" & i).Value) = "secondary" Then
L2 = L2 + 1
Se.Range("A" & L2).Value = Ms.Range("A" & i).Value
End If
Next i
End Sub

[/VBA]

[mikerickson]

This is a formula solution.
In the attachment, Secondary!A2:A100 has the array formula
=INDEX('Main Sheet'!A:A, SMALL(IF('Main Sheet'!B2:B100="secondary", ROW(A2:A100),9999), ROW(1:100)))&""

and Secondary!B2 has the formula =REPT("Secondary",(LEN(A2)>0)), dragged down.

Array formulas should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

[Dimitriy]

Thank you both for your reply. Mikericson can you shed some light on your formula. I've tried it in the sample2 file, and it does exactly what i want but i can't seem to implement it into my actual spreadsheet.

I am not familiar with SMALL formula, and I am not sure why you have an IF statement there. Please help me understand the logic of the whole equation.

Thank you.

[mikerickson]

IF('Main Sheet'!B2:B100="secondary", ROW(A2:A100),9999)

returns an array
if MainSheet has "secondary" in column B of a row, it returns the row number
if the column B entry is not "secondary", that element of the array is 9999 (a large number < 65536)

So IF(...) returns an arrray like
{2; 9999; 9999; 5; 6; 9999;...}

SMALL(IF(...),1) = 2
SMALL(IF(...),2) = 5

so, SMALL(IF(...), ROW(1:100)) is the same values as IF(...), except sorted.

INDEX(A:A, SMALL(...), 1) is the column A value of those rows.
Note that since "secondary" in B returns a small value, there are no blanks in INDEX(A:A, SMALL(...), 1) until after all the "secondary" matches are displayed.

For the 9999, I assumed that A9999 is empty
the &"" at the end of the formula is to prevent the formula from displaying an empty cell as 0. (cf. =A1 vs. =A1&"" when A1 is empty.)

[Dimitriy]

Works like a charm! Thank you so much!