Is this possible with VLOOKUP ?

[TURK]

Good day to you all,

I think it's reffered to a VLOOKUP with IF statement.
I've tried a lot of different combinations, but still can't get it to work.

I'm trying to import the data for unpaid jobs from the 2011 tab, to the Unpaid_Jobs tab, sort of, summary, where I can see at a glance which jobs have not been paid.

Would I be correct in saying that, if column 'F' is blank, and there is a '1' in column 'E' ( as I use a '1' to indicate a completed job, and an 'X' for a cancelled job ), that VLOOKUP would import the data in column 'A' ? I would be in the position to see the unpaid job number at a glance. I've been using filters for a while now, but its a bit impracticle.

heres the spreadsheet.

Unpaid_Jobs.xlsx
	A	B	C	D	E	F	G
1	jobno	date	customer	tech	complete	paid	ref
2	12345	01/02/2011	SMITH	JOHN	1	50.00	10021
3	12346	01/02/2011	JONES	JOHN	1	90.00	10022
4	12505	03/03/2011	SIMPSON	ALLAN	1
5	14400	03/04/2011	DAVIES	JAMES	1	80.00	11011
6	14732	04/06/2011	CLARK	DARREN	1
7	15022	04/06/2011	STEWART	JAMES	X
8
2011

 

[Trevor G]

What about this code to do the same

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> cpy()<br><SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">With</SPAN> Sheets("Sheet4") <SPAN style="color:#007F00">'Change sheet name</SPAN><br>    LR = .Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> LR<br>        <SPAN style="color:#00007F">If</SPAN> .Range("E" & i).Value = 1 And .Range("F" & i).Value = "" <SPAN style="color:#00007F">Then</SPAN> .Rows(i).Copy Destination:=Sheets("Sheet5").Range("A" & Rows.Count).End(xlUp).Offset(1)<br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[TURK]

Hi Trevor,

I do appreciate the help, and your time. But what the hell is that !?
what am i supposed to do with it. it looks nothing like an excel formula.
I'm a beginner, please be gentle

 

[Trevor G]

It is a visual basic program which you would run as a Macro from within the workbook. If you can't deal with this then perhaps hang on to see if someone can provide a formula.

 

[anglais428]

VB is probably the best way for this and Trevor G has already written the code for you.

 

[TURK]

Thanks for all your help, but if theres no other way to accomplish what I am trying to do, with VLOOKUP, i guess i'll have to learn Macro's ! Do I have to install Visual Basic on my PC ? I'm still learning the basics of formulas, in fact, my spreadsheet already has quite a few formulas in it. will running a macro also, have an effect on the entire workbook ?

TURK.

 

[John Davis]

Good day to you all,

I think it's reffered to a VLOOKUP with IF statement.
I've tried a lot of different combinations, but still can't get it to work.

I'm trying to import the data for unpaid jobs from the 2011 tab, to the Unpaid_Jobs tab, sort of, summary, where I can see at a glance which jobs have not been paid.

Would I be correct in saying that, if column 'F' is blank, and there is a '1' in column 'E' ( as I use a '1' to indicate a completed job, and an 'X' for a cancelled job ), that VLOOKUP would import the data in column 'A' ? I would be in the position to see the unpaid job number at a glance. I've been using filters for a while now, but its a bit impracticle.

heres the spreadsheet.

Unpaid_Jobs.xlsx
	A	B	C	D	E	F	G
1	jobno	date	customer	tech	complete	paid	ref
2	12345	01/02/2011	SMITH	JOHN	1	50.00	10021
3	12346	01/02/2011	JONES	JOHN	1	90.00	10022
4	12505	03/03/2011	SIMPSON	ALLAN	1
5	14400	03/04/2011	DAVIES	JAMES	1	80.00	11011
6	14732	04/06/2011	CLARK	DARREN	1
7	15022	04/06/2011	STEWART	JAMES	X
8
2011

Do you mean something like this in Column H?

=IF(AND(F3="",E3=1),VLOOKUP(A3,$A$1:$G$6,1,FALSE),"")

 

[im2bz2p345]

Thanks for all your help, but if theres no other way to accomplish what I am trying to do, with VLOOKUP, i guess i'll have to learn Macro's ! Do I have to install Visual Basic on my PC ? I'm still learning the basics of formulas, in fact, my spreadsheet already has quite a few formulas in it. will running a macro also, have an effect on the entire workbook ?

TURK.

Hi TURK,

You don't need to install anything additional. Here are some simple instructions on using VB in Excel.

To use the posted code;
Start the Visual Basic Editor (via Menu Tools, Macro, Visual Basic Editor, or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste your code
Note: All Macros start with "Sub MacroName()" and End with "End Sub"

How to create a button and assign a macro to it:
If you don't already have the "Forms" toolbar active, click on Tools>Customize>Toolbars and check "Forms". The toolbar will appear on your screen

Click on the Button icon, and drag out a button on the screen. When you release the left mouse button, an "Assign Macro" dialog box will appear. Highlight whatever macro you want, and click OK. The macro will now be assigned to that button.

You can also run the code by hitting Alt-F8 and selecting the macro name and clicking Run.

Hope that helps,

~ Im2bz2p345