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

vlavarnw

New Member
Joined
Jul 6, 2016
Messages
3
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

DateLocationReceipt NumberCategoryItem DescriptionPrice
07/01/2016Fry's Grocer0701AProject 1
0701B
0701B
0701B
0701C

<tbody>
</tbody>


Sheet 2
DateReceipt NumberReceipt Total
0701A
0701B
0701C

<tbody>
</tbody>



<colgroup><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
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!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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

New Member
Joined
Jul 6, 2016
Messages
3
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

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,295
Messages
5,595,294
Members
413,984
Latest member
stikpet

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