Excel 2010 :: Formula Referencing Date Headers And Column A Criteria And Return Sum
Jan 9, 2013
I have a rolling 12 month (each day in column) tab in Excel 2010 that references variances by ID number in column A, and the column headers are each day for 366 days (2011). I would like to create a tab that shows the past 10 day's variances by ID number. I haven't been able to write a formula that will look at the date headers and the row ID number to return the figure for that day. Here is a sample of the 12 month rolling and the 10 days at a glance that I want to populate.
Rolling 12 Months
IDName12/25/201212/26/201212/27/201212/28/201212/29/2012and so on
1234Employee Name - - - - (11.07)
1235Employee Name - - - 0.20 -
1236Employee Name - - - - -
1237Employee Name - - - - (1.00)
[Code] .........
View 4 Replies
ADVERTISEMENT
Feb 19, 2014
I have been struggling to find where my code is throwing up an error 1004 on the last line of the below code. I have a number of tables which will update automatically from Pivot tables on another sheet.
So the first part of the code is adding in the new column and then I want to autofill the date into the headers of the column which I thought the below would do, but I just don't understand why I keep getting the error. My data is dynamic as it will grow month by month which is why I am using R1C1 referencing.
Sheets("PNN Table").Cells(9, 16384).Select
ActiveCell.End(xlToLeft).Select
ActiveCell.Offset(0, -2).Select
Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, 1))
View 3 Replies
View Related
Mar 11, 2014
I am creating a template in Excel 2010 and am having some issues due to a combination of positive and negative values. Specifically, I have a named range of values that can have either a positive or negative deviation from zero. I need to return the value that has the greatest deviation from zero (either + or -) based on criteria in another named range I have used the following array formula with success for the "Y" range:
{=IF(PRB=M1,INDEX(Y,MATCH(MAX(ABS(Y)),ABS(Y),0)),MAX(Y))}
However, if there are not any negative values, as in the "Z" range, it only returns MAX(Z) for the entire range, when I would like ti to return "0.00134" (the largest deviation from zero based on the PRB range criterai. Would this be better accomplished using VBA?
PRB
Y
Z
A90B90
A90B-90
[Code].....
View 3 Replies
View Related
Mar 19, 2014
in Excel 2010, whether using VBA or otherwise;
1) how can I return the oldest date in the entire workbook (or at least in the entire sheet if it's not possible to find that date in the entire workbook).
2) how about the most recent date ?
View 2 Replies
View Related
Dec 14, 2011
I don't have Adobe Acrobat (just the reader) and I have Excel 2010.
I'd like a macro to do the following:
Take two ranges from a single sheet.
Print them to the same PDF with different header/footers using the built-in Excel PDF generator (File->Save & Send->Create PDF/XPS Document).
How can I do this? I already have a macro which exports the current sheet to a PDF, but I'm looking to export two ranges with difference header/footers to a single PDF.
Below is my current macro:
Sub PrintActiveSheetToPDF()
Dim strFileName As String, strDir As String, strFullPath As String
Dim fso As Scripting.FileSystemObject
Set fso = CreateObject("scripting.filesystemobject")
[Code] ........
View 1 Replies
View Related
Aug 23, 2013
Excel 2010. I have a workbook that has multiple sheets where some have headers and others do not. Some sheets are static in that no user input is required and others are dynamic so the toal number of pages per worksheet may vary based on how much information the user inputs.
An example:
Sheet 1 is static and requires no input, it does not have a header or a page number. For compliance reasons this sheet must be printed on top of all the other sheets in this policy
Sheet 2 is dynamic. This sheet can range from 2 pages to 5 pages depending on user input. This sheet must have page numbers starting with 1 and it must have a header on page 2 through the last page, but no header on page 1.
I am currently using a worksheet_activate sub to input data into the headers and footers of sheet 2. This works very well and puts the data where I want it to go just by using the "Different First Page" option in the headers design tab.
The problem arises when I select both sheets to print. Excel now thinks Sheet1 is the first page, so the first page of sheet 2 now has a header and the page number beings at 2 instead of 1.
Where I can stop this from happening?
Current sub:
Code:
Private Sub Worksheet_Activate()
' If WS is activated, place information in header
Application.ScreenUpdating = False
'Policy #
[Code] ..........
As I said, this works all well and good for when I'm just printing Sheet2 but when I have to print Sheet 1 & 2 together, it no longer prints the way it should. There are times when Sheet 2 will be the first page of the overall document but there are a few instances where it will be second to Sheet 1 and I run into this issue. Is there a way to specifically reference which headers/footers the code will input the data?
View 1 Replies
View Related
Mar 16, 2014
I want to return the column headings (the months) where there is a 'No' value, per person. How to do this?
Here's the screenshot
Screen Shot 2014-03-17 at 11.45.34 am.png
And here's a dummy version:
Example Spreadsheet.xlsx
View 3 Replies
View Related
Oct 20, 2011
how can i sum base on the Criteria in Columns G, in this sample the sum should be 67.
Sheet1ABCDEFG1V. GoodGoodFairBadN/AReported29853N/A8Bad31085328N/A410953N/A8Good510853N/A8Good61085378Good77853N/A8V. Good810853N/A8Good910753N/A8Reported108853N/A8Good11585308N/A1210653N/A8Good1367Excel 2010Worksheet FormulasCellFormulaG13=67
View 9 Replies
View Related
May 6, 2014
I have a schedule that covers 3 shifts for the entire month. I have another sheet that is used for crew sign in that I want to auto populate with all the people on that shift for that day.
I need to be able to use 2 different cells on the sign in sheet for reference for the formula (one for the day, and the other for the shift. ex. Day 3rd, shift D). I'm wanting to use an array formula to accomplish this. I've uploaded a stripped schedule as this is for the military and can't have any data on it that pertains to what or who it's for.
BTW our current method is to due it manually for every shift for every day that wastes about 12-16 man hours every week. I have excel 2010 at work currently. Access is out of the question and Macro's/vba may or may not work as security is always being increased on our systems so either a single or multiple formula is the route I'm looking to do that will work the the next few years I hope.
View 6 Replies
View Related
Aug 11, 2013
Excel - 2010
I have the following formula:
=SUMIFS(FEB!TRANS_Cost,FEB!TRANS_Cat1,CAT_Main3,FEB!TRANS_Cat2,"Computer Items",FEB!TRANS_Status,"Paid")
TRANS_Cost = B:B on FEB tab
TRANS_Cat1 = C:C on FEB tab
TRANS_Cat2 = D:D on FEB tab
CAT_Main3 = "Everyday_Expenses"
TRANS_Status = E:E
In short the formula adds the total cost of anything 'tagged' as Everyday_expenses, computer items, paid. I want to adapt it so that it takes the value from a defined cell such as Month, so that data can be pulled by month.
View 2 Replies
View Related
Apr 30, 2012
Using Excel 2003.
In column Y, I have a list of employee numbers (there are a random number of blank spaces in between each number)
In column AE, I have a list of clock in times (there are a random number of blank spaces in between each clock in time)
In column AY, I have a list of the same numbers from column Y (there are no blanks in this list)
Column BB. I am looking for a formula to place in cell BB2 and copy/paste down that will match the number in column Y and return the first non blank cell from that point in column AE.
Example:
10062 is employee number in cell AY2. Her employee number (10062) is also located in cell Y5. Her clock in time is located in cell AE7.
10099 is employee number in cell AY3. Her employee number (10099) is also located in cell Y14. Her clock in time is located in cell AE16.
What formula can I place in BB2 so that it returns the clock in time of employee 10062. Then copy and paste so that BB3 returns the clock in time of employee 10099?
View 6 Replies
View Related
Dec 21, 2011
I have a fairly large database in excel 2010 that in being updated everyday by a employee. In column A there are a set of dates all in the same format 9/1/2011 what i would like to happen is that if there are any dates in column A then it cut out the row and paste it into Sheet1 (the data is stored in sheet2).
View 6 Replies
View Related
Oct 31, 2011
I use Excel 2010, 64-bit-version with Windows 7. I have one workbook, where whenever I create a formula that references to one specific sheet, the Excel crashes and cannot be quitted even with Task Manager. I have to restart the whole system.
I do not have any worksheet events in either sheet. This is the same workbook that crashes if all cells are selected from the top-left corner of the worksheet.
I tried by deleting unnecessary COM add-ins but that didn't work.
View 1 Replies
View Related
Jun 7, 2014
I have a userform with two listboxes in them, i have set the columns in the listbox to 9 and would like to populate the column headers in the listboxes with the column header of one of the sheets.
View 5 Replies
View Related
Oct 11, 2012
USING EXCEL 2010
I'm looking to create a conditional format which highlights a date RED if it is within 90 days of today's date (the date the file is opened). What would the formula for that look like?
View 2 Replies
View Related
Nov 27, 2012
Using Office 2010, I am trying to do a averageifs formula for a specified date range. I can get it working by specifying the date range in the formula itself, however when "pointing" the formula to a specific cell with a date in it, the formula gives me a div/0 error.
Formula that works is:
=AVERAGEIFS('UHP Weld Data'!M:M,'UHP Weld Data'!B:B,"08/29/2012")
What I need is a version of: (currently not working)
=AVERAGEIFS('UHP Weld Data'!M:M,'UHP Weld Data'!B:B,"H1")
The date range will constantly change as I want it to show me the past 30 days only.
View 1 Replies
View Related
May 31, 2013
How to set up a formula on Excel 2010 using Conditional Formatting.
I want to identify duplicate account #'s within 15 days of the work date. Here is sample of the report.
Account Number
Facility
FC
Payor
Current Balance
Aging Level
Team Member
Work Date
87890
HHSS
1
BCBS OF TX (PPO)
$9,616.94
121+
Steven Johnson
5/20/2013
[Code] .........
View 4 Replies
View Related
Feb 12, 2014
Windows 8.1, Excel 2010. I have this code that updates the links. I use it in various workbooks, but they are all going to the same document; "Data Master (QC"
ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources
When I change computers, I have to manually tell each document where to fine the linked document. I want the documents being linked to look for the file path in a designated cell, i.e Cell E3 = "C:UsersOwnerDocuments1-QCDataData Master (QC).xlsm".
Something like ThisWorkbook.UpdateLink Name:=ThisWorkbook.Range("E3").Value
View 1 Replies
View Related
Jan 26, 2013
We have arrival dates and departure dates for guests and would like to figure out the number of meals needed on a particular date.
Named ranges: NumGuests _ArrivalDate _DepartureDate _CheckInTime _CheckOutTime
Need number of breakfasts
Number of Lunches
Number of Dinners for a given day of the week.
So if a guest is here say Jan 25 arriving after 9am, staying 2 nights they will have Lunch and Dinner on the 25th, BLD on the 26th and BL on the 27th. Since by default most arrive after 9 (miss breakfast), we can eliminate the checkin time for the breakfast calc and make the first one for the next day.
There might be other guests staying through that time period. The ******* would like to know how many meals they will need to prepare for the week.
Windows XP, Excel 2010
View 5 Replies
View Related
Nov 28, 2013
My and a work college needed to combine our separate excel worksheets into a single document.
Office 2013 didn't have a function to "import sheet from file" so we used open office to import my .xlsx worksheets.
After we finished importing we exported the final workbook as .xls (so I could open it).
After opening the workbook on my pc (excel 2013) i notice some of the sheets no longer have column headers, but the row headings still exists. (No ABCD, only 1234)
Also I am unable to use features such as "Freeze Pane"
I suspect this was caused by importing and exporting through open office?
View 4 Replies
View Related
Feb 7, 2014
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B
Expiry Date________Redeemed by
15/08/2014
15/02/2014
15/08/2014________John
15/02/2010________Marc
15/02/2011________Bob
View 4 Replies
View Related
Jun 15, 2012
I have a a spreadsheet that pulls user information from our corporate address book. I would then like to copy that data to another worksheet for additional data scrubbing. The problem I am facing is that, I can get the reference to work the first time but if I pull new data down, the destination spreadsheet now shows #REF!
I've tried using the below formulas but they all wind up the same way. ' Results' is the name of the spreadsheet I am making the reference to which has the dynamic data in it.
=Results!#REF!
=INDIRECT("Results!$B3")
=indirect (cell("results",B3)
View 1 Replies
View Related
Jun 13, 2006
How do I set up the formula bar so it doesn't cover the column headers?
View 1 Replies
View Related
Jun 24, 2011
Is there a formula similar to WORKDAY that would include weekends and make something due on the next business day? For example, I have a bill due on 6/1/11 and I need to follow up 5 days later - which would be normally 6/6/11 but the WORKDAY formula makes it 6/8/11 as it is adding 2 extra days.
I am using Excel 2010.
View 14 Replies
View Related
Nov 8, 2012
I'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.
If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.
I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.
View 1 Replies
View Related
Sep 10, 2013
I have a cell (A1) which contains the month. A2 contains the year. I need a formula which will return each date of the month.
View 7 Replies
View Related
Jul 23, 2012
I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.
My attempt is in cell D2 on sheet1.
View 5 Replies
View Related
Sep 22, 2013
I am attempting to create a workbook that will keep an inventory of items in a catalog that I own and want to order (worksheet1). Ultimately an order form would be filled in(on worksheet2). I would like to have the entire row of data copied (from worksheet1)to the next open/empty row on the order form (worksheet2), if any cell in column C (worksheet1) has data.
I have attached the workbook : Catalog Orders.xlsx
View 4 Replies
View Related
Feb 24, 2014
I am looking for a formula that will select a date in the month based on certain criteria. Found the choose function but not sure if I can really get that to work. I basically have a list of clients, with zip codes, restricted days of the week - and would like the system to group them by zip code and select the best day of the month to schedule an inspection but not pick he restricted day. The goal being have zip codes scheduled together - but on a day other than garbage day. Is this even doable?
View 2 Replies
View Related
Jun 6, 2007
I'm trying to use VBA code to delete certain rows based on a couple of criteria as follows:
1) If column header is "TERMINAL NAME" and the cell value in that column is "BONDDESK", I need the entire row deleted.
2) Similiarly, if the column header is "PRODUCT TYPE" and the cell value in that column is "CORP", that entire row needs deleted as well.
The information is coming from another source, so the columns may change from day to day, i.e, "TERMINAL NAME" may be in Column L one day but in Column O another day.....
View 6 Replies
View Related
