Table Names Revert To Cell Names When File Is Reopened

Jan 4, 2013

I have a worksheet with many tables that I use in formulas.

I like tables for a couple reasons, one being the ability to insert/delete rows without affecting the rest of that worksheet row.

Also, automatic copy of formulas/formatting is great.

But, what I really like about tables is the ability to use the naming conventions in formulas.

Problem is when I save this worksheet, after I close it and open it back up, all table references in my formulas have been converted to cell references.

Example:

Code:

=IF((SUMIF('Quote 1'!$M$28:$M$43,">"&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&tblOSSRV[Min Order Cost])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],""&tblOSSRV[Min Parts Per Line])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],"

View 4 Replies


ADVERTISEMENT

Create Array Of File Names/sheet Names

May 1, 2008

Two part question:

1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?

2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).

View 9 Replies View Related

Folder Names Instead Of File Names/macro

Dec 10, 2008

I need to make this macro read FOLDER names instead of FILE names. When I posted this question yesterday to get this macro, I wasn't told that each file in its own folder. I need the folder names now.
_____________________________________

Sub test()
With Application.FileSearch
.NewSearch
.LookIn = "C:Ford"
.SearchSubFolders = False
.Filename = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Next i
Else
Cells(i, 1) = "No files Found"
End If
End With
End Sub

View 9 Replies View Related

File Names :: File Renaming Each With The Names I Have On Another List

Jun 13, 2008

I have a task I would like some assistance with…

I have a work book that I have to copy over 70 times for over 70 work locations. As you can see, this will require different file names for each location.

I would like some have help with a code that I can use. If possialbe I like a code that will make copies of the file renaming each with the names I have on another list. Is this feasible?

View 9 Replies View Related

Sort Table (Names And Values) From Cell

Jun 7, 2014

I am keeping a spreadsheet for an auction

We have 20 people and each person gets a total bid for 48 items.

if person1 = 55,person2 = 100,person3 = 76,person4 = 67,person5 = 49 etc.

The person's names will be in column C while values in D in cell G3 will hold the total bid for the items each week updating.

What I am trying to do is sort the table (names & values) from cell G3.

So if each week G3 total gets bigger then the table sorts to match.

