Excel 2010 :: Checking Date For Two Columns?

Apr 15, 2013

I have two excel 2010 files that I want to run this on. Each one has about 10+ worksheets in them. I am looking to have a vba script that will look at columns D and E starting at row 4 and check if they are expiring in the next month or have already expired (before today's date). Then it would return a text file that will say which worksheet it is on, the row and column, and what date is in that cell.

View 9 Replies


ADVERTISEMENT

Excel 2010 :: Cross Checking Info With VLookup And Extracting Data From Sheet

May 9, 2014

So in my excel document I have it such that on the first sheet (Labeled 'Sorted') the data is set up as follows: LOCATION, EXTENSION, NAMEWith the appropriate data under each header. On the second sheet (Labeled 'Locations_Ext') I have a named range called Locations; it contains extensions from a separate document, names of people associated with the extensions from the document, and their locations on the map. I am trying to have it such that the excel matches the extension number given on the first sheet with the ones that I extracted from the document on the second and output the location into the first column on the first sheet. The code I have set up for that process is:

=VLOOKUP(B(//SomeValue),Locations,1,FALSE)

I then want the excel to cross check the information that I took from the Visio to see if the visio is up to date with the latest info we have received (Which is the data on the first page under Extension and Name). The code:

=IF(C(//SomeValue)=VLOOKUP(B(//SomeValue),Locations,3,False),"Good","Error")

Both codes seem to be giving me this: #N/A as the results for every cell.. I'm using MS 2010

View 2 Replies View Related

Excel 2010 :: Checking If (Trust Access To Visual Basic Project) Ticked

Sep 17, 2012

I have a macro that seems to work okay for older versions of Excel - I use Office XP (2002) tell me if this will work for newer versions (2007 / 2010)?

KB1017 - Trust access to VBA project

Code:
Function VBATrusted() As Boolean
On Error Resume Next
VBATrusted = (Application.VBE.VBProjects.Count) > 0
Exit Function
End Function
Private Sub Workbook_Open()
If Not VBATrustedAccess() Then

[code]....

I was thinking that i should probably have

VBATrusted = -1

as the third line so that it is negative unless the tick is there. No way of checking older versions right now though.

View 3 Replies View Related

Excel 2010 :: Userform Date Picker Textbox Will Not Select Current Date

Feb 10, 2012

I have userform with date pickers and have text boxes overlaid on these, when I select todays date from the date picker it does not display the current date in the text box (I have 8 date pickers on the userform). If I select another date then reselect the current date it works. It has occasionally worked but why.

Below is the code for populating the text box from the Date Picker.

Private Sub DTPicker1_Change()
TextBox1.Value = DTPicker1.Value
End Sub

The initialize userform code uses the following to format and set the textbox

Code:

TextBox1.Value = Format(Date, "dd-mmm-yy")
TextBox1.Value = ""

Windows 7 with Excel 2010

View 7 Replies View Related

Excel 2010 :: Converting Hijri Date To Gregorian Date From Another Cell

Apr 19, 2014

I have a spreadsheet where we will be inserting the Arabic Hijri date taken from a Customs document (bayan). On my spreadsheet I'd like to have a column immediately next to the Hijri date that would convert the Hijri date to the Gregorian date so both dates will be visible for our Arabic and Western personnel who will be using the spreadsheet.

I am using Microsoft Excel 2010.

View 3 Replies View Related

Excel 2010 :: Populate A Daily Calendar Using The Dates Between Date Of Arrival And Date Of Departure

May 30, 2013

I'm using Excel 2010. I need to populate a daily calendar with the number of nights spent, extracted from the Date of Arrival and Date of Departure of individuals.

View 2 Replies View Related

Excel 2010 :: Move Part Columns Of Data From 6 Columns To Form 1 Large Column In Column A?

Jan 31, 2013

I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.

I need to move part columns of data from 6 columns to form 1 large column in column A.

For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !

Also need to delete unnecessary 'page headers' throughout as in rows 2-6

View 2 Replies View Related

Excel 2010 :: Comparison On Two Columns

Jun 20, 2013

I have two columns (A and B) with the same data. Column A is missing a value that is in column B.

Column A has 11330 rows whereas Column B has 11331 rows.

I am trying to do a comparison on the two columns to determine which value is missing from Column A.

I am using Excel 2010.

View 3 Replies View Related

Excel 2010 :: Unable To Cut Columns?

Jul 25, 2013

I could swear I used to be able to cut and paste columns in Excel 2010, but for the past week I haven't been able to. When I click on a column and do a right click, "CUT" is greyed out. I can cut any section, but not a whole column.

View 3 Replies View Related

Excel 2010 :: Lookup With Different Columns

Mar 11, 2012

On my "Order" sheet, I enter an account no. that looks up the company name, address, etc on my "Accounts" sheet. Below the address info on the "Accounts" sheet is a list of the salespersons with their email address.

After entering the account no., I want to enter the salespersons name and automatically look up their email address. I can make the lookup function work if I tell the formula which column the salesperson is located in, but am lost how to write the formula to locate the salesperson based upon the column no. that contains their company info.

It's not pretty, but this works.

=IF(F1"",HLOOKUP(F1,ACCOUNTS!A1:Z100,ORDER!G9, FALSE),"")

F1 - contains the account no. entered on the order sheet.

Accounts!A1:Z100 - range containing columns of account info.

ORDER!G9 - This is the row number value I obtained by using a match function to lookup the Salespersons name entered in cell F8, then I add 1 to render the value of the row that contains the email address located directly below the salespersons name.

=(MATCH(F8,ACCOUNTS!B1:B100,FALSE)+1)

Herein lies my issue, the salesperson name will not always be in Column B

I can write another Match to obtain the column no, but then I am using another cell to hold a calculated value for my lookup statement.

ACCOUNTSABCD1CODACCT1ACCT2ACCT323 COMPANY 1COMPANY 2COMPANY 34ADDRESS ADDRESS ADDRESS 5   CITY STATE ZIPCITY STATE ZIPCITY STATE ZIP6TELEPHONETELEPHONETELEPHONE78910TOM JONESJANE DOEMARY SMITH11tjones@company1.comjdoe@company2.commsmith@company3.com12SALESPERSON 2SALESPERSON 213salesperson2@company1.comsalesperson2@company2.com14SALESPERSON 315salesperson3@company1.com
Excel 2010

In words, Find Mary Smiths email address, she works at the company with the account number in cell F1 on the order sheet.

View 6 Replies View Related

Excel 2010 :: Matching Between Columns

Apr 19, 2012

I am using Office 2010. I have a list of names in column A and a list of URLs in column B and I'm trying to find which ones have a match or partial match at least.

ex. (the name is in column A and the URL is in column B)

1 youtube www.youtube.com
2 Mr excel www.mrexcel.com
3 Wine Lover www.winelovers.com
4 Brian Jones www.ilovecats.com

In the example, I would like to highlight (or something) #s 1,2,3 as matches.

View 2 Replies View Related

Excel 2010 :: Compare 2 Columns With Dates?

Dec 11, 2013

I have 2 columns directly next to each other each containing dates.

Example 1:
F2 = 5/23/13
G2 = 5/23/14

Example 2:
F3 = 6/6/13
G3 = 4/11/14

I wanted to do a conditional formatting that would highlight the cell in
column G if the Month/Day doesn't match the Month/Day in column F

Is there a way to do this with Excel only looking at the Day/Month and ignoring the year??

View 3 Replies View Related

Excel 2010 :: Separate Two Rows To Two Columns?

Feb 26, 2014

I am trying to separate two rows to two columns. How can I do this for +200 data. For example,

1
Apple
2
Ball
3
Candy
....
I need this to be:
1 Apple
2 Ball
3 Candy
and so on.

show me how I can do this for Excel 2010.

View 3 Replies View Related

Excel 2010 :: Rolling (scrolling) Columns

Oct 24, 2013

I have a spreadsheet with standard rows and columns - nothing elaborate. There is a section with a column for each month of the year, with 10 line items showing monthly totals for various business functions. What I would like to do, if possible, is ... instead of showing all 12 columns (making for a very long spreadsheet), I would like to show only 4 of the 12 month columns (in a window) and scroll through the months with a scroll bar.

View 3 Replies View Related

Excel 2010 :: Selecting Certain Desired Columns At Once

Dec 5, 2011

I wish to select all the cells which contain the word " Available " in column A at once . But i didn't find any option to do so using excel's Go TO Special feature.

I am using Excel 2010.

View 4 Replies View Related

Excel 2010 :: Dropdown Box To Hide Columns

Jan 31, 2012

I have seen in a number of spreadsheets that you can create a drop down list using data validation that hides certain columns depending on what you select. this leads me on to my question...

I have created a Gantt chart for an entire year and what to put a drop down box in C1 that contains four selections (Q1, Q2, Q3, Q4) to represent the four quarters of the year. I want to work it so that when the user selects Q1 it hides the columns where the other Quarters of the year are kept so you can only see that quarter.

If that is possible I would then like a second drop down box that allows the user to select a specific week.

Q1 is columns AW:DI.

I'm using excel 2010.

View 1 Replies View Related

Excel 2010 :: Sum Multiple Columns To Get YTD Amount?

Jun 16, 2012

I have to construct a financial model for Senior Executives to show year to date spent amounts. I have my worksheet as follows:-

Cell A2,A3,A4.. to A100 has - Account Numbers (Ex. A/c. 4100..)
Cell B1, C1, D1.....has Jan2011,Feb2011,Mar2011.......and so on till Dec2011.
Cell B2 onwards, down and to right, all spent amounts by month

What I need is a formula to get year to date number, which will change to Executives requirement.

Cell-ABCDE
1Jan2011Feb2011Mar2011Apr2011
24100100100100100
34101200200200200
44102300300300300

Year to dateMar 2011( Months will be changed)
Account 4101( Accounts will be changed)
Amount should be 600 What Formula ?

My excel version is 2010.

View 7 Replies View Related

Excel 2010 :: Filtering Multiple Columns (OR)

Jul 18, 2012

I have a worksheet with

Col A being Name,
B being primary skill,
C secondary skill and
D tertiary skill.
(Sanitised example below)

There are about 15 diferent types of skills ("Skill x, Skill y etc") listed in each of column B,C and D.

I would like to have a filter (or similar) where all names would show if a certain skill is present in either column B, C or D.

For example, a filter that on the below spreadsheet would allow me to view the names of all people who have "Skill x" either as a primary, secondary or tertiary skill.

I am using Excel 2010.

Name
Primary
Secondary
Tertiary

Person 1
x

[Code] .........

View 3 Replies View Related

Excel 2010 :: VBA Selecting Only Certain Columns In A Table

Sep 10, 2012

I have a table in columns C:J, starting in row 7. I have a macro that selects and copies the whole table (after I filter the table) and pastes to another sheet. Recently I've realized that I can only copy/paste C:I or it will mess up my formulas during the process.

This is the code I have right now, which works beautifully for the whole table:

Code:
ActiveSheet.ListObjects("Table36").Range.AutoFilter Field:=6, Criteria1:= _
"=Alan", Operator:=xlOr, Criteria2:="="
Range(ActiveSheet.ListObjects(1)).Select
Selection.Copy

What I need is to know how to copy everything in the table except column J. I can't just copy a certain range cells because the number of rows is variable. There has to be a way to modify the ActiveSheet.ListObjects("Table36") right?

Using Excel 2010.

View 2 Replies View Related

Excel 2010 :: How To Freeze Top Rows And Columns

Sep 25, 2012

How do I freeze the top 6 rows and the first 3 columns in excel 2010 work-sheet so when scrolling they remain static.

View 2 Replies View Related

Excel 2010 :: Lock Rows And Columns

Dec 8, 2012

I have dedicated rows and column units.I do not want these to be changed from there current setting.Can just rows and columns be locked and password protected? If yes,where and how do i process this feature?

Excel 2010

View 9 Replies View Related

Excel 2010 :: Delete Empty Columns By VBA?

Aug 1, 2013

i am trying to come up with VBA code, sheet is attached. i have some columns that have headers but rows are empty . so the VBA should delete all the se columns entirely and leave those that has headers and have data in rows.

Excel 2010ABCDEFGHIJKLMNOPQRSTUVW1AccountUnitFund CodeDepartment
ActivityAnalysisTypecodedeskitemBegin DateQuantityUnit of MeasureAmount
CurrencyJob CodeEntry EventParent Budget Entry TypeOptionsLine CodeFunding
SourceFacilities and AdministrationCost Sharing2

[Code] ........

View 1 Replies View Related

Excel 2010 :: How To Highlight Incorrect Value From 3 Columns

Aug 19, 2013

I have a Excel 2010 spreadsheet that contains 3 columns of (£) values. I am wanting to highlight a cell if one of these values do not match the other 2 columns. e.g below I would need the middle column to be highlighted as it does not match the other 2 cells.

25,449.47
25,451.65
25449.47

The thing to point out is that it can be any of the cells that do not match, not necessarily the middle column and occasionally all cells will not match and all need to be highlighted.

I am sure I need to use the Conditional Formatting, but cannot figure the formula.

View 5 Replies View Related

Excel 2010 :: Combine 2 Columns Containing Numbers On It?

Sep 18, 2013

I would like to combine 2 columns, containing numbers on it, is it possible?

I have found the other option how to do it

=A2&B2 but then this doesn't work

I have
COL A COL B
12345 0000

The result I want would be= 000012345

View 2 Replies View Related

Excel 2010 :: How To Put Date Picker

Oct 15, 2010

I need to put date picker in excel 2010, the earlier doen't work.

View 5 Replies View Related

Excel 2010 :: Find Or Remove Duplicates In Columns

May 11, 2014

I have following sheet:
Data from A1: BU1644

I need to check, if in A1:BU1 are any duplicate words.
All the formulas I found deal with finding duplicates downward (like A1:A1000).
Have not seen any formula which works across (from left to right)

Is there an easy way in Excel 2010 either to tag/ and -or remove the duplicate which I could apply and then just copy downward? The formula must work from left to right, because many words repeat downward.

View 2 Replies View Related

Excel 2010 :: Columns That Are Dependent On Each Other Without Circular Reference?

Aug 7, 2014

I have two columns of data; one is in inches, one is in metres. If someone is inputting data from an original source that has the data in metres I want excel to automatically calculate and input the data into the inches column. Additionally, if they input the data from the source in inches, I want excel to convert it to metres and input into the other column automatically. The conversion factor from inches to metres is * by 0.0254. I am no expert but after looking for this on the internet I am sure this requires some sort of VBA event code (although I don't really know the first thing about VBA). The excel columns will be in the same worksheet. I don't have a circular reference like I would do using normal Excel formula? I am using Excel 2010.

View 2 Replies View Related

Excel 2010 :: Inserting Columns / Rows And Cells

Nov 20, 2013

For some reason I don't seem to be able to insert any columns/rows/cells in to my excel spreadsheet. This is a problem in both basis sheets and more advanced ones.

View 1 Replies View Related

Excel 2010 :: Selecting Corresponding Values From Multiple Columns

Feb 18, 2014

I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'

I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'

See the attached image to make it clearer..

Excel2010

Excelhelp.jpg

View 3 Replies View Related

Excel 2010 :: Sort Multiple Columns Simultaneously?

Jan 3, 2012

I am trying to sort each "Pct" column in descending order. Of course, I can do this manually, but I have over 100 to do, so I'd like to know how I can automate this (of course, the two columns to the left of "Pct" must move along with it).

delete
EFGHIJKLMNOPQ8BallFrqPct
BallFrqPct

[Code]....

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved