|
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
What I am looking for:
A formula that matches two numbers of one sheet and to two number of another sheet in the same w/b and bring the amount from same row column L.
Detail:
Every month I copy a sheet and give it a new name as of that month, then I add expenses in some of the cells in column ‘L’, there are almost 1000 rows to add, when the sum after adding does not tie to what it should be, I check manually if I made any typo error or missing or duplicating any additions, I have to go through it one cell at a time to all the cells weather I added in that cell or not, since I do no know, I just add new amount in existing amount.
I like to know the formula that I can compare column L with prior month’s column L to know where I added and the formula should deduct the prior month’s amount, leaving the amount just added.
For example March. Cell D54 = 900, April cell D54 = 1000, so that means I added $100
The problem is that I can not just deduct column L or March sheet to April sheet, column may have been sorted, more rows may have been added in new sheet etc. but there is one thing common, column B account #, column D project #, one account number can be more than one time and one project ID may be more than one time. But account number to corresponding project ID can only be one time.
I want to match account # and project ID to March sheet and bring March amount to compare.
Than
Similar Excel Video Tutorials
INDEX & MATCH functions Example 2!
- See how to use the INDEX & MATCH functions together when VLOOKUP or LOOKUP will not do the job! See how use the INDEX & MATCH functions togeth ...
Similar Topics
Hello all!
I would appreciate any assistance anyone can offer. I'll do my best to explain...I'm attempting to do something that seems completely simple but have proven not to be (at least for me). I keep track of my caseload at work (as required) - and have several formulas in excel that give me statistical information. However, I'm attempting to figure out how to keep track of *new* cases added each month. Sounds easy right? (How I wish it were) In order to do this (because of how I have structured my case load) I need to find a formula that would compare a range of cells to their "counterparts" and return a total of all values that were not equal. But I may be getting ahead of myself. Let me try my best to explain how I have it structured:
All my case names are in ROWS, and all the months of the year are columns. When a case is added to my caseload, I enter in an "A" in the cell that corresponds with the case and month. Each time a month goes by and a case stays open, I need to update the month column by inserting a value (just a simple "a"). A lously visual representation:
Jan Feb March April May
Joe A A A A
Fred A A CLOSED
Bob A A A
The best way to get a number based on how many cases were added for a particular month (in my mind) would be to return a sum based on values that equal each other in a particular range of connected values, those connected values being the present month and the month prior. Example: Case "BOB" was added in March, so case bob would have blank values in all cells for all the months up until March (every month after march would have a value entered as well until it was closed).
I was thinking the best way to accoplish what I wanted would be to compare the month the case was added with the month before. If I were able to do this on all cases each month, then I should be able to generate "5 cases added in April", "2 cases added in June" etc...
I've spent an embarassing amount of time trying to manipulate the Sum, Sumif, Sum(if) functions but to no avail. I'm not sure if I have the logic wrong or if I need to try some entirely different function. Any ideas whatsoever? I suspect I may have wasted my time because I just read over what I wrote here, and it hardly makes sense to me. Sigh.
Hi there
I have a workbook in Excel 2003 with two spreadsheet, a data sheet and a summary sheet. The data sheet contains account numbers in column A and monthly values from column B to M (April to March) for each account. Account numbers occur more than once in column A.
The summary sheet contains each account number as in the data sheet, but only once with a month column and a YTD (year to date) column. Part of my function is to report on a given month number as well as a year to date number as at that given month. For example if it is May, I need to report the results for May as well as April and May totals. Cell C 1 contains the value of the particular month (say month 1 equals April and month 12 equals March).
I need a formula or function to return the year to date results given a certain month (cell C1) for each account number. The sumif function does not help since it only returns values for 1 column. I need the formula to perform a sumif function but if I put in 2 in cell c1 I need the formula to add the values in columns B and C, if I put a 3 in cell C1 I need the formula to add the values in columns B, C and D.
Hey everyone! I'm trying to find a formula that would spread out a value amongst multiple cells along a bell curve.
Say I have $x,xxx. I want to spread that amount out in a certain proportion, regardless of how many cells I'm spreading it throughout.
Say I have a $10,000 project. I want to be able to spread it out like a bell curve in the cells I choose. For instance, if it was a five month project it would be distributed as
January - $1,000
February - $2,500
March - $3,000
April - $2,500
May - $1,000
If I added another month it would change to
January - $833.33
February - $1,666.66
March - $2,500.00
April - $2,500.00
May - $1,666.66
June - $833.33
I already thought of manually adding percentages to each cell, but the formula needs to be dynamic.
Thanks!
Hey everyone! I'm trying to find a formula that would spread out a value amongst multiple cells along a bell curve.
Say I have $x,xxx. I want to spread that amount out in a certain proportion, regardless of how many cells I'm spreading it throughout.
Say I have a $10,000 project. I want to be able to spread it out like a bell curve in the cells I choose. For instance, if it was a five month project it would be distributed as
January - $1,000
February - $2,500
March - $3,000
April - $2,500
May - $1,000
If I added another month it would change to
January - $833.33
February - $1,666.66
March - $2,500.00
April - $2,500.00
May - $1,666.66
June - $833.33
I already thought of manually adding percentages to each cell, but the formula needs to be dynamic.
Thanks!
Can anyone help?
I wanted to have the weeks of the month down one column = 52 week.
down the next column I have different amounts of money in that week.
some months have 4 weeks and other have 5. I wanted a program to say:
If you see a month "x" look at the next column and take that amount. Then on the next row you have month "x" again (week 2) go to the next column and take that amount and add it to week one. And so on until all 4/5 week are added to give on result.
Then the same for the next month...
month amount/week amount/month
05-Mar 0
12-Mar 70
19-Mar 210
26-Mar 350 1050
02-Apr 420
09-Apr 455
16-Apr 490
23-Apr 525
30-Apr 525 2555
07-May 560
14-May 595
21-May 630
28-May 630
This sort of thing, where the value of 1050 is all the amounts added together in the column next to march. and 2555 for April etc.
Many thanks for any of your help
D
This is going to take a while to work on. I'm pretty new to VB, but I thought this would be a fun personal project to learn excel better, and it will help me budget
I need to distribute the funds put into Amount Added (see below screenshot) to several other funds. Each fund needs to expect a certain amount of cash each month, and every time funds are put in, the amount still needed needs to appear below the current total of the account in red.
Distribution to each account could simply be a percentage. I'll see if I can write it in psuedo code.
Code:
Funds required:
Insurance($50/month)
Gas($80/month)
Saving($100/month)
Spend($50/month)
If AmountAdded(d4) != monthly requirement for each fund
then add 35% to Insurance(F4), 35% to Gas(H4), 20% to Saving(X4), and 10% to Spend(V4) //Make sure to add to bottom of column that the fund is in.
Display AmountRequired to meet monthly requirement in red beneath the amount added to the account
If AmountAdded(d4) = monthly requirement for each fund
Then add 50 to Insurance(F4), 80 to Gas(H4), 100 to Saving(X4), and 50 to Spend(V4)
Display current total of fund at bottom of column, (0, 1) from the last modification.
If (0, -1) from last modified cell is modified, move current total down again // that way I can add or subtract from the account. When I make //modifications, they should appear in blue.
If Gas and Insurance != AmountRequired at end of the month
Then Check Spend fund, subtract all available funds to equal AmountRequired
If Spend Fund < AmountRequired
Then subtract left over AmountRequired from Saving Fund
Display Current month Once in Column A, in line with the rows as modifications are made (add new month as modifications are made in a new month).
It would end up looking like this screenshot:
I wanted to have the weeks of the month down one column = 52 week.
down the next column I have different amounts of money in that week.
some months have 4 weeks and other have 5. I wanted a program to say:
If you see a month "x" look at the next column and take that amount. Then on the next row you have month "x" again (week 2) go to the next column and take that amount and add it to week one. And so on until all 4/5 week are added to give on result.
Then the same for the next month...
month amount/week amount/month
05-Mar 0
12-Mar 70
19-Mar 210
26-Mar 350 1050
02-Apr 420
09-Apr 455
16-Apr 490
23-Apr 525
30-Apr 525 2555
07-May 560
14-May 595
21-May 630
28-May 630
This sort of thing, where the value of 1050 is all the amounts added together in the column next to march. and 2555 for April etc.
Many thanks for any of your help
D
Hi
this is very dynamic requirement.
I have attached image as well sample sheet for what i need.
In brief:
I want to transfer(cut or moving) the cells to respective column but on same row from where is has been cut.
For example: (where , (comma) means different column
Account No.1
item33,quantity,amount,
item2,quantity,amount,
item48,quantity,amount,
Account No.2
item9,quantity,amount,
Account No.3
item19,quantity,amount,
item2,quantity,amount,
item6,quantity,amount,
item75,quantity,amount,
item52,quantity,amount,
Now i want put it like this
Account No.1 item33,quantity,amount,
Account No.1 item2,quantity,amount,
Account No.1 item48,quantity,amount,
Account No.2 item9,quantity,amount,
Account No.3 item19,quantity,amount,
Account No.3 item2,quantity,amount,
Account No.3 item6,quantity,amount,
Account No.3 item75,quantity,amount,
Account No.3 item52,quantity,amount,
I have also attached image and sample excel file.
Thanks
Hi,
I have a table with project names in column A, Resource names in column B,
and the amount of time each resource will spend on the project in Jan, Feb,
March etc in columns C, D, E etc.
On another sheet, I want a summary table, which will have the totals for
each month for each project. So in the summary, Column A will have 1 row for
each project, and Column B will show total for Jan etc.
The problem I have is, how do I make this dynamic? I.e. the number of
project will change, as will the number of resources on each project.
Thanks for any help anyone can give
Barbara
Hi,
I have a huge amount of data in an excel sheet in different columns. This is basically a month to month data for 12 months for various accounts. For each account (row), I want a formula or macro that, keeping the current month as the base month, looks at the all the remaining 11 months, and if any amount in that 11 months range is equal to the base month’s amount or marginally changes from it (according to a defined threshold, e.g., 10 in my sample), indicate in some manner in the last column (titled as “Result” in the attached sample sheet), so that all such accounts are identified.
I hope that I’ve defined the problem in an understandable form. I don’t know whether this is a doable thing or not. But looking at the amazing expertise of the Excel gurus in this forum, I have some hope. I shall be grateful if someone can help me in achieving this result.
Thanks and regards
I have two problems,
First I need to compare a month in a column with a date in a row.
For example: column b has dates like this 7/06, 8/06, etc.
In another sheet, i have the months across the top like January,
February. (these are in different columns).
I need to compare column b month with the month in the other sheet. If
it is true, then I need to take a value from column k (on the same
sheet as column b) and put it in that corresponding column. Like the
column for july would have an amount in it.
Second, I have column c that has number of months.
What I basically need is an if statement and lookup that will look at a
column with dates and put an amount from column k in the corresponding
column in another worksheet. At that point, it will look at the number
of months column and insert the amount in however many columns it says.
For example: column b, is 7/06. Column J (another sheet) is July, and
column k is 87,500 and column c is 6. It will look something like this:
January, February, March, April, May, June, July, Aug, Sept,
Oct, Nov, Dec
87500
87500 87500, 87500, 87500,87500
My lookup is like this:
=VLOOKUP(Sheet2!D4,startdate,1,FALSE)
but this gives me '38904'.
any help would be appreciated.
Hi
I am facing a issue of compiling the Data of Two Different sheets
Sheet on contain the Following
Name JAn 2008 Feb 2008 March 2008 April 2008 and So on
X
Y
Z
The Sheet Two contain the data in the Following Format
Name Month Amount
X Jan 2008 1000
X Feb 2008 1500
X March 2008 5000
y JAn 2008 1000
Y March 2008 500
Z March 2008 100
Z April 2008 1000
I want to take the Data from the Scond Sheet and put it in the First sheet Month wise
Please help me do this
Balendra Kumar
Hi, I have two sheets; Sheet 1 being the data and Sheet 2 is sheet needing a lookup. I only need formulas in the green area. I need sheet 2 to first do a match on the Project number on Sheet one and then retrieve the Month for the corresponding data. The project numbers are not always in the same order so I think I need to first do a VLookup to get the row of the project number in sheet 1 ie 25006, then check the row for text "A" and then go vertically up to grab the month "January".
Thanks in advance
******** ******************** src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********>
cellSpacing=0 cellPadding=0 align=center>
Microsoft Excel - Lookup.xls
___Running: 11.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A2
=
A
B
C
D
E
F
2
Sheet 1
3
Project #
January
February
March
April
May
4
25006
A
B
C
5
43087
A
B
C
6
22758
A
B
7
8
9
Sheet 2
10
Project #
A
B
C
11
43087
February
March
May
12
25006
January
February
April
13
22758
April
May
July
14
Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have got a excel sheet which is designed as follows:
1. Sr.
2. Start Time
3. Finish Time
4. Time Taken
5. Project
6. Description
7. Remarks
8. Total Hrs
Screen Shot Provided as:-
http://quick.holdthatpic.com/images/260981.jpg
The column 4 contains a formula for calculating the time taken between two intervals ( finish time - start time). Column 8 is
just to count the sum of Hours from the the time taken column.
Now i have multiple sheets added (one for each day) in one excel sheet. Taking this into consideration i want to write a
macro which would
1. First ask me if i want to calculate weekly or monthly progress.
2. In case of weekly sheet it would take account e.g # of hours spent on each project , the least amount of time on the
project etc
3. In case if the user selects month wise then user is asked for which month he wants to view the report (june,may etc). On
selecting the desired month it then do the same as specified in (2).
Problems:-
How could this be done? Is there any other solution other than using macro. Macro would just help me in automating my
tasks which is important. The report that I want to generate from the use of macro is Pie-chart representation ... which is governed on variables (e.g time take,total hrs and sr no of project). Its fairly simply to generate the report from one sheet but from multiple sheets it becomes a problem of correlation.
I have two worksheets. Sheet 1 contains two columns of data. Column A contains a list of account numbers and column B contains demographic information about the account number in column A. Sheet 2 contains a list of account numbers in column A and Column B is blank. I need a macro that will look at the account numbers in column A and if it finds the identical account number in column A of Sheet B, copy the demographic data in Column B of Sheet A to Column B of Sheet 2. Since the two worksheets do not necessarily contain the same account numbers, they will not match up row by row. If it cannot find a match in Sheet 2 for an account number in Sheet 1, I need it to skip to the next account number and repeat the process. When the macro reaches the last account number in Sheet 1, I need it to stop. This is a reoccurring monthly report with approximately 200 account numbers and the demographic data is carried forward month to month.
I have got a excel sheet which is designed as follows:
1. Sr.
2. Start Time
3. Finish Time
4. Time Taken
5. Project
6. Description
7. Remarks
8. Total Hrs
Screen Shot Provided as:-
http://quick.holdthatpic.com/images/260981.jpg
The column 4 contains a formula for calculating the time taken between two intervals ( finish time - start time). Column 8 is
just to count the sum of Hours from the the time taken column.
Now i have multiple sheets added (one for each day) in one excel sheet. Taking this into consideration i want to write a
macro which would
1. First ask me if i want to calculate weekly or monthly progress.
2. In case of weekly sheet it would take account e.g # of hours spent on each project , the least amount of time on the
project etc
3. In case if the user selects month wise then user is asked for which month he wants to view the report (june,may etc). On
selecting the desired month it then do the same as specified in (2).
Problems:-
How could this be done? Is there any other solution other than using macro. Macro would just help me in automating my
tasks which is important. The report that I want to generate from the use of macro is Pie-chart representation ... which is governed on variables (e.g time take,total hrs and sr no of project). Its fairly simply to generate the report from one sheet but from multiple sheets it becomes a problem of correlation.
I have been given two worksheets in one file. There are several columns of data with a column in the middle which contains the account number. The account number on each sheet, however, is in the same column but not in the same row. I need to subtract an amount from one worksheet to another for the same account number (this will need to be done for multiple columns).
For example, for Account #100 (in cell K1), on Sheet1, the amount in cell E1 needs to be subtracted from the amount in cell E5 on Sheet 2, Account #100 which is in Cell K5. I sure hope I'm making this clear. I did get SumIf formula to work but don't know now how to do the subtracting or if I'm on the wrong track. All this is really doing is bringing back the sum to a different column: =SUMIF('63303'!K13:K341,AAPAS!K13,'63303'!G13:G341).
Thanks in advance for any assistance you can give me forthis formula.
sinjin
Hi,
On the attached spreadsheet I would like to display client movements on the movments tab by selecting the month from a validation.
By movements I mean that any new additions are included in the Clients Added and any that are no longer there are Clients Removed. We would be looking at last month and the month previous, i.e. selecting March in the validation shows me what has changed between February and March.
I was thinking that we can use the account number and an IF statement to do the comparison but we also classify any client with a different Status as a movement on and off as well so we would need the formula to take Status into account too. In my sample see Mr Trotter account number 22222 has a different Status in Feb and March, this would be classified as a Client removed AND a Client Added.
Is there a simple method for doing this?
Thanks as always
Hello,
I've working on a sheet that tracks data over the life of a project. Projects can run anywhere from 1 month to 12 months. Right now I have each month the project is up labeled as "Month One", "Month Two", "Month Three", etc. People are finding it confusing because it's not "March", "April", "May".
What I'd like to do, in cell F5 have a data validation cell listing the months of the year (that part I can do) where the user can input the month the project is starting. In cell C5 they list how many months the project will run. The output would be cells F13:F24. F13 would be the same as F5 and F14:F24 would be the months moving forward equal to what was put in C5.
For instance:
If the project starts in March and runs for 3 months, F13=March F14=Apr F15=May.
If the project starts in July and runs for 2 months, F13=July F14=August
Can this be done? Any help would be greatly appreciated.
Thanks,
I have 2 excel documents. In the first I have a range of cells where one coumn is the date (in number month form, ex. march=3) and the other column is an amount.
In the other document I want to be able to sum the amount column of the first doc. based on which month it is designated to. Ex. show the sum of all amounts in month 3 in one cell, month 4 in another. Is there a formula for this? I have tried and tried.
I have 2 excel documents. In the first I have a range of cells where one coumn is the date (in number month form, ex. march=3) and the other column is an amount.
In the other document I want to be able to sum the amount column of the first doc. based on which month it is designated to. Ex. show the sum of all amounts in month 3 in one cell, month 4 in another. Is there a formula for this? I have tried and tried.
Hi!
i have an excel sheet having Name, Account No., Job Card No. Amount etc. The sheet will contain approx. 300 rows. i want to print only those rows which will contains data and the last two rows which will show the total of the above rows, i.e., total of amount. i want formula to print the sheet using the above criteria, i.e., excel will print those cells which contains Name, Account No., Job card no. Amount and the last two rows. in the excel sheet i am using formula using Name option Print_Area=OFFSET(FullPrint,0,0,Lastrows) which print only rows which contains data but not the last two rows. THe sheet will also be protected, only Account No., Sl. No. & Amount will be entered and the data will be sorted in ascending order according to Account No. . an excel sheet is attached for ready reference. Help me in my project.
Hi,
I have two worksheets "Expenses" and "Totals"
In "Expenses" Column A = Month
Column B = Name
Column E = Amount
In "Totals" Column A = Name
Col B-M are the months.
I would like the month columns in the "totals" sheet to reflect the total
"Amount" in "expenses" based on Month (Column A "Epenses") and the name in
Column A of "totals"
i use eg
=SUMPRODUCT(--(Expenses!A2:A6="September"),--(Expenses!B2:B6=A18),--(Expenses!E2:E6))
This works However
How do you set the formula up so that when new rows are added for workers
the formula can be copied?
and
How do you set the formula up so that when new rows are added in expenses
the totals change? So E2:E6 becomes E2:7 etc.
I think i have made sense
I have a home-made budget I created in Excel. The last page is a project page where I have preset project numbers I can assign to allocate portions of each month's budget toward. I have a SUMIF equation which basically says if the "Project #" shows in the "Description" column on the Month WS, then put the sum from the "Amount" column of the Month WS in the in the "Applied" column of the Projects WS.
When I use the formula for a single Month WS, i.e. "=SUMIF(February!H26:J106,"VK09001",February!C26:C106)" it calculates perfectly, however, when I use a range of sheets, i.e. "=SUMIF(January:December!H26:J106,"VK09001",January:December!C26:C106)" I get the "#VALUE!" Error. What is going on? I tested it by using just one Month WS, instead of a range of sheets, for each single sheet (i.e. January, February, March, etc. etc.) and it worked every time, so I don't think there is a problem with any single cell in the formula.
Can someone help me, please?!
I'm trying to extend a formula that creates a column in the middle of a sheet depending on what month it is (so if its november the formula creates a new column after october and heads it "over due amount" - I need help with adding a sum formula tothis new column (e.g: =SUM(AX3+AV3+AT3+AQ3+AN3+AL3+AJ3)) the difficulty is that the formula starts with the current month e.g November for this month, but december next month) and only sums every 2nd cell back to April. Once the formula is added it needs to be autofilled to all the rows in that column uptill the data finishes...can anyone help
|
|