If the overall total bid after 48 weeks (G3) was 52 then the table sorts with 55 on top (because it's closer) 49 next.

The closer the person's bid to the total bid the further up the table the person will be.

I have uploaded an example with before and after sample.BidExample.xlsx

View 8 Replies View Related

How To Populate Table With Names Based On Cell Numbers

Mar 13, 2014

I have a column (A) with names, Column (B) with platoon numbers. On another sheet I have a table for each platoon and I want it to be populated with the names of each platoon indevidually. The names are alphabetically and the platoon numbers are mixed.

Column A Column B
Name Platoon
J Goodman 1
L Barns 7
H Law 1
B Aims 10

and so on....

I want all the names from platoon 1 to appear in the table for platoon 1. I have about 3000 members and 40 platoons.

View 3 Replies View Related

Delete Characters Unallowed In File Names In Cell Text

Feb 14, 2012

How can I delete characters unallowed in file names in cell text all in one go instead of below long macro. (i.e. can you minimize the below vba)

These characters that I wanna delete
"/", "", ":", "*", "?", "< ", ">", "|"

Code:
Range("A1").Select
Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

[Code].....

View 6 Replies View Related

Create Dropdown That Will List All Names Starting With Initial Letter Of Names?

Jan 13, 2014

I have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears. I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name.

View 9 Replies View Related

Can Create Data Validation List Of Names Created In Name Box Or Sheet Tab Names

May 7, 2012

Can I create data validation list of the names created in the name box or of the sheet tab names?

View 5 Replies View Related

Excel 2010 :: VBA - Hide Sheets Using Code Names Not Sheet Names

Oct 15, 2013

Code:

Sheets(Array("Sheet 1", "Sheet 2")).Visible = False

How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?

Want to ensure my code will work if the user changes the sheet name.

View 2 Replies View Related

List Sheet Names And Internal Names Within Workbook

Feb 25, 2011

Is it possible to produce a list on a new worksheet of all sheet names and their their internal names within a workbook?

If so I would like the tabbed name's to begin in say A2 with the corresponding internal name in B2.

View 3 Replies View Related

Looking Names In A List With Names Written Differently And With Duplicates

Aug 26, 2008

I am using Excel 2003 and Windows XP.

I have been given a list of my firm’s target clients (in excel) and an opportunities report (exported into excel) from our CRM system, which lists all the opportunities (i.e. opportunities to sell/provide products/services) that have been created for each client. Some of the column headings in the opportunities report are as follows:

Client; Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created etc.

What I need to do is lookup each client, from the target clients listing, in the opportunities report to see whether an opportunity has been created; and if so, return the row of values (i.e. the Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created) for that client. The result will be placed next to the name of the client in the target client worksheet.

I have a couple of problems. Initially I tried to use the VLOOKUP function to lookup the client name in the opportunities report and return the Opportunity ID (I then planned to use the same formula to return values from the other columns); however, as the client names in the target client listing were not always written the same way as they were in the opportunities report, the formula often returned #N/A. The formula I used was

=VLOOKUP(A8,'Opportunities Report'!A2:F51,2,FALSE)

So for example, the first client that I was looking up was written as “ABC Ltd” but in the opportunities report it was written as “ABC Limited”.

My second problem was that for some clients, there were multiple opportunities listed in the opportunities report. Where this was the case, there was a separate row (repeating the client name in the first column) for each opportunity created. I think that was messing up my VLOOKUP formula as well.

Is there a way to look up the client name, from the target client listing, in the opportunities report even if it’s slightly different and return the row of values for each opportunity created for that client on a separate row?

View 9 Replies View Related

Pulling Out Single Names From A String Of Names

Sep 1, 2009

I have a list of names in a single cell. They are all seperated by a comma, then a space. Example would be: John Smith, Steve Wilson, Wallace O Malley, etc. What formula could I use to pull out the names individually, starting from the farthest right?

View 2 Replies View Related

Reorder List Of Names With Capitalized Last Names

Feb 24, 2014

I'm trying to sort out a list of names from a website that publishes names in the following format:

DOE John
VAN GOGH Vincent
DA VINCI Leonardo
NADAL PARERA Rafael
JIMENEZ RODRIGUEZ Miguel Angel

What I'd like to do is get the names in the following format

John Doe
Vincent Van Gogh
Leonardo Da Vinci
Rafael Nadal Parera
Miguel Angel Jimenez Rodriguez

Basically all the last names - which are all capitalized - would be moved to the end of the text string. Of course any leading spaces should be removed and I guess using the Proper() function, all capitalized words could be capitalized in a standard way.

I found the following function, here: [URL] ...

but what it does is just take the capitalized words and separate them into a separate cell, which is not all of what I want.

View 1 Replies View Related

Replace Bad Names From A List Of Good Names

May 14, 2009

create a script that will replace the names in column A on sheet1 from a Master sheet in the same workbook?

The problem is that different users are entering data on sheet1 col A in different ways example someone may enter Johnc or John C Or John What I want is for something to run down col A on sheet1 and look for the like name on the master sheet if the name matches then do nothing but if the name is like another name on the master sheet then replace the name if they are almost alike.

View 11 Replies View Related

Randomly Select Four Names From List Of Names

Jan 22, 2014

I need to create a function that selects 4 names randomly from a list of 15 names and displays the 4 randomly selected names in the one cell. Also, you cannot repeat the same name in that cell, (i.e. bob cannot be selected twice in his group of four)

View 1 Replies View Related

Opening Excel File From List Of File Names Located In Single Sheet?

May 31, 2013

loop and range function to apply in the below code through which I can avoid writing code for all the rows.

I am trying to open excel files located in single folder from files name (along with the path) in single worksheet (Column B and Row 1 to 500).

I have created follwing code which opens the file and then runs a macro in it.

a Sub Test()
Dim strFName As String
strFName = Sheet1.Range("B2").Value

[Code].....

View 4 Replies View Related

Extract Data And File Names From File In Folder And Paste Into One Sheet?

Jan 13, 2013

The code below looks at file names in column A and then goes to a folder and opens and copies the data in range c2 -lastrow from each file and pastes the data into sheet2.

how I could add to the code so that it also inserts the file name in column c?

It would make it easier to track the data in column B.

VB:
Sub CopyFromFile()
Dim fPath As String
Dim lRow As Long

[Code].....

I have attached a sample workbook. The list of file name is in sheet 1. An example of the output is in sheet 2. The data in column A is dummy data generally spans 100's of rows not just 10 as in the example.

The purpose of the code is to be able to put a list of file names in column a in sheet 1 and extract data from those files in a folder. The data extraction works fine. The reson for adding the file names is so that I know what file the data came from.

View 1 Replies View Related

Look Up Names From A Table

Sep 20, 2009

I have attched my workbook. As you will see I would like to use this as a template.
Worksheet 1 (fixtures) has the layout for the first half of the season all the places are numbered. Worksheet 2 (teams) has the numbers but as yet I do not know the names of the teams. Is it possible that when I receive the names of the teams I can put them in the teams worksheet and Excel change the numbers on the fixtures list to the names of the teams. I have been doing this for a couple of years now and have done all the working out with pen and paper which can take a while.

View 2 Replies View Related

Copy File Names From A Directory Into An File

Mar 11, 2007

Is it possible w/ some vba code to copy the names of files, either from a search or a specific directory to an Excel Worksheet?

View 9 Replies View Related

Add Sheets With Names From Table

Nov 21, 2006

On sheet1 of my workbook I have a table containing the colums : Title, Name, Street+nr, postal code + city of some people, Telephone, email.

I would like to do the following (by clicking a button perhaps?)

- For all the records in my table on sheet1 I would like to insert a new sheet in my workbook named to their names (as in sheet1).

- The new sheet must be an existing sheet that I've already made the content (a kind of expence sheet)

- Somewhere on the top of the sheet I would like to have their names , titles en addresses (fetched from sheet1)

View 9 Replies View Related

Compare 1st X Letters Of Names To Other Names

Apr 2, 2008

Here's what I'm trying to do:

In a spreadsheet I have a series of names with associated data, for instance: ...

View 8 Replies View Related

Export File Names To Excel?

Nov 15, 2010

i would like to export the sub-directory file names to excel.

View 5 Replies View Related

Macro For Retrieving File Names

Jan 10, 2008

Here is what i have:

View 13 Replies View Related

List File Names From Folder

Apr 30, 2009

Need code to open a browser to select a folder and list the files in that folder in excel. I do not want to include sub directories.

View 4 Replies View Related

Query File Names In Folder

May 28, 2009

Is there a way to write somethign that will go to a particular folder on your computer and bring back the Names of all files in that folder?

I need to build a sheet that lists certain information from each file, which is already included in the file name - trying to avoid opening hundreds of files just to get data from one cell....for example:

All files are in the following folder: ....

View 11 Replies View Related

Macro To List File Names

Jul 29, 2009

I have a Macro which will list file names in column A, but it does not include the sub folders. Edit this macro to include sub folders as well.
I also want to display the file path in the column C for ech document displayed in the column A

HTML Sub ListFiles()
Dim objFSO As FileSystemObject
Dim objFol As Folder
Dim objFiles As Files
Dim objFile As File
Range("A2:A300").Clear
Set objFSO = New Scripting.FileSystemObject
Set objFol = objFSO.GetFolder("c: est")
Set objFiles = objFol.Files
Range("A2").Select
For Each objFile In objFiles
ActiveCell = objFile.Name
ActiveCell.Offset(1, 0).Select
DoEvents..................................

View 6 Replies View Related

Changing File Names Within Worksheets

Mar 25, 2009

my formula is as follows: =(VLOOKUP(A7,'[Monthly AR Analysis - Feb09.xls]AR - Current'!$A$1:$I$153,8,FALSE)). I will be using this for formula on about 15 different worksheets ... my issue is the file name changes every month and currently I have to go in and do a find and replace, I would like to have one main page where I could change the file name month and it will change all the rest.

ie: Monthly AR Analysis - Feb09.xls next month changes to Monthly AR Analysis - Mar09.xls .... So basically each month I want it to pick up the new file that has been saved with the new name. So what I would want is to be able to have a cell that I could change the Feb09 to Mar09 and then it would do the same to all the worksheets. I attempted to use CONCATENATE to change file name and it worked but will not work within the Vlookup.

View 4 Replies View Related

DLL File For VBA To Add Comments And Names In UTF-8 Codes?

Jan 29, 2012

get utf-8 string variable from userform.textbox?

when i try to get it, it shows me "?"..

is there any dll file for vba to add comments and names in UTF-8 codes?

View 1 Replies View Related

Extracting Data From File Names

Oct 4, 2013

We use a bunch of equipment at work that spits out a pdf file that is going to consist the results of a particular test.

Those files are named in a unique format. It looks a bit like this:-

EH_CM42_EB0C8105G00_CPS41D_F30C3105E00_2013_09_10_08_39_25.pdf

Out of this file name it consist of the serial number (F30C3105E00) of the equipment which is the first set off italics/bold and time(08_39) and date (2013_09_10) this test was performed.

So My question is there a way or a vbs macro or anything that will be extract these three different information and save them in three different cells on the spreadsheet. BTW Also I should mention that these files are going to be stored on a network drive

PS- The only way I have figured out to do this is to create a .bat file that runs the dir command and saves it to a text file.

View 5 Replies View Related







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