VLOOKUP? Help please

[josh.clare]

Hello all,

I was wondering if someone could help/advise me.
I have a table in sheet1 with the following headings:

Company
Invoice Number
Paid Y/N

I have a table in sheet2 with the following headings:

Company
Total Invoices Paid
Total Invoices Unpaid

I want sheet2 (using formulas or a macro) to automatically fill its self in using the data from Sheet1.

Using the Company as the lookup, i then want it to count how invoices there are relating to that company and how many of them are paid/unpaid depending on the y (yes) or n (no) in the row next to it.

Hope this makes sence,
would really appreciate some help with this.

Thanks,
Josh

 

[repairman615]

Hello,

Try the sumifs (2007), You will need an amount. In this example the table is in A:D.

Adjust the ranges to suit:

<TABLE style="WIDTH: 423pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=564><COLGROUP><COL style="WIDTH: 423pt; mso-width-source: userset; mso-width-alt: 10313" width=564><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 423pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=564>=SUMIFS(Sheet1!$D$2:$D$6,Sheet1!$C$2:$C$6,"=Y",Sheet1!$A$2:$A$6,"="&Sheet1!$A2)</TD></TR></TBODY></TABLE>

The Amount is in col. D:D

 

[josh.clare]

Hey repairman,

thanks for the reply,
sorry to be a pain but i am completely lost. :s

Thanks,
Josh

 

[repairman615]

No worries,

What version excel are you using?

What range is your table located on sheets 1 and 2?

Are the sheets named "Sheet1" and "Sheet2"?

I am guessing that you are also using a column for amount of invoice on

sheet1. Is that correct?

Basically the formula sums the invoice amounts if the company name and "Y" exist in the table entry.

 

[josh.clare]

Sheet1 is named Invoice Details
Sheet2 is named Department Details

Sheet1 consists of:
Invoice number in Column A.
Company in Column H.
Paid Y/N in Column C.

Sheet2 consists of:
Company in Column A.
Total Invoices Paid in Column B.
Total Invoices Unpaid in Column C.
Total Invoices in Column D.

Hope this helps, sorry i should have been more clear.
Thanks,
Josh

 

[repairman615]

Try this for Invoice Paid:
<TABLE style="WIDTH: 411pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=548><COLGROUP><COL style="WIDTH: 411pt; mso-width-source: userset; mso-width-alt: 10020" width=548><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 411pt; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl65 height=20 width=548>=COUNTIFS(Table2[Paid],"=Y",Table2[Company],"="&'Department Details'!$A2)</TD></TR></TBODY></TABLE>

For Unpaid:
<TABLE style="WIDTH: 401pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=534><COLGROUP><COL style="WIDTH: 401pt; mso-width-source: userset; mso-width-alt: 9764" width=534><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 401pt; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl65 height=20 width=534>=COUNTIFS(Table2[Paid],"=N",Table2[Company],"="&'Department Details'!$A2)</TD></TR></TBODY></TABLE>

For total for that company:
<TABLE style="WIDTH: 383pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=510><COLGROUP><COL style="WIDTH: 383pt; mso-width-source: userset; mso-width-alt: 9325" width=510><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 383pt; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl65 height=20 width=510>=COUNTIF(Table2[Company],"="&'Department Details'!$A2)</TD></TR></TBODY></TABLE>

Note that the table on the Invoice details sheet is named table2 in the above examples.

This should count the invoices for the givin company.
2007 and up.

 

[repairman615]

I like this even better:

1
<TABLE style="WIDTH: 455pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=606><COLGROUP><COL style="WIDTH: 455pt; mso-width-source: userset; mso-width-alt: 11081" width=606><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 455pt; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl65 height=20 width=606>=COUNTIFS(Table2[Paid],"=Y",Table2[Company],"="&Table1[[#This Row],[Company]])</TD></TR></TBODY></TABLE>

2
<TABLE style="WIDTH: 455pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=606><COLGROUP><COL style="WIDTH: 455pt; mso-width-source: userset; mso-width-alt: 11081" width=606><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 455pt; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl65 height=20 width=606>=COUNTIFS(Table2[Paid],"=N",Table2[Company],"="&Table1[[#This Row],[Company]])</TD></TR></TBODY></TABLE>

3
<TABLE style="WIDTH: 383pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=510><COLGROUP><COL style="WIDTH: 383pt; mso-width-source: userset; mso-width-alt: 9325" width=510><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 383pt; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl65 height=20 width=510>=COUNTIF(Table2[Company],"="&Table1[[#This Row],[Company]])</TD></TR></TBODY></TABLE>


Uses a table on department details sheet named table 1.

 

[josh.clare]

i managed to get the 1st set of formulas working! they work brilliantly!
thank you very much!

couldnt get the second set working :s.

Thanks,
Josh

 

[repairman615]

Check to see what the table name (which may need to be changed) is on the department details sheet if you would like to use the last set.

Glad you have got it as I also use tables on different sheets for invoicing. I can relate to how nice it becomes when the formulas fill out the data for you.

 

[josh.clare]

thanks alot! really appreciate it.
just a quick one, is it possible to get a list of the companies from Column H - Invoice Details in Column A - Department Details but filtering out the duplicates?

Thanks,
Josh