Automatically fill in a cell from data that is entered into another cell/column

[vlavarnw]

So I am trying to make an excel sheet for my product development department to keep track of receipts with their totals and a break down of these receipts (each item assigned a category "project" was bought for). What I am trying to do is have the Date, Location, Receipt Number (Which I am going to assign according to date/how many receipts from that day), Category, Item Description, and Price on one Sheet 1 and on Sheet 2 I will have Date, Receipt Number, and the Receipt Total.

Sheet 1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Location[/TD]
[TD]Receipt Number[/TD]
[TD]Category[/TD]
[TD]Item Description[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]07/01/2016[/TD]
[TD]Fry's Grocer[/TD]
[TD]0701A[/TD]
[TD]Project 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0701B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0701B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0701B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0701C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Receipt Number[/TD]
[TD]Receipt Total[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0701A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0701B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0701C[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 787"]
<colgroup><col><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
So what I am trying to do is as I enter date in the column Receipt Number from Sheet 1, Excel will automatically fill in the data into Sheet 2 in the column Receipt Number, but I do not want any duplicates in Sheet 2 to show up. This is due to I want to be able to take the individual information from sheet 1 and have it sum the total of the receipt into Sheet 2 for me for each receipt so I am not having to look at a lot of repeated numbers/prices. So if i want to look at just one receipt I can use Sheet 2 and compare to see if the price matches what is on the paper receipt.

I have tried using the formula below in Sheet 2 for cell B2 and did not have any luck:

=INDEX(Sheet1!$C$2:Sheet1!$C$5,MATCH(0,INDEX(COUNTIF($C$1:C1,Sheet1!$C$2:Sheet1!$C$5),0,0),0))

I have a feeling that I may need to use a VBA or Macro for this, but I am unfamiliar with VBA so I am not very sure how to go about using it if what I am trying to do is possible.

Any help would be great! Thanks!

 

[Roderick_E]

Pivot table would be easier. Otherwise you'll need something like =SUMIF(Sheet1!C:C,B2,Sheet1!F:F) in sheet 2 column C.

 

[vlavarnw]

Roderick_E,

Thanks for you reply. I am also not very familiar with Pivot Tables. Could you please describe how I would set it up in a pivot table?

Thanks

 

[Roderick_E]

Hi there. On sheet1 click on the top left header and then INSERT/PIVOT TABLE yes for new sheet.
Right click on the pivot table and show field.
Drag receipt number field into the pivot table then price next to it. Right click on price, select sum... play around with adding different fields as needed.

 

[vlavarnw]

Thank you very much! I was able to get it to work!

 

[Roderick_E]

You'll be a pivot table pro in no time