I have to many refrence links I need a macro that replace the filename. How to replace workbook refrence filename on a formula using cell value,
example:
from [13.xlm]2014.Goals'!C8 to ["cell-value".xlm]2014.Goals'!C8
In the following formula, I would like to replace STARK with the value in cell D33. I've tried a couple of different things using &s but no luck yet. I think I'm just missing something really simple.
View 10 Replies View RelatedI have created a workbook that generates new worksheets with unique identifiers as tab names. I.e. On a "Create New Sheet" worksheet, input three pieces of data, click "Create New Sheet" and this data is applied to a template sheet, macro continues running to copy the template and rename is with a unique SKU.
My problem is as follows: I have yet to figure out how to generate an Index page that is updated as each new sheet is added with the following:
a) Cell containing the SKU and a hyperlink (or some other link) to that particular tab, and
b) Beside this cell, pull the contents of certain cells from that SKU's tab (such as description, but also the contents of calculated fields on that SKU's tab like current quantity and current price).
I would like the Index to be live so that if data is changed on the SKU's tab, it flows through to the Index......
I'm working on a spreadsheet that will access two weeks worth of data at a time. Is it possible to have the filename reference contain a variable or a link to another cell in it?
For example: ...
I believe this is the last piece of the puzzle of a project I'm working on. Here are the components I have to work with:
Component 1:
VB:
Dim file As Range
Set file = Range("rgFileToBeChecked")
'this sets "file" as the full path
[Code]...
I am having trouble inserting a cell reference into a formula. I have a spreadseet that uses data from another workbook. The workbook name changes monthly, so i'd like to be able to place the workbook name in a cell and have the formulas in the spreadsheet use that cell to get the name of the workbook to get the data from. So:
if, for eg, the formula in my spreadsheet is ='[test data file.xlsx]Sheet1'!$B$4+'[test data file.xlsx]Sheet1'!$B$6
I would like to be able to enter test dta file.xlsx into cell A2 of my spreadsheet and replace a reference to A2 into the formulas. I've tried several ways, but can't seem to get it to work.
I have a formula that calculates data from the workbook that the formula is in. I need it to do the same calculations only pull the data from another workbook. Here is a sample formula:
= SUM(X1504 + SUMPRODUCT((H1:H394=73)*(U1:U394="NVLIV"),S1:S394))
Would I do something to the nature of:
=SUM(C:/sample.xls[Sheet1$x1504] + SUMPROD...etc...?
Basically how do you reference another workbook that is closed?
I am trying to use a cell reference to have excel automatically Save a worksheet using a cell reference as the workbook name. When I step through the code and use the immediate window I get the correct name for the file (fname in this case) but as soon as Excel tries to save the file, I get an error.
Here is the code I am using:
fname = Sheet3.Range("I1").Value
ChDir "S:Op CostsBudget 2013Data"
MsgBox "The actice file will be saved as " & fname
ActiveWorkbook.SaveAs filename:= _
"S:Op CostsBudget 2013Data" & fname & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
So finally got my sumrpod working using this formula
=SUMPRODUCT(1*('Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$C8:$C10000=A5))
Is there a way i can link 'Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[South Pac Load Tracker - MAR14.xlsm] to a cell so that when the next months tracker is created and the filename changes i simply change the filename in the cell which im using to reference the filepath/filename.
e.g A1 = South Pac Load Tracker - MAR14.xlsm
Formula would read =SUMPRODUCT(1*('Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[' & a1 & ;]SPECIALS TEAM NZ'!$C8:$C10000=A5))
I'm trying to do: In Book2.xls, Sheet1, Column A, I have a list of tab names in another workbook (2003 Com October.xls). In column B of Sheet1, I want to utilize what's in column A to pull data from the correct tab in 2003 Com October.xls. Here's what the result should resemble:
******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2B3B4B5B6B7=
ABCD1Onyx*IDName**2acgattikhickert**3acgattikhickert**4acgattikhickert**5acgattikhickert**6agomesnbizman**7athomasmweaver**Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have the following codes in which I am to refer a file and folder to do some further processes.
myfilename = "C:Documents and settingsacsMy DocumentsEntrymyfile1.xls"
folderPath = "C:Documents and settingsacsMy DocumentsEntry"
I want to enter these two paths in a sheet in my excel report file (For example, I am running the report from Report1.xls, in which there is a sheet name "Filepaths". In this,
in B5, I would like to enter the Filepath (B5 named as "FILEPATH")
in B6, I would like to enter the Folderpath (B6 named as "FOLDERPATH")
So that, if I am copying the folder to another area, I no need to change the code every time in the VBA editor. I can do changes in these cells and it will be taken as the path to run the code further.
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.
I want to copy a formula from Workbook A to Workbook B and have the formula configured with Workbook B's spreadsheet cells, not Workbook A's.
View 2 Replies View Relatedi want to return a value from a cell in another workbook where part of the the workbook name is determined by a date set on the current open sheet.
so on my open sheet i have a cell where the user can change the date and i want the below formula to then use the date to look at the relevant file.
any ideas would be appreciated. know how to do this in VBA but no clue with formulas.
what i am trying to do is concatenate part of a file name and a cell value which contans a date
=VLOOKUP(A4,[accountsummary17022008.xls]Sheet1!$A:$B,2,FALSE)
I am having trouble making any formula references between 2 different workbooks.
For example, I being typing out my VLOOKUP function on 1 sheet, as soon as I get the table argument, I switch over to the other workbook in which the table is, and I no longer have the VLOOPKUP function active.
I am having this problem for any formula in which I am trying to reference this workbook.
I have ensured they are both saved as Excel workbook documents. I am using Excel 2007
I'm trying to perform a copy & paste operation by hand which to me should be easy in theory. I have a worksheet whose formulas have been corrupted. I have a backup of this sheet- I'd like to copy the backup sheet into the workbook. The problem is that the formulas, which reference other worksheets within the workbook, are copied over as links to the backup workbook instead of the new workbook they are being copied too. This seems simple, but how do I copy the worksheet and the references without having to go through all of the references by hand to only apply to the local book.
View 4 Replies View RelatedReplace filename with variable in "Activecell.FormulaR1C1=" expression
I have recorded a macro which contains several lines of code like this one:
I open two spreadsheets only. In one of them I would like the filename of the other spreadsheet shown in cell A1.
The reason why is the filename is a 6 number date and I'd like to confirm it showing in full format.
I am trying to ensure that m work book keeps the same name even when I email it to others.
I have been told on this forum that it is possible for a workbook to check its own name once opened?
I have two different names that this file can be called 1st is the one that is in use all the time and the 2nd is a archive version where the name changes each month.
The file name I wish to keep the same for the no archived version is Toolbox and a predetermined number I have a workings sheet called workings and in a cell i have a formula =TEXT("Toolbox Application " & Cover!E10,) this will give me the file name that i need to check for example it could be Toolbox Application 00089 or Toolbox Application 00967.
I also have a cmd that archives the workbook and saves it as Toolbox and the Month it was for Toolbox for Jan for example
I also need this to open if it is an archive
if neither of the names match i need the workbook to close and a msgbox to tell user to rename the workbook correctly.
I am building a macro and am just not figuring out how to do this. I am trying to create a new workbook, then give it a name. Simple right? Well, I have the format for the file name to be "mm-dd-yy Transmittal #.xlsx" but I can't figure out how to make the # part of that equation start as 1 but if the file already exists change it to 2, if that exists, change it to 3, and so on. Here's what I have so far:
Code:
Sub Transmittal()
Dim NewBook As Workbook
Dim MyPath As String
[Code]....
I don't think I'm allowed to do "x + 1" so that is probably where my problem is coming from.
I have a workbook that gets saved many times with many different filenames.
All I want to do is have cell A1 display as text the name of the file.
eg similar to the date function, there should be a = filename function .
Im trying to create a macro that when i click the button a new workbook is created and saved as the value from C5 cell.
View 9 Replies View RelatedIs there a way to find the full name and path of the workbook the macro is running in?
View 4 Replies View RelatedI have a VBA form that the user enters information into and once they click submit on the form the code opens a new workbook and then enters the information from the form into that workbook. What I need is for that code to then save the workbook with a filename from one of the fields entered into the form but I'm not too sure how to do this.
View 3 Replies View RelatedI'm trying to record a macro that calls up the saveas dialogue and uses a filename from a cell (it's the w/c date). I can start recording the macro, open the dialogue box and copy the contents of the cell but i can't get it to paste as the filname.
What I'm trying to achieve is to allow users of a muli-sheet workbook to be able to click on a 'save' button on any sheet and have the workbook save with a filename which includes the current week commencing date. This way all the weeks changes will be in just one file and from the following monday a new file will be started the the old one left alone as an archive.
Am working on a procedure to open all files in a folder and copy some data from the files into another workbook (master). I have this piece of the code. However, I also need to get the filename (from which data was copied) and paste this into an adjacent cell in the master (so I know which file each data point came from).
View 8 Replies View Relatedi want to replace things with reference of cell for example:-
Find value in B2
replace with value in C2
in whole sheet
Is there a more efficient way that I can swap out an ending fixed reference value in thousands of vlookup formulas across many pages within the same workbook? For example, let's say one of my vlookup formulas is: =vlookup(A2,'Apples!'$A$2:$W$47,7,false)
and I want to change the range to 'Apples!'$A$2:$W$200, across several variations of the same formula, across several pages within the same workbook. Can this be done easily without tackling each sheet separately using copy, paste special, formula?
Where it is bolded I am trying to replace that with a cell reference such as A1 where I can input a website in that cell rather than edit the VBA code everytime
Here is the code:
Sub URL_Get_Query()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://quote.money.cnn.com/quote/quote?symbols=msft", _
[Code]....
I am trying to use the formula below to get the filename of the spreadsheet, it seems to work fine but when you open a second sheet the first sheet will pick up the name of the second. =MID(CELL("filename"), FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1). These spreadsheets will then be uploaded to another application and having 2 with the same name could cause issues. Obviously the answer is to only open one at a time, but this is always subject to human error!
View 3 Replies View Related