Log In
Register
Home
Active Member
June 14, 2020
Offline
I have a basic sales and report spreadsheet and i need to move pivot table data (item sold and Price) to an invoice.
I have a drop down on the invoice with customer names, which I would like to drop down and select customer which in turn would bring there sales items and price into an invoice.
Any help appreciated
See Answer
Trusted Members
December 20, 2019
Offline
The first thing i would say is be careful of merged cells on the invoice - they are just about the worse invention Excel has ever had and will do more harm than good the more you use Excel's features and functions.
What version of Excel are you using? The filter function was made for this type of thing and would be brilliant, but its only in Office 365
Active Member
June 14, 2020
Offline
Its an old version 2007
Trusted Members
December 20, 2019
Offline
Fair enough, so a bit of a work around but this is also dependant on your Pivot table being in the same format every time
We have a couple of counters - one for the first position the customer name appears in the pivot (=MATCH($A$11,Report!A:A,0)) and another for the last time it appears (in reality we are looking for "Customer Name"&" Total") (=MATCH($A$11&" Total",Report!A:A,0)). These 2 counters can be incorporated into the main formula if you wish but i thought i showed the idea better being seperate.
=IF(ROWS($A$15:A15)>$G$10-$G$9,"",INDEX(Report!B:B,$G$9+ROWS($A$15:A15)-1))
We then use Rows & and expanding range to count how many rows we have used and the formula turns off once we get past the last number
Change the name in the drop down and the invoice updates
Tested in Excel 2007 & Excel365
Purfleet
PS i delete your merged ranges and formatted as 'centre accross selection'
Answers Post
1 Guest(s)
