My spreadsheet uses alternating columns for actual and budget inputs
I would like to preload the budget for the whole year
But the ytd formula should only show results to match the input of actuals
In otherwords, If I input actuals for period 1, the YTD formulas for actuals and budget should return YTD period 1 only and ignore the remainder of the budget inputs
After period 2 actual inputs, the formulas shlould update to reflect period 2 YTD and so on and so forth
How can I accomplich this?
Sample of layout attached
Totals at Cols AK-AM
=> and for my ms excel has its columns (year,month,date) in this if i input the date that is base in the database range from StartDate TO its EndDate will automatically put the month and year which base also in my database (year,MonthNum)
HERE:
A1 B1 C1 Year Month Date A2=2014 B2=January C2=01/01/2014
I need to determine if the input date (Yearx) in a userform is a leap year. I tried doing this: Leap = Evaluate("MOD(Yearx, 4)") IF Leap = 0 then (show 29 days on my planner).
But no matter what date I put in, it generates "0" as the value for Leap, so indicates that February has 29 days. Obviously I'm not doing this right.
I am building a shared workbook that will have the same data on multiple worksheets.
The problem is that users are going to be entering updates to that data on individual sheets, not going thru and updating each sheet.
Also, some of the updates will be themselves updated as the day goes on.
What I need is for the workbook to only use the most recent data/update on all of the sheets.
Example: Sheet1 will have all of the info sorted by time. Sheet2 would have only the data for account "A" sorted by job number. Sheet3 would have account "B"...
When a change is made to any of these sheets I want the other sheets to be updated.
Now that I have wrote this out it seems to me that a "worksheet event" macro might do this for me...
But can you write an event macro into all the sheets that would not result in a loop (event macro on sheet1 changes sheet2 which triggers the sheet2 event macro...)?
I have a forecast monthly trial balance sheet and an Income Statement Analysis sheet. I am analyzing the Year to Date performance for Dec-2013. I need a formula that will match the income statement line i.e revenues, accounting expenses etc. and then sum horizontally from Jan-2013 to Dec 2013 ( if YTD month' Dec-2013' is greater than or equal to date range then sum horizontally the corresponding income statement lines up to the reporting month).
based on user date ranges entered on sheet1, I'm trying to write code that will write each month of the date range on other sheets across the 2nd row. at this point I'm getting "object required" error at "Set DateStart = Cells(2, 6)"
I also want the date format to be mmm-yy (Mar 14) on the sheets even if sheet1 has a different format. I tried using sourcerange instead of DateStart, but that didnt work either.
Code:
Dim projStartDate As Date Dim projEndDate As Date Dim DateStart As Date[code]....
formula off here i use all the time relating to finding and sumproducts for specific months and years i.e.
Jan 2008, Dec 2007.. depending on these dates excel searched through a specified range and returned me any values i wanted like No. of occurences, totals, sums etc etc it was a sumproduct formula...
is there any way i could specifiy a date i.e. Jan 2009, which would search column a and return the date /and/or an account number in column b, only if the date was during jan 2009?
The reason i want this is to use a lookup on the account numbers to return specific items of info, but i only need the account numbers if they occurred in specific months which i want to choose.
I have a column with dates of July 1 of every year. How can I use conditional formatting to shade a cell a certain color based on July 1 of every year? The cell for July 2013 should be shaded, when 2014 comes, it is no longer shaded, then 2014 becomes shaded, & so on...
1. I would like to be able to select a month from a drop down ( cell C4), and for Column B ('Cumulative Performance') to reflect the sum for each name between Jan and the month selected.
2. In Column D I would like to rank the relative position of the sum total; such that if I selected 'Dec', John would display '13' in D7, Anne '3' etc.
3. In Column E I would like to show by way of a coloured arrow (or even a smilie icon) the relative change in ranking of the sum totals evaluated for my chosen month with those calculated up until the previous month (e.g. for Anne, if I select June, the Jan to June total is 36 (rank 2 in the June total's), the May to Jan total for Anne is 32 (rank 1), therefore her relative rank movement between the June and May cumulatives moves down and cell E8 would show a red-down arrow (amber horizontal for no change and green up-arrow for an improvement in rank).
I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)
I am a production manager for a homebuilder and I am trying to create an Excel spreadsheet that will allow me to input a date in a column and have a second column dependant on the date and move as I change the date.
Since no work is done on weekends, I'd like the output column to skip weekends and move the input to the next available weekday. I have conditionally formatted the date to be a red fill as a visual cue but I'm not sure where to go from here.
I need some VBA code to hide columns if they are outside of a specified date range.
- the worksheet i need to run this VBA on is named 'Summary' - Columns A to G need to remain un-hidden at all times - from H17 to ZZ17 i have every month of the relevant years listed (all there chronologically) - D3 holds the 'Date From' variable - D4 holds the 'Date To' variable
I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.
The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.
Sheet 1 - I have columns filled with dates (weekdays only) For example 28-May, 29-May, 30-May up to 28-Nov. Above 10 rows below each date columns and each cell has some values.
Sheet 2 - I have Start date defined in B1 Cell, and End date defined in B2 Cell
I need defining the formula to sum all the cell values fall between the date range defined in Sheet 2 B1 and B2.
For Example if B1=28-May B2=30-May, I want the sum of all the numbers come under the range of 28-May to 30-May, with this above example, it should be 19.5.
I have one spreadsheet with a list of materials by material number. I have another with all sales activity. How can I pull into the first spreadsheet the date of the latest activity from the second spreadsheet?
I have a workbook to keep track of names and dates of a specific action. There are 10-15 names that recur on the list. The most recent action is added to the bottom of the list. The names are in column I and the date of the action is in column J.
The problem is having to scroll through the list to find the most recent action for each person. Ideally i'd like to have a list of the names in column N and then in column O the most recent date in the list for each name.
I want to lookup an ID which there may be three of, but I would want the most recent date out of the three ID. I can get this to work by putting the column in ascending order but this is not very practical.
Is there any way I can do a lookup and add a date parameter to lookup the most recent?
What I have is an output of about 30 worksheets of individual stock data in a workbook, all with the same column headings, laid out like the following (so that the Column Headings are stock data as recorded on the Date in Column A): <Stock Name> <Date> <Price> <Price to Book> <etc.> with the stock titles also as the names of the worksheet.
What I want to do is create a macro that outputs to a single worksheet where I can input the stock name and a certain date for the stock, and then have it insert the appropriate row from the stock's worksheet underneath.
So, if I say I want the data for Stock A on March 24, 2007, I can input those two and have it output the appropriate rows from the worksheet. Or, more generally, I can create a list of stock names and dates, and have the macro output that data underneath.
I have a list of numbers column A and a list of dates in column b, i need a formula which looks up the most recent for each of the numbers. can this be done?
Have a sheet with 24K rows. Column A is client name. Column B are check dates. The info is sorted by client then check date. Clients can have one to many check dates, so I client is listed as many times as there are check dates. If ten checks, then ten rows for that client. One check then one row for that client.
I only want to keep the last/most recent check date for each client, deleting all other rows for that client with check dates before the most recent. I believe I need to step through the file comparing column A, row by row, to see if I have a match for the next row in A. If no match I keep that row, because there is only one check, and move down one. If a match, then I delete the current row.
I'm trying to put a report together that shows the most recent salary increase for each employee. Below is a snap shot of what my pivot looks like - How can I format the report to only show the most current change - Which in this case would be the 2 highlighted rows....
I've searched the site, but have not found anything that matches what I want to do. I have a statistical spreadsheet that tracks machine accuracy by date. The spreadsheet has multiple columns per worksheet. The info is entered and sorted by date with oldest date first. I want to set up a seperate worksheet to find the most recent date from several different columns and populate it in a seperate cell. From there I'll do some calculations to identify how long it has been since a particular machine has been accuracy verified. Is there a formula to do this or does it have to be done through a macro?
