Using Cell Value As File Name In A Formula?
Apr 20, 2013
[URL]
I'm trying to put together an accounting spreadsheet that is able to pull information from other files.
So I have a staff member, let's say "Rick", and I have created a file with all his details in it aptly named "Rick".
I also have a file called "Year Accounts" which pulls information from other files to basically give me a simple yearly overview.
I want to be able to pull my staff details into this one (tax file number, wages etc...)
To do this I want to be able to put his name "Rick" into A1 And A2 to go to that file and get the information from cell A5 in the "Personal Details" page Then in A3 to go to the "Wages" page and get cell B3
The following formula almost works, but where/how do I add the page reference as it only goes to the first page? =INDIRECT("'"&A1&"'!A5")
View 1 Replies
ADVERTISEMENT
Jul 29, 2009
I have the following (working) formula that pulls the largest value from an array in a closed workbook on a network share:
=LARGE('\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL SHAREDM806 ReportsFY 2008-2009Clarion countyJanuary 09[385-4009.xls]M-805'!$F$11:$G$41,1)
I want to be able to use a cell value within the open workbook to change the name of the referenced workbook (i.e. the 385-4009 portion of the formula) in the fromula
I have atempted to use the following:
=LARGE('\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL SHAREDM806 ReportsFY 2008-2009Clarion countyJanuary 09["'"p8"'.xls]M-805'!$F$11:$G$41,1)
but excel returns an error and highlights the following portion of the formula:
'\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL
View 2 Replies
View Related
Jan 16, 2008
Trying to reference cells in a closed workbook for the first time, and am having problems. The formula in my cell is: ='H:Trial BalanceTESTING[12-07-2007.csv]12-07-2007'!$P2
At first it wouldn't work, and I always had a #REF! in that cell, but when I opened the csv file the correct value showed in the cell that has the formula. Is it not possible to have it work without me having to open the csv file as well?
View 2 Replies
View Related
Feb 15, 2008
I have the following formula =HYPERLINK(("G:ProjectTest Project"),"Link". Now this works fine, However I have a cell "A5" containing "Test Project" And I would rather the link refernced the cell contents So the code would look something like this; =HYPERLINK(("G:Project"+A5+"),"Link"). or something to that effect, this obviously doesnt work hence why I am here.
View 3 Replies
View Related
Feb 26, 2008
explain or point me the right direction for the problem below: I need to find a way to vlookup a spreadsheet that has changing dates in the file name such as filename_xx.xx.xx. This is a piece to a macro I'm currently working on.
View 2 Replies
View Related
Jun 23, 2008
I am trying to get my cell to match the name of the file. I am aware of the following formula: =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-5)
This does return what I want, but members of my team often have several sheets open within one Excel window. The formula returns the most recently opened file within the window rather than the name of that particular file. This then distorts my results sheet as it ends up with the name of the most recent file opened rather than the person's name (which is what the file is called)
View 3 Replies
View Related
Jan 22, 2008
I have a problem that may be a little bit diff. from must of the Readonly post that I have found here. Most people would like to have all of there files opened as readonly files, but I don't in this case. I am copying a number of files that has VBA code in just about all of them. I am using a CD writer to copy the files to a CD using Windows Explorer and that is working just great, No problem copying the files.
My problems comes in when I copy the files from the CD to another computer, all of the files are Readonly files on the new PC. I have not saved them as readonly and don't want them to be readonly files. Does anyone have any ideas why this is happening what I need to check or change to get the files to another computer without being a readonly file.
View 2 Replies
View Related
Apr 9, 2008
I need to reference a cell to another cell in a different workbook (not opened), however the name of this workbook contains a variable in its name. Something like:
='C:[Week" & variable &".xls]R23'!$D$3
where variable is the number of the week. I have tried using a cell name instead of a variable: ='C:[Week=($B$3).xls]R23'!$D$3
and I get a dialog box to update values by selecting a file.
View 4 Replies
View Related
Mar 3, 2007
The master has rows corresponding to numbered files:
4300 | CellValue1 | CellValue2 | etc
4301 | CellValue1 | CellValue2 | etc
In attempting a simple formula (a couple VBA codes I tried did not work and were probably substantially more than I needed anyway), I made the following:
='CMain FolderSub Folder[concatenate(cell w/file number,".xls")]Sheet1'!A1
This is identical to the copy/paste link formula currently in use except that I am trying to have the actual file number/name generated off the master list instead of what is in the file itself.
Purpose: in case I was not clear on this above, I am trying to automatically populate the master worksheet with the data from the workbook. The current procedure is to copy a line of the data from the workbook, then paste as a link in to the master worksheet on the row corresponding to the file number.
View 6 Replies
View Related
Jun 12, 2006
I've got a couple of formulas in a workbook which lookup values in another workbook. When I reopen the initial workbook the formulas results come back as errors (i.e. #VALUE!) unless the workbook it is looking at is also open. This is not desirable, does anyway know how I can lookup values in another workbook which are not open? The formula which is causing me problems is as follows:
= SUMIF( 'H:Infozoom[2006-01_assy.xls]New Table1'!$F:$F,$A8,'H:Infozoom[2006-01_assy.xls]New Table1'!$AH:$AH)/60
View 2 Replies
View Related
Oct 22, 2009
I have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.
I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.
View 9 Replies
View Related
Dec 12, 2012
I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.
I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.
Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.
Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy
[code]....
View 4 Replies
View Related
Jul 10, 2014
I have a Workbook that already has a macro in it that will generate multiple sheets based on certain criteria.
For each of these newly generated sheets (numbered 1-6 in the attached example), I need to be able save each of them to a PDF file based on a unique name contained in a certain cell (in this case, each named is referenced in cell Q1 of each sheet). As such, I should end up with 6 pdfs based on the attached sample files.
The PDFs would ideally need to be landscaped and should be just 1 page per sheet.
In my attached sample workbook, you'll notice that I have a "PracticePrint" macro that doesn't quite get the job done. One other criteria involves not printing any sheet with the characters "1010" in it (my PracticePrint macro is setup to do that, but it fails to do much else).
View 1 Replies
View Related
Nov 3, 2009
I am trying to import some csv files so I can combine them, but am having probs with the filename and location.
Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With
With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................
View 5 Replies
View Related
Nov 20, 2008
Here I would like to get your valued guidance for the usage of "IF" formula. I am having two sheets one is new XL-1 and other is old XL-2. I want to have a item number from the old file corresponding to the same item descprition in the new file. I am attaching the snap shots of two files which is more explanatory than describing in words here.
View 2 Replies
View Related
Nov 28, 2003
I need help creating a formula that will get a specific cell from a specific file in a directory with multiple excel files, where the filename of the file to look in is stated in the cell of the current new excel file.
View 9 Replies
View Related
Mar 21, 2013
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies
View Related
Aug 18, 2014
Is it possible to insert the filename automatically in formula after the input from the user?
I want to ask the user to input a filename and afterwards, call that filename in an Index-Match
I have the below formula with the sample filename:
ActiveCell.FormulaR1C1 = _
"=INDEX('[filename1.xlsx]DATA'!C9,MATCH(RC[1],'[filename1.xlsx]DATA'!C32,0))"
Range("A2").Select
View 1 Replies
View Related
Dec 20, 2012
I bought a new computer with Windows 7. A file that is fine on my old unit does not work on the new one.
Column "H" should provide a list of birthdays comming up in the next two months, but something is not working.
View 4 Replies
View Related
May 1, 2014
I have a macro that will create 15 reports based on a filter in a Pivot Table and will save them to a folder based on the filter names. This work great, however in one of the reports I have a few vlookups as an example of one of them,
Code:
=VLOOKUP(Selector,Trends2!$B$6:$LV$22,AI50,FALSE)
. Now when the report is created from the macro, the vlookup is changed to,
Code:
=VLOOKUP(Selector,'Z:ISB DataISR RASC non transactional dataISR dataNational and Zone Reports[ISR National and Zone Template v18a.xlsm]Trends2'!$B$6:$LV$22,AI50,FALSE)
Is there any way to stop this from happening? Is it a general thing in Excel? Code below that creates the reports if needed
Code:
Sub Create_National_Report()
'Start of report creation process
Sheets("Pivot Tables (2)").Select
[Code]....
View 1 Replies
View Related
Jan 19, 2010
if this is possible, but is there anyway to reference a file location in a formula using another cell?
EG:
Cell A2 (Text) = C:Folder1Folder2Folder3Sheet v1.2.xls
Cell B2 = =A2'tab1'!$A$1
So, in cell B2 it will use the file location in cell A2.
I need this because the values will be updated in Sheet v1.2 and version controlled (so if it changes to 1.3 all you have to do is change cell A2).
View 9 Replies
View Related
Oct 14, 2006
We have a set of workbooks with a linked Vlookup formula. When we email the file to staff that do not have access to the linked file, the linked formula seems to change the directory. see below
Original Formula
=VLOOKUP($A$30,'G:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)
Formula after user opens email with the drive changed automatically to C
=VLOOKUP($A$30,'C:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)
Note that the user does not have access to the G drive and they are not updating the links when they open the file.
View 4 Replies
View Related
Oct 1, 2011
Version: Excel 2007 WinXP
I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.
Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:
Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)
I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.
Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's
Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.
View 2 Replies
View Related
Oct 10, 2011
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies
View Related
May 28, 2014
I attached herewith a file filled with Countif, sum, or, and. I look for a VBA to get lighter file. Because the file in fact will be filled up to 40,000 rows.
View 7 Replies
View Related
Mar 3, 2009
Need to solve the formula in the file. Is it possible to solve this in just one move? in that case.
View 2 Replies
View Related
Oct 26, 2009
We're using SUMIF and it won't work unless the external excel file is open.
This is the formula we're using:
View 7 Replies
View Related
Apr 26, 2012
I need building a formula to pull information from a database file. The purpose is to try to interpret the data so that I can analyze a time period along with it's content. I don't know how best to explain it. Basically I have a table and I want it to pull information based on 3 criteria: time, type, and total. I would like for the formula to identify a time frame, then identify a type, and then take the totals and add them together.
Time / Type / Total
11:31 /1 / 4
11:43 / 1 / 6
12:04 / 2 / 1
12:31 / 1 / 3
Time / Type / Total
11:30 - 12:00 / 1 / 10
12:00 - 12:30 / 1 / 3
The top is the database and the bottom is basically what I want to happen on the other table. Pretend the / and dividers for each column.
View 9 Replies
View Related
Apr 30, 2012
I have a file that summarizes data by country and by month but then another file with a yearly summary. I need to pull in the same cell references for each month but using each tab's name: 'Jan' tab, 'Feb' tab etc. The problem is every time I tell it to find/replace the tab name, I have to select the file from its location in a browsing box every time. I have also tried the indirect formula without much luck and because I also had to add all the month summary tabs to the original file to get the indirect formula to work. I would rather keep both files separate.
How do I copy over formulas in the Jan column and change the tab reference to the Feb tab for the Feb column without having to change things 200 times for each countries data?
View 2 Replies
View Related
Mar 8, 2013
In sheet1, I wrote my data for lookup porpose. The data named (Table). Then I saved it as Add-in (named TEST). That Add-in, already added to my excel Add-in
I open another excel file but How can I use lookup formula from that data (Table) in sheet1 in that Add-in (Test)?
View 1 Replies
View Related
