Budget Help

KenWright64

New Member
Joined
Nov 16, 2005
Messages
8
I run a budget account at the end of each month for safety equipment,
I manually cut each persons withdrawal for that month and paste onto their own sheet as I cannot figure out how to lookup on the issue sheet and take out all the different issues to individual persons, I can manage to get one column of info and thats it. This takes me ages as ther is an average of 1500 withdrawals a month. Thank you, where would I be without this invaluable site?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
KenWright64

If you could give a few rows/columns of sample data from your 'issue sheet' we might be able to tell you how to get it to each individual sheet without manual cut and paste. You should also tell us what the layout of an individual person's sheet looks like so we know where the data has to go.
 
Upvote 0
Try using a pivot table.

Select a cell in your data area and the Data/ pivottables&pivotcharts/
click next and ensure the range includes all your data.
click next and then layout. drag and drop the data you want in to your report
click ok and then finish it will put the data into a new sheet for you.
 
Upvote 0
Safety.xls
ABCDEFG
3SalaryIssue Sheet
4NumberNamePart DescriptionQtyDatePriceLine Total
50068CHRIS C DUNCANX-HEAVY/SUPREME GLOVE (M)1203/10/2005 06:157.2386.76
60068CHRIS C DUNCANSURETOUCH V70N SZ.81/2103/10/2005 06:162.062.06
70131DAVID ****ROFTDECIGUARD EAR PLUGS103/10/2005 06:1611.6611.66
80131DAVID ****ROFTSUPER SOFT EAR PLUGS103/10/2005 06:1615.9215.92
90667PHILIP HOWARD19" KEVLAR ARMLETS (SHINEY)UV10103/10/2005 14:4719.9719.97
100667PHILIP HOWARDSURETOUCH V70N SZ.81/2103/10/2005 22:112.062.06
110082STEVEN ROBINSONX-HEAVY/SUPREME GLOVE (M)1205/10/2005 08:157.2386.76
120068CHRIS C DUNCANX-HEAVY/SUPREME GLOVE (M)1205/10/2005 09:267.2386.76
13
14
15SalaryIndividuals sheet
16NumberNamePart DescriptionQtyDatePriceLine Total
170068CHRIS C DUNCANX-HEAVY/SUPREME GLOVE (M)1203/10/2005 06:157.2386.76
180068CHRIS C DUNCANSURETOUCH V70N SZ.81/2103/10/2005 06:162.062.06
190068CHRIS C DUNCANX-HEAVY/SUPREME GLOVE (M)1205/10/2005 09:267.2386.76
Issues
 
Upvote 0
You're not going to be able to do this via a formula. You might be able to save time, however, by changing your process. On the assumption that you're currently copying and pasting one row at a time (and I know it's dangerous to assume :wink: ), I would suggest using auto filter.

Use auto filter on your issue sheet. Filter by Salary Number and then copy the visible cells to the individual's sheet.

Hope this helps you out!
 
Upvote 0
Any particular reason why you have a different sheet for each person? What are the different sheets used for? If it's just to show each person's summary, consider Barrie's autofilter suggestion, which would eliminate your need to cut and paste. If it's to group each person's items together, I'm not convinced that a pivot table wouldn't accomplish that...
 
Upvote 0
KenWright64

Without knowing your exact circumstances, I suspect that AutoFilter or PivotTable as suggested by Brian, Barrie & Oaktree is probably the best solution. However, in the end, if you want to go with formulas, I believe you can - with the use of 'helper' columns as follows.

On the issues sheet add the helper column 'Name & Count' as shown below. It would be better if this new column can be inseted to the left of the existing data as shown, but if not, alternative formulas on the individual person's sheet can be substituted. This helper column A can be hidden once it is populated with formulas if required. The formula in A5 (copied down is):
=C5&COUNTIF(C$5:C5,C5)
Mr Excel.xls
ABCDEFGH
3SalaryIssue Sheet
4Name & CountNumberNamePart DescriptionQtyDatePriceLine Total
5CHRIS C DUNCAN168CHRIS C DUNCANX-HEAVY/SUPREME GLOVE (M)1203/10/2005 6:157.2386.76
6CHRIS C DUNCAN268CHRIS C DUNCANSURETOUCH V70N SZ.81/2103/10/2005 6:162.062.06
7DAVID ****ROFT1131DAVID ****ROFTDECIGUARD EAR PLUGS103/10/2005 6:1611.6611.66
8DAVID ****ROFT2131DAVID ****ROFTSUPER SOFT EAR PLUGS103/10/2005 6:1615.9215.92
9PHILIP HOWARD1667PHILIP HOWARD19" KEVLAR ARMLETS (SHINEY)UV10103/10/2005 14:4719.9719.97
10PHILIP HOWARD2667PHILIP HOWARDSURETOUCH V70N SZ.81/2103/10/2005 22:112.062.06
11STEVEN ROBINSON182STEVEN ROBINSONX-HEAVY/SUPREME GLOVE (M)1205/10/2005 8:157.2386.76
12CHRIS C DUNCAN368CHRIS C DUNCANX-HEAVY/SUPREME GLOVE (M)1205/10/2005 9:267.2386.76
130
Issues


On the individual's sheet, also insert a new column A. In A4 put the person's name.
In A3: =COUNTIF(Issues!A:A,A4&"*")
In A5 (copied down): =IF(ROW()>ROW($A$4)+$A$3,"",VLOOKUP($A$4&ROW()-ROW($A$4),Issues!$A:$H,COLUMN(),0))
Again, column A could be hidden if desired.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top