Using Cell Value As Workbook Name In Formula?
Jun 5, 2013
I have a range of cells, A1-N1, that contains the names of each of my excel worksheets in this workbook. (ie A1=Brittany, B1=Corey). Each table in this summary sheet is the totals for that person for the month. (ie Total of the scores from Brittany's worksheet for the month). Because we have frequent additions to our workforce, I need to change the names / reorder alphabetically every month. I am looking to put together a formula that will read the name from A1, use it as a sheetname! reference in a formula that will say --- Pull the value for E11 from the sheetname from A1. Example -- If A1=Brittany, then pull the value from E11 on the Brittany! sheet.I have been working through this all day. I am still relatively new to Excel and completely self taught through books. I can't seem to find a reference for this. In my mind it should be possible so that if I change the names on that original A1-N1 list that the tables will all dynamically change to those names and pull the values from the correct corresponding sheet to that name.
View 2 Replies
ADVERTISEMENT
Jun 13, 2013
When I try to run the macro, an error comes up saying application-defined or object-defined error. The following code is the line in which I receive the error.
VB : tool.Worksheets("ALL").Cells(row1, "J").Formula = "='[TRB Database.xlsm]TRB Database'!$I$" & A
View 3 Replies
View Related
Mar 19, 2014
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))
View 1 Replies
View Related
Oct 24, 2006
If I have a two files called test1.xls and test2.xls for example. Is it possible to link test1.xls to test2.xls by putting the filename in a cell?
i.e. In test2 put the filename in say A1 and in the link put the path and instead of naming the file you want to link to put in A1 instead.
I know it doesn't work if you do it that way I was just wondering if there was a way to do it.
View 5 Replies
View Related
Feb 9, 2007
"Smith B" is text in C1.
My current formula in C3 is a simple reference to a cell in another workbook:
=+'Z:Time Records2007[Smith B.xls]Daily Time'!$C1352
I would the formula to refer to C1 to get the "Smith B" part.
This would allow me to copy the formula to the right and reference different text as it looks at D1, E1, F1, etc.
To say it another way. I want to know if there's a way to make a formula that would result in looking at the file Smith B.xls by inserting a refernce to C1 in it?
=+'Z:Time Records2007["C1".xls]Daily Time'!$C1352
View 5 Replies
View Related
May 24, 2007
Say cell C5 contains the name of a project (workbook). eg
C5 = "[Project1]"
In my current workbook, I want to find the value in cell B8 of Sheet1 of the project shown in C5. If I wrote this directly, it would be "='[Project]Sheet1'!B8"
But the name of the project is a variable shown in cell C5 (as explained above). So I need a formula that will find the value in cell B8 of sheet1 of the project shown in C5.
View 7 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
Feb 4, 2014
Actually I do want to copy cell N589 from all files excel (sheet1) in c:database2014 to my workbook click run into the row C.
is there any of you can solve it?
View 1 Replies
View Related
Feb 20, 2008
i 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)
View 3 Replies
View Related
Jan 27, 2012
Anyone know of a way to detect if a cell is blank in a closed workbook?
Here's what I'm trying to do:
=IF(ISBLANK(closed workbook cell reference),NA(),closed workbook cell reference)
also tried:
=IF(closed workbook cell reference="",NA(),closed workbook cell reference)
Neither one worked. I'm using Excel 2007.
View 8 Replies
View Related
Jul 17, 2014
I am working with multiple workbooks with several tabs in each one. I need the forumula to update the "sheet name" from the source workbook even if the destination workbook is closed.
this is a portion of the forumula I am working with:
=SUM(IF(AND([L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4>=182,[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet26!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet27!$I$4
View 9 Replies
View Related
Jul 26, 2009
I have a formula in the Name Define part of excel in workbook that refers to another workbook. =IF(COUNTIF([DaysWorking.xls]Sunday!$A:$A,'2009'!I1)>0,1,"")
The DaysWorking.xls workbook is a data workbook, users may change the data and after they are done, they may exit the program but not save it. The DaysWorking.xls workbook should always revert back to the original form. I've put a read only property on it, but the problem lies when the user saves the file under Save/As function. Excel then updates the link to whatever the user saves the file as. Is there a way to make sure that the formula never get's changed?
View 2 Replies
View Related
Mar 27, 2009
I am creating a formula - that will look up a value within a seperate workbook. I want the workbook reference to change based on a cell which contains the date.
So workbook name is "Report 120309.xls"
Cell: A1 contains the date "120309"
Current formula ='[Report 120309.xls]Sheet 1'!$F$50. Can I replace the data within the formula with the date in the cell.
View 2 Replies
View Related
Mar 11, 2014
I have 2 workbooks. 1 is where data is entered called wksPB, the other is like a database workbook. The wksPB has data to be feed from column B to F and there is a dropdown.
what im looking to do is, if the combobox value is selected as Decline then it should display a messagebox that reference value in cell B is denied. if combobox value is Agreed then the macro should search the database workbook for the text entered in column b of wksPB and then copy data of C:E of wksPB to H:J of database sheet where that text is found and also the approver name in wksPB C24 to the approver cell of where that text was found. I've reached till finding the text but what I get my head twiting on is how to copy the text from wksPB column B:F to database sheet column H:K. Im attaching the sample workbooks and the code where ive reached till.
[Code] .....
Attached Files
Replacement Records - 2014.xlsx
forum file.xlsm
View 1 Replies
View Related
Mar 14, 2013
On one workbook I have "column A" that lists serial #'s, one serial number per cell. What I need is to be able to take all the non blank data from that column and copy it to another workbook with all those serial #'s in 1 cell numerically sorted and separated by commas.
View 1 Replies
View Related
Jun 20, 2008
I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...
'Dim officen As Integer
'Dim thiswb As Workbook
officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"
Sheets("officen").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select.............................
View 8 Replies
View Related
Aug 4, 2009
I need to create an automated process to copy data from book1 and book2 and paste it in another workbook called book3. I want vba code to do this task whenever I open my book3
Book1
Location: G:FUTOMS-phases - eTD and TDReference Infoook1.xls
Data to be copied: Entire Rows 6 to 12
Paste: Book3> Sheet1
Book2
location: H:OptionsInfoook2.xls
Data to be copied:: Entire Rows 1 to 5 & rows 10 to 15
Paste: Book3>Sheet2
View 14 Replies
View Related
Feb 15, 2013
Here is the scenario: I have workbook called Leads_2013-0215 it has about 10K records I have workbook called LawFirms it has about 4K records.
If the value in Column I in Leads_2012-0215 is present in column G in LawFirms, I want to add the value Law Firm to column AV in Leads_2012-0215.
View 2 Replies
View Related
Sep 28, 2012
I have two workbooks open. I need to "Save As" and close workbook 2 with a file name from a cell in Workbook 1. The macro is running from workbook 1.
I'm guessing a change in the last line. I don't know what Dim means either.
Dim FName As String
Dim FPath As String
FPath = "G:"
FName = Sheets("sheet 1").Range("A1").Text
ThisWorkbook.SaveAs Filename:=FPath & "" & FName
View 1 Replies
View Related
Apr 25, 2013
I have a workbook that is used to calculate values from various data etc. On completion of calculating the values, eg. £20,000, I want to put the values into specific cells of another workbook template. I can of course do it manually by selecting the cell of the template workbook and do an = then reference the the cell in the calculating workbook.
What I want to happen when all the calculations are complete is that:
1. A new workbook, based on the template, is opened and saved with an appropriate title
2 The values from the calculating workbook are automatically entered into the new (saved) workbook at specific cells
View 1 Replies
View Related
Nov 24, 2007
I want to open an excel file whose name is a value in a spreadsheet, and then pull a value from the spreadsheet I queried into my existing workbook. Here is the syntax i have so far:
to note: I'm calling the workbook in which the macro is located testproject.xls, and I'm using [integer].xls as my list of files that the macro will query. Ultimately, this code will go in a loop.
Set currentCell = Worksheets("Sheet1").Range("A1")
varCellvalue = currentCell.value
Workbooks.Open "path_of_file" & varCellvalue & ".xls"
Windows("testproject.xls").Activate
currentCell.Offset.(0,1).Select
ActiveCell.FormulaR1C1 = "=[3.xls]Sheet1!R1C1"
The problem is, I want [3.xls] to carry the sale value as varCellvalue with .xls appended, and not be kept static at 3. I tried inserting & varCellvalue & ".xls" into the brackets, but with no luck. My only difficulty is getting [3.xls] to vary along with varCellvalue.
View 6 Replies
View Related
Nov 11, 2008
I am trying to set up a template for my MD to use to calculate budgets and have so far encountered a number of problems most of which i have overcome thanks to help from this forum.
What i am trying to do is have a 'Selection sheet' which contains a number of drop down boxes from which the user can choose several different options.
Based on what they have choosen on this sheet a second sheet 'initial budget' is populated.
What i am trying to do is a formula similar to the following. but i can't seem to get it to work.
View 15 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
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
Jun 20, 2013
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]...
View 9 Replies
View Related
Nov 20, 2013
Coming from this formula in vba :
VB:
Dim sh As Worksheet
Set sh = Worksheets("Data helper")
Set Rng = sh.Range("C2:C" & sh.Range("C" & sh.Rows.Count).End(xlUp).row)
I want to translate this to a workbook dependent formula, but I'm not sure what I'm doing wrong
VB:
Dim book1 As Workbook
Dim sh As Worksheet
Dim srchRange As range '(added this line in edit : still not working)
'Set some variables:
[Code] ....
View 9 Replies
View Related
Nov 11, 2009
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.
View 3 Replies
View Related
Mar 20, 2012
I'm having an issue with a formula that references another workbook, and I'm not sure what the culprit is.
The formula is this: =IF(ISERROR(E4/VLOOKUP(D4,'C:Documents and SettingsusernameMy DocumentsRoom Capacity Master List.xlsx'!Table2[#All],2)),"",E4/VLOOKUP(D4,'C:Documents and SettingsusernameMy DocumentsRoom Capacity Master List.xlsx'!Table2[#All],2))
It works just fine on the initial pass. When I reopen the book and it prompts if I'd like to update the data, and I select yes, everything goes away. Cells E4 and D4 are both part of a PivotTable, not sure if that is important or not. This is happening on Excel 2010 running on XP.
View 2 Replies
View Related
Feb 23, 2014
I need a formula to look up the value in another workbook. I have a number of workbooks that have a list o competitor's names and their scores The competitor names are not in the same order in each workbook. I have created a master workbook to extract the score from each workbook. I need the formula to match the name in another workbook and then pick up the score cells to the right.
View 4 Replies
View Related
Oct 22, 2008
I am trying to copy text from one workbook to another with a formula, I have tried formatting the cells as text and general but no luck. I tried Concatenate and got the formula displayed but not the result.
View 9 Replies
View Related
