Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Find file by name Results

What I want is to do a max count in a column after determining if the name is what I have input.

Basically, I have a sheet in my workbook that contains a column with the max days "in a row" someone worked( column D in the example). I need to find a way for it to search a different column by whether it contains a name (column A in the example), then do a =max on the column (Column D again) with the max days in it only for the rows where the name matches. It then inputs the max number back to the year page in column M in the row for the name.

I am very sorry if the question is confusing, I am attaching an example spreadsheet.

I have attached both an xlsx file and an xls file. My default is the xlsx for Excel 2007.

Thanks in advance again.

Hi all,

I'm setting up an Excel (2000) sheet to record the results of a competition at my local photographic society. I'm trying to make it as simple as possible to fill out by the people using it, so have shaded the cells that require the user to put data in. I'll post a screenshot of the sheet, which is named "DPI", to make it easier:

(EDIT) screenshot removed - see attached file in post #3 below instead

I use one row for each member entering the competition. Each member can submit up to three photographs for judging. The names in B5:B30 are tied to the member number in A5:A30, and pulled in with VLOOKUP from a separate sheet containing all the club's members. The names in this example are, obviously, fakes. ;-)

The three blocks are to enter the image titles and associated scores. I have a range called "scores" which is defined as =DPI!$D$5:$D$30,DPI!$G$5:$G$30,DPI!$J$5:$J$30

The rank columns are simply calculated using the RANK function. E.g. the formula in E5 is =IF(ISBLANK(A5),"",RANK(D5,scores,0))

The cells at the top are calculated using the LARGE function upon the defined range "scores" (although they could equally use the SMALL function on the rank columsn instead).

The bit I'm stuck at is what I want it to say along the top. eg: "Leg 1 Winner: 30 is {insert image title} by {insert member name}"

Using the winner as an example, I want to take the winning score shown in D1, find it in the named array "scores", and return the image title to the left of it. Obviously I can't use VLOOKUP as the data is to the left of the lookup value as well as being in multiple columns. I wanted to use OFFSET to return the value of the column to the left, but to do that I need to pick up the reference of the cell containing "30" i.e. the winning score shown in cell D1.

How can I find this cell reference? Or is there a better way? I thought about the old MATCH/INDEX function, but INDEX doesn't seem to work very well with data in non-contiguous arrays as I'd have to specify which block to look in.

Once I can get the image title returned I hopefully sholdn't have any bother extending it to return the member name from a fixed column, but I can't figure out how to get the reference of the cells in the "scores" array that contain the winning scores shown in D1, D2 and D3.

Many many thanks in advance for any help! This has been driving me crazy for the past couple of days!

I have in Excel a list of my employees by Name/Race/Gender/Birthdate/Hire date/Salary. There are a couple things I want to do with this information but I can't find out how to do it in the help file or online. I have basic excel 2007 knowledge but nothing advanced. What I want to do is:

