
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
05Mar 0
12Mar 70
19Mar 210
26Mar 350 1050
02Apr 420
09Apr 455
16Apr 490
23Apr 525
30Apr 525 2555
07May 560
14May 595
21May 630
28May 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
05Mar 0
12Mar 70
19Mar 210
26Mar 350 1050
02Apr 420
09Apr 455
16Apr 490
23Apr 525
30Apr 525 2555
07May 560
14May 595
21May 630
28May 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
First, thanks very much for even reading this at all because it is very long and I apologize for that. It’s a pretty big problem.
I have a very complex formula to figure out that deals with budgetary costs being populated correctly for each month and I’m not having much luck. In fact, it’s a bit overwhelming and I feel that I’m in way over my head on this one. Can you help?
See the attached workbook. Be aware that this workbook is in a transitional mode with many things (mostly VBA) not working at the moment due to lots of recent additions of columns, one of which is what this post is about.
For ease of working with the specific variables, I’ve kept the sheet unlocked and have hidden all columns on the worksheet that don’t pertain to the problem, as there are many on this sheet. I also included 12 rows with preentered test data, all ready for the months to test formulas with.
I will try to be as descriptive as possible.
For each cell in the month columns (AP:BA) on the Budget_Data sheet, the formula needs to use variables located in columns Q, X and AG:AO to derive the correct value to be applied to any given month or months for the next budget year.
The variables a
Q14 = “Recurring Fee” – Must be selected from Q14 for any of the rest of this to apply at all. All other selections from Q 14 would be assumed to only apply to the month select in the Start Month in AK14.
X14 = The action for the Recurring Fee (Add, Remove, Renew – anything chosen outside of these is assumed to only apply to the Start Month chosen)
AG14 = The quantity of an item to be purchased – This would always be “1” with a Recurring Fee and the amount in AH14 will be applied to specific months based on other criteria, below.
AH14 = The total cost per item per Payment Cycle in AO14.
AK14 = The month that the recurring fee will begin
AL14 = The month number, a helper column for AK14
AM14 = The month that a recurring fee will end
AN14 = The month number, a helper column for AM14
AO14 = The payment cycle for the recurring fee (OneTime, Monthly, Quarterly, SemiAnnual, Annual)
Rule: There can’t be both a Start and End month for any record (row). It must be one or the other and the formula for each month needs to automatically adjust for this. For any Start Month, the monthly payment should begin in the selected month and continue through December, and for any End month, the payment should be applied to this month and all months prior to the End Month but both based on the Payment Cycle selected in AO14.
For instance, there are 3 actions in X14 and 5 in AO14 that the formula needs to account for:
The recurring fee will be Added and will start in a specific month (AK14) based on the Payment Cycle.
The recurring fee will be Renewed, in which case, it will be applied to all months, based on the Payment Cycle.
The recurring fee will be Removed and will be ending on a certain month but needs to be applied to this month and all months prior to the ending month based on the Payment Cycle.
Each of the above must apply to months based on the Payment Cycle chosen from AO14, OneTime, Monthly, Quarterly, SemiAnnual or Annual.
For “OneTime” fee, the amount in AH14 should be applied to the Start Month chosen in AK14.
For “Monthly”, the amount in AH14 should be applied to each month, beginning with the Start Month that was selected in AK14 and should continue through December.
For “Quarterly”, the amount should be applied to the Start Month in AK14 and then every 3 months through December.
For “SemiAnnual” the amount should be applied to the Start Month and then to the 6th month after, unless it goes beyond December, since we’re only working with one year at a time.
For “Annual”, the amount should only be applied to the Start Month just like a “One Time” fee.
Should any of the month cells see that the data in X14 = “Remove”, then they should look at the End Month in AM14 instead of the Start Month in AK14 and include only the End Month and months before the End Month for the amount in AH14 to be applied, basically the opposite of what they do using the Start Month explained above.
If you have any questions about anything I might have left out, please let me know.
And again, thanks very much for any help you can provide.
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 Piechart 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 Piechart 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.
Hello,
I had previously posted this under a separate title but I wasn't able to attach the updated file to the original thread.
My end result will be a template that can be resaved for the current month that will update all values automatically, the value in the prior month's Depreciation Expense list(G19H30) will be manually updated by copying down the formula to the current month and entering the expense amount for the prior month as well as any new additions to be added to the bottom.
When looking at Column H, you'll see a formula that I've pieced together to compare the dates in Column A with G1 and L1, resulting in the multiplication of the values in Column F by either the number of months that have passed this year or the number of months passed this year until the full term has been met. First if someone could let me know if there's a way to clean up this formula that would be great. My biggest concern at this point however is that if you look at H12, you'll see that I haven't copied the formula down. I want to add to the big nasty formula a piece of logic so if the amount of time passed from the date in A12 is less than the amount of time passed from G1 to L1, to multiply the value in F12 by the number of months passed between the A12 and L1, +1.
Thanks for any assistance.
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.
Hello there
I am currently struggling with a script i am trying to create. I have account numbers that get paid each month, and i have created an input box where one can type in the account number, it will locate it and give you the row number, then another will ask you for the month, and give you the column number. Now that part is working perfectly, but i would like to add the next step where it uses the Row Number from the account number and the Column number from the Month, with the use of an input box, it would place the amount typed, within the range it generated. For example: Account number 7894561230 is located in row 67, and July is located in Column 3. Then the next input box should place the Amount in Column 3, Row 67.
Please Login or Register to view this content.
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.