1. Find employees whose salary is between 50000 and 65000. (I'm assuming I can use some kind of formula?)

2. Find employees who are female and hired before a certain date, for example 1/1/99.

3. Find the number of employees in each race and income group.

This is what my table looks like (I changed names for privacy):

http://i238.photobucket.com/albums/f.../employees.jpg

Any help would be greatly appreciated.

I want to create a drop down list of customer names from their files. When I choose one it will open their file. Is it also possible to start typing their name it will autofill by closest spelling. Example; I type Ro and autofill finds Ron and opens file.
Thanks in advance.

Is there a way to find the largest amount of characters used in cell by column?
See I have a another program that can only import 53 characters per cell. So what I would like to do is see what row in column A has the most characters that way I can shrink it properly. For example,
Name
Joe Blow
Matthew black Jones
Sandra Martinez Gonzales
Priscilla Dow
David Gomez

So on the Name Column the longest name is Sandra Martinez Gonzales and I would like to know the amount of characters used. In this case, there are 24 characters used including spaces. Is there a simple way or formula that I can use? Because in reality, there can be thousand of records in a file and scrolling one by one is time consuming.
Thanks in advance.

Hi, I have a column with servers information. All servers at the same column. I would like to get each server information and copy into a new column. The information will be separeted by a cell with Server Information name.

I have a file attached and all data is under Column A. I have manually copy and past the information I need to the columns C, D and E.

Please, could someone help me doing that automaticaly. The original file has more them 500 servers.

Tks in advance
Eularios

I apologize if this question seems simple. I am a novice at Excel. I looked through the Help file to try and resolve my problem, but nothing seems to work. I have several subset groups of data in a spreadsheet, sorted by name. Some groups are four rows, some three, some two, and each subset group is totaled at the end of that subset. I want to be able to copy a new row of data from another spreadsheet and insert it as the new bottom row in one of my subsets, and have it automatically be included in the formula for the subset. I find that if I insert the row in the middle of a subset group, it becomes included in the formula and the new totals include the data. However, if I try to insert it directly above the subset total line, the data shows but does not get included in the formula or totals.

I would like to know how to insert it directly above the totals row and have it included, or how to move the row down to the bottom if I insert it in the middle so it does get included. Any help is greatly appreciated.

Hello Excellers,

The AutoCorrect function has proven to be an amazing time-saver for my company. We use it to enter customer names and even complete addresses w/ phone #s etc. into our spreadsheets.

For example: just by typing a three letter code like jsk and then hitting ENTER (or spacebar or any punctuation), it saves us from typing John Smith Kitchens, Inc. And by entering jsk1, we've got Excel correcting it as:

John Smith Kitchens, Inc.
123 Main Street
Theirtown, NH 55555
(603)555-5555 Phone
(603)555-5556 Fax

Entering the 100+ company names & addresses took some time up front but was worth every keystroke for the time it is saving us now. The problem is this...I'd like to import this AutoCorrect data into other PC's in our office, but I don't know if there is a way to do it. Is there a way to export this data or locate the hidden data file on the 1st PC and drop it in the next PC in the appropriate place where Excel will find it?

Any help would be greatly appreciated. Thanks in advance!

Troy

(BTW: Most of our PCs are using Windows XP and Excel 2003 with the exception of 1 using W2000 and 1 using W98.)

Hey,

Here's my problem/question.

I have a big excel file with about 6000 rows of data (mostly text). The 6,000 entries in Column B are all text and that is the data I'm interested in. Each entry contains a person's name. My goal is to figure out which name's are the most commonly listed. Yes I could just sort by that particular column and I've done that. But with 6000+ entries, it's still hard to tell which are listed the most.

So my aim would be something like the following...

If I have this in Column B (shorter version, what is listed after the '--' would be the cell contents):
Row1 -- Jake Scott
Row2 -- Donald Smith
Row3 -- Tim Matthews
Row4 -- Donald Smith
Row5 -- Jake Scott
Row6 -- Donald Smith

For it to be able to tell me that 'Jake Scott' occurs 2 times, 'Donald Smith' occurs 3 times, and 'Tim Matthews' occurs 1 time. And then sort those numbers so I can have a list of the most common entries.

Would this be possible? If so, how? If not, any other ideas outside of Excel?

regards,

Jon

Hi,

When I try to open any excel document by double clicking on it the following error is coming up. "Cannot find the file [file name] (or one of its components). Make sure the path and the filename are correct and that all required libraries are available. However if you first open excel and then go File >> Open >> and select the file from there the file would open without any problems.

I have re-installed office but this didn't solve the problem.

Can you please advise?

Thanks and Regards
Gordon Buhagiar

Hi All,

I am using a formula to lookup a range in another worksheet. The worksheet name itself can only be determined by performing a lookup in another range. I can retrieve the correct name but the formula does not recognize it correctly. When I try to use this formula the open file screen pops indicating it can not find the worksheet in the current workbook and asks me to choose another file.

=(VLOOKUP(VLOOKUP($D23,Names,6,FALSE),{WorksheetName}!$C$16:$N$69,12,FALSE))

I am trying to replace {WorksheetName} with what is below:

=(VLOOKUP(VLOOKUP($D23,Names,6,FALSE),VLOOKUP(H23,Standings,3,FALSE)!$C$16:$N$69,12,FALSE))

Any help would be appreciated!

Lets say i type some keywords in a cell then is it possible to give me the names of all the files in my computer which contain similar stuff...

IS it possible to use Excel Platform to search for any files by providing the closest name and extension..

If yes that would be really intersting to know the logic and code used ..

So all you Wizards pl
Please attach the file..with proper explanation
It would be a learning experience as i find this site extrememly useful

Hi everyone,

I’m new in VBA, trying to find out solutions to my problems bymyself. But below is something I couldn’t make any progress.
I have a monthly report file where I store input data in sheets named Jan, Feb etc. I produce the reports on separate sheets and name them as Jan Report ,Feb report as you may guess. I have 6-7 charts in report sheet where source data is from input sheets. When I start a report for new month, I simply copy and rename last month’s report sheet and work on it. However, all charts still refers to last month’s data and I’m updating source data of each series in each chart manually. This means about 50 link to be updated. I tried to find out a solution by writing something below. All I want to change sheet name in source data for next month.

Range("ag6").Select

Application.ScreenUpdating = False

sSheetName = Range("ag6").Value

For Each oChart In ActiveSheet.ChartObjects
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = Sheets(Range("ag5").Value).Range("AN8:AN38")
ActiveChart.SeriesCollection(2).Values = Sheets(Range("ag5").Value).Range("AuN8:Au38")

By the way, when I copy the sheet and rename fit for new report generation, chart names are changed in new sheet, so the SeriesCollection function will not help?. I need a code to cover all chart objects in the active sheet.

Thanks for your help.
Regards
Antak

i have a problem which i'm not sure can be sorted

The problem is as follows:

I have 100's of excel documents which i need to sort by date and place the results into a new excel workbook.

The excel documents all have a similar format in which they include the words:

Date: (The invoice date) & Invoice number (The Invoice number)

Is it possible to look to all the excel files and pull the information of :

Date & Invoice number.

and make a new excel file containing all this information and then sort it on Date

example:

Excel file 1: Name: Egremont Day Nursery.xls
Excel file 2: Name: Keswick Police Station.xls
Excel file 3: Name: Magistrades court.xls

and so forth

all the files have a similar format:

Date: 11 April 2004
Invoice Number: BARB001

ect.

Can excel look at each of these files find the the cell named date & Invoice number pull the data from them and write it to seperate file

1. Date: 11 April 2004 Invoice Number: Barb001
2. Date: 12 April 2004 Invoice Number: Barb002

and so on.

Hope this makes sense.

Good Afternoon

I find my skills a bit lacking when trying to retrieve a networked file. I have a link to a company network file. The link looks something like:

www dot company dot com/location/file.aspx

When you take the link it opens the save, open, close dialog box. I would like to use a personal macro to open the file so I can run a pivotable on the data and then save the file with a custom date-based name. The pivotable and save I'm comfortable with but the grabbing it from the link I'm hung up on (mental block probably because the dialog pops up). The file is updated weekly automatically by one of the enterprise solutions.

Open to suggestions, thanks!

Bob

Hello -

I am needing a script in Excel that I can press a button and it will ask for a folder name, then read through all the XLS files in the folder one by one and step through the individual tabs writing a segment from each tab to another spreadsheet

For example: I have 123.XLS, 456.XLS and 789.XLS all in one folder

123.XLS

Sheet1

Sheet2

Sheet3

456.XLS

SheetA

SheetB

789.XLS

SheetX

SheetY

SheetZ

MACRO/Script would reside in ABC.XLS in another folder so it is not read.

I would like this script to first open 123.XLS, finding it has 3 sheets. I would for example to read A1 and place it back into ABC.XLS, then go to sheet2 and read A1 and place it back into ABC.XLS, then sheet3 and read A1 and place it back into ABC.XLS

Then go to the next XLS file.

I hope this makes sense.

thanks.

Right so the problem is as follows if anybody could give me a few pointers:

I've what seems like a simple task to do. I'm coding in VBA and want to run a macro such that you identify two folders (each contain the same number of files, with the same name).
I then want to compare these files(.txt, .xls, .exp, .tab - Issues with file types?) whether that be by parsing the files or by loading them into excel in a temporary sheet, pair by pair and doing some sort of vlookup.

The point of this is to find any discrepancies and highlight them in a seperate sheet for further analysis. (can the FC function in DOS help at all?)

If anyone could point me in the direction of a VBA method or a starting point I would greatly appreciate it. Thanks in advance.

Hello,

I've got a quote template where if the user enters a customer contact information that doesn't appear on a database excel file, then it will automatically add it to the database file. I've got everything to work (selecting value, opening database file etc..) except for moving the contact information over to the database excel file. It works by getting the company name on the template file, then finds and selects the cell that has the same company name. I need it to then take the contact first name from the template and copy it to the same row (but 4 columns to the right) the company name is on in the database file. Hopefully that makes sense. Here is the code:
    Dim awb As Workbook                                                'quote template file
    Dim copycell As String
    Set awb = ActiveWorkbook   
    Dim wBook As Workbook
    On Error Resume Next
    Set wBook = Workbooks("Test Accounts by Contract Rep.xls") ' database file

    If wBook Is Nothing Then    'Not open
        Application.ScreenUpdating = False
        awb.Activate                                       
        Workbooks.Open filename:= _
                       "c:Test Accounts by Contract Rep.xls"     'database file

        awb.Activate                               
        Sheets("Data").Select
        copycell = Range("E148").Value
        Windows("Test Accounts by Contract Rep.xls").Activate   

        Range("A1").Select
        Cells.Find(What:=(copycell)).Select

' this following section is where it doesn't work

        With Worksheets("Test Accounts by Contract Rep.xls")

                .Cells(1, 4).Value = awb.Sheets("data").Range("E143").Value    'Contact First Name
                .Cells(1, 5).Value = awb.Sheets("data").Range("F143").Value    'Contact Last Name
                .Cells(1, 6).Value = awb.Sheets("data").Range("G143").Value    'Phone #
  End If
  
end sub


Hi

Im hoping someone can help me here.

I have an array named vArr - which is created from a range of two columns of data storing both Currencies and Exchange Rates. This will be a variable range and somedays could contain 3 members like below and other days contain 100 members.

CLP 999
HUF 666
IDR 100

vArr(1,1) is CLP and vArr(1,2) is 999
vArr(2,1) is HUF and vArr(2,2) is 666
vArr(3,1) is IDR and vArr(3,2) is 100

An example of my code is as follows:

    For I =
LBound(vArr) To UBound(vArr)

    'The search and replace code will be in here.

    Next I
I then have a list of Exchange Rate data (file attached).

I want to loop through this list and find ALL INSTANCES of member vArr(I,1) in column D and then divide the cell to the right (column E) by member vArr(I,2)

Can anyone help me on this?

Cheers

McCrimmon

I'm new to this forum, so please excuse me if my question is phrased awkwardly or if it is utterly asinine. I realize that what I'm trying to do is really better suited to Microsoft Access, but I've inherited a massive workbook created in Excel 2003 and have been told NOT to convert the project to Access.

I have a few different workbooks (Genre of Literature) containing multiple worksheets (each worksheet is a different author), each containing lists of library patrons (along with their unique library card number) who wish to be placed on reserve lists for brand new best sellers by their favorite authors. Since library patrons are able to select up to 20 authors across 5 different genres, their information can be listed in multiple sheets and/or workbooks.

Each time a patron makes changes to their personal information (e.g. getting a new library card with a different number, requesting large type books instead of regular type, adding an email address to their contact information), I must open each workbook and perform a clumsy find/replace function in order to update the information. If it's not a matter of simply replacing one value with another, there's manual typing or cutting and pasting that has to be done. Because there are 5 different workbooks, each containing numerous authors, this process takes a long time.

If I were to create a "master workbook" containing each library patron's name, unique library card number, and contact information, would it be possible to update the information in that master sheet ONLY and have every other occurrence of the patron's information update automatically? In other words, can a change in the row containing "master" information trigger changes in the rows containing that patron's information in several different workbooks with multiple sheets? There would have to be some way of linking the "master" information to the other worksheets and having those sheets refer back to the "master" sheet.

It looks like VLOOKUP might be helpful, but I just can't figure out how to write the formula; especially since the worksheets were set up by someone else long ago and they're not in LOOKUP Tables or tables at all. Please feel free to ask for clarification since I realize thia rambling message is confusing.

Thanks in advance!

-Martha


No luck finding an answer? You could always try Google.