VBA / Dynamic Workbook Name Reference
Feb 6, 2014
I am using:
Code:
Dim Aname As String
Aname = ActiveWorkbook.ActiveSheet.Range("D2").Value & "Pricing"
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=Aname & ".xls"
to create a new workbook. I need to copy 2 columns from the original workbook/worksheet to the one being created though. I can get the the original easy enough, but then getting back to the newly created book is proving difficult.
(the 2 columns being copied are filtered to what is needed and I have used the below to copy it, yes I know that is 3 rows but I couldnt get it to copy just E and G at the same time to the lastrow without including F)
Code:
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("E2:G" & LastRow)
.Select.copy
End With
View 7 Replies
ADVERTISEMENT
Feb 24, 2014
I currently have a file that is linked to an external workbook that is referencing a certain cell from a certain tab. The below is what Column A & B look like right now. What I'm wanting to know is is there a way to have the formula in Column B automatically reference the name in Column A (which is the tab name in the external workbook) instead of having to manually change the formula when the value in Column A changes?
In other words, I want everything in the external reference formula to remain the same except have the person's name change dynamically...
Mary
='X:Anderson CorporationEmployees[Work Hours]Mary'!$F$42
Joe
='X:Anderson CorporationEmployees[Work Hours]Joe'!$F$42
Frank
='X:Anderson CorporationEmployees[Work Hours]Frank'!$F$42
Michelle
='X:Anderson CorporationEmployees[Work Hours]Michelle'!$F$42
Sam
='X:Anderson CorporationEmployees[Work Hours]Sam'!$F$42
View 1 Replies
View Related
Apr 17, 2014
I'm trying to build a dynamic formula where a cell reference shifts one column over when a certain percentage changes. It's hard to explain, but the attached workbook should make it clear what I'm trying to accomplish.
SampleWorkbook.xlsx
View 2 Replies
View Related
Mar 3, 2009
I need a dynamic formula which if hard-coded would be something like =(tabname!A7).
But instead of A, I want a calculated value such as ($C$2*12+1). My columns are numbered, so I want to say "the 26th column along", "the 37th along" etc, as C changes. I tried the index formula but couldn't get it to work, assuming it is even the right one.
View 5 Replies
View Related
Oct 19, 2009
Q: How can I point excel to the begining value cell if I know the ending value cell and the nubmer of cells to move to get there?
More details:
I have an investing sheet that I need to calculate the CAGR (compound annual growth rate). The formula is (ending value/begining value)^(1/number of years)-1
The number of years can vary but the ending value will aways be in the same cell. I am using a count to see how many years have been entered.
View 3 Replies
View Related
Apr 22, 2008
I have a sumproduct function that is aimed at a big block of data. Is there a way that I can write the function to where it will always find the last row of the data set and adjust accordingly?
For example if right now the range is A1:G2000 and next month I add 20 rows of data I want the sumproduct ranges to automatically adjust to A1:G2020.
View 10 Replies
View Related
Mar 26, 2014
The values in Cells D1, D2 and D3 are user inputs.
A formula in B16 should sum values in database (ie sum of B6 to B11)
Example :
A
B
C
D
E
1
Enter the column no
B
2
Enter the start row no
6
[code]......
Result
149
View 1 Replies
View Related
Feb 26, 2007
The original worksheet will have a sheet of data, but I want the user to be able to insert a row of data into this sheet and have the original values still referenced in the macro.
I do not believe the range function will work, because the relative cell positions are what are changing. Also, the offset command would not work, because the positions are not absolute to the active cell. Correct me if I am wrong...
Snippet of
If Shape = "Freeform 30" Then 'AR
Receiver = "kim.randy@companyX.com"
End If
I want it to point to something like =Receivers!C13
How would I do this without hardcoding the data above?
View 9 Replies
View Related
Jul 21, 2006
I have a question about dynamic cell references that I canīt find any info about anywhere else. This is what I want to do: I need to create a sentence that changes depending on user choices. Example:
Cell A1 = Sheet!AX
Displayed text Dear Mr. "name"
Where the X is a number that will change depending on other things. I would love to do something like: =Sheet!A(E1). Where the value in E1 is for example 4. The result would then be =Sheet!A4 and if I changed the value in E1 the reference =Sheet!A(E1) would also change. I donīt know the syntax for doing this or if it is even possible.
View 4 Replies
View Related
Jun 23, 2012
I've got the following code:
VB:
Application.DisplayAlerts = False
Worksheets("WildCard").Delete
Application.DisplayAlerts = True
I'd like the "WildCard" to pull from a value in a cell whereby I can run a button macro that, when pushed, deletes the tab/worksheet named "WildCard". For instance, if I wanted to delete "Sheet3" I would type "Sheet3" into, say, cell C3 and then press the button to run the macro and "Sheet3" would be deleted. How to I refer the macro to cell C3? I currently have a macro that inserts/names a sheet based upon a cell reference and macro button. I'd like one that can delete in the same way.
View 7 Replies
View Related
Dec 18, 2013
I would like to be able to write a formula which references text within a cell and the formula automatically changes when the text changes. Example is attached.
Current formula is
=IF(E2="A",F2-A2,IF(E2="B",F2-B2,IF(E2="C",F2-C2,F2-D2)))
Is it possible to write the formula so if I change "A" in A1 to "Z" the formula will change as well? Or the formula will at least always reference the text within the cell, not necessarily the text that exists at the formula's conception?
Example.xlsx
View 3 Replies
View Related
Dec 22, 2011
I am looking to start writing a code that will a row # from a dynamic cell within the workbook and use that row # to select the row and delete it.
Example:
In Cell G5=8
I would like the VBA code to see 8 as row 8 and delete row 8.
Row 8 is determined by looking up a name and finding which row it is in and returning the row number.
View 1 Replies
View Related
Mar 13, 2007
I'm trying to create a user defined function to drop into dblookup functions that will always reference the first cell in an associated column and the first cell in the row of an associated column.
I came up with the following, however the problem is that the udf always returns the values for the active cell's, row and columns.
Function SRCref2()
Application.Volatile
myfield1 = ActiveCell.Column
Dim1 = Columns(myfield1).Range("a1").Value
SRCref2 = Dim1
End Function
Function SRCref3()
Application.Volatile
myfield2 = ActiveCell.Row
Dim2 = Rows(myfield2).Range("a1").Value
SRCref3 = Dim2
End Function
I would nest these in a formula such as "=srcsum(srcref2(), srcref3())".
Ideally I could then use this same funtion anywhere instead of having to use and worry about the accuracy of "=srcsum($a$2,$b$1)"."=srcsum($a$3,$b$1)"."=srcsum($a$4,$b$5)".etc.
It seems like there should be a current cell method or something but I can't find it and I'm not smart enough it seems to use vb to designate some kind of floating intersect as a base for a row and column reference.
View 8 Replies
View Related
Apr 1, 2014
I am trying to create a formula to count a range relative to a dynamic reference (at least I think that's you would phrase it).
I thought I was on to a winner with this:
Formula:
[Code] .....
But it doesn't seem to accept the : as a legal operator.
I suspect the answer is to use SUMPRODUCT somehow.
View 6 Replies
View Related
Jan 22, 2010
Is it possible to copy a formula and have the worksheet name increase incrementally instead of the cell reference? Example: In column A, I have the following -
=TDI_SR_F_1!$G$1
=TDI_SR_F_1!$G$2
=TDI_SR_F_1!$G$3
I want to copy this across to column B, but instead of just copying that exact foruma across I am wanting a formula that will update the worksheet reference to:
=TDI_SR_F_2!$G$1
=TDI_SR_F_2!$G$2
=TDI_SR_F_2!$G$3
TDI_SR_F_2 is the worksheet after TDI_SR_F_1. NB - I cant use find and replace function as it will change the '1' which is part of the cell reference ($G$1) to a 2 as well. I know I can use TDI_SR_F_3 as a search string, but the process is going to be extremely tedious, i.e. there are literally over 100 rows I need to do the find and replace on.
View 2 Replies
View Related
Oct 11, 2011
I need making a dynamic cell reference in an indirect formula, which looks at a different workbook.
The other workbook is called: ESF.xlsx
The sheet name in the other workbook is defined in Cell B2
The first cell i need to pull across is B115.
In cell B8, This is what i have so far:
=INDIRECT("[ESF.xlsx]"&B2&"!"&"B115")
How do i make it so that i can copy this formula quickly, but so only the row and column reference change?
Like in C8 i would want it to look at cell C115, In C9, id want it to look at C116 etc.
View 2 Replies
View Related
Nov 14, 2006
In the attached spreadsheet I am trying to always reference the sum column from Qty in Det Est.
This is because the number of roads changes for each project.
I created a dynamic range name surfacing with the following formula:
=offset(qty!$a$1,0,0,counta(qty!$A$A), counta(qty!$1$1)).
My question is what formula do I use to reference the sum column for each row on the Det Est Sheet.
View 4 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
May 18, 2007
i have 2 worksheets. one with portfolio stock data, and a second that i would like to use for dynamic charting. the chart worksheet has a drop down menu that chages a refernce cell to a number corresponding to the collumn in the portfolio worksheet for the chosen ticker
i would like to make a dynamic chart that updates the data ranges based on something like this: ....
View 9 Replies
View Related
Dec 1, 2007
I have a list of codes from which I'm trying to extract all unique entries.
For example, col A has 001, 001, 002, 003, 003, 003, 004, ...
I'm trying to create a report template with formulas that reference col A and return a single unique entry for each code so that the new column has only 001, 002, 003, 004.
Essentially, the codes reference customers. A customer can have multiple purchases -- but I'm trying to create a report in which I can utilize SumIf formulas to Sum the purchases for each customer and represent those purchases on a single line per customer code.
The catch is that the purchases change monthly (i.e., next month the purchases may be 001, 002, 002, 002, 004) so my report template needs to have the flexibility to adapt without me rewriting the SumIf formulas and return SumIf results for only customers 001, 002, 004. Whereas in the prior month, the report returned SumIf results for customers 001, 002, 003, 004.
View 9 Replies
View Related
Aug 13, 2008
I have a problem creating a circular reference with dynamic ranges. I have searched already but can't find the specifc problem. I have a spreadsheet where I use vba code to insert extra columns. Within the worksheet there are sum calculations at the end of each row. When I add a new column within the table I would like the sum calculation to expand to account for this extra column.
Now, I thought that dynamic ranges would be the way to go but the only way I can see to do it creates a circular reference as the CountA function trys to count the cell with the sum function in it.
View 2 Replies
View Related
Jun 6, 2014
I want see if it's possible to take the name of the source workbook and put it in a cell in my destination workbook and then use that as the reference in formulas. The source document title includes a date that changes yearly, or in some instances changes entirely, and I want to keep the formulas in the destination workbook intact. I don't want to have to go to every instance where I have used the source workbook and change the title. Instead I would like to copy the source workbook title, paste it into the destination in the same cell and have that propogate the needed changes into the destination workbook formulas.
View 2 Replies
View Related
Apr 26, 2009
I am trying to create a pivot table on a new sheet names as 4x4
I am getting following error:
Error : 1004
Reference Not Valid
I am not sure where is the problem. I have generated this code by using macro recorder and just changed the source and destination of the pivot generation code.
View 9 Replies
View Related
Aug 6, 2012
I have a workbook "Weekly Field Report-Master Copy" and another workbook "Weekly Field Report $$$-Master" The "Weekly Field Report $$$-Master" workbook has all the formulas that pull data from the "Weekly Field Reports-Master Copy" workbook and populates a financial report of that week's field unit production.
When I save the "Weekly Field Report-Master Copy" workbook to a new name "Weekly Field Report Job #1" and then save the "Weekly Field Report $$$-Master Copy" workbook to a new name "Weekly Field Report $$$-Job #1" is there a way to automatically change the formulas in the "Weekly Field Report $$$-Job #1" workbook to now pull data from the "Weekly Field Report Job #1" workbook instead of the "Weekly Field Report-Master Copy" workbook???
Currently I am manually changing the formulas in the new workbook "Weekly Field Report $$$-Job #1" to reference the data in the new workbook "Weekly Field Report Job #1". Otherwise the formulas pull the data from the "Weekly Field Report-Master Copy".
View 4 Replies
View Related
Nov 11, 2012
I am running Excel 2003 on a Win7 system.
Here is my situation:
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
=--(AND($AX8>$AA$4,$Y8>0))
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.
View 1 Replies
View Related
Mar 8, 2014
I have sheet1 along with three columns as below
Result column will show the result after sum ** column A & B
[Code] ......
This is simple that i will add this formula =A2+B2 in Result column cell C2.
But i want to perform this addition action on Sheet2 and return the result in column C2 ** Sheet1.
What formula should be populate in Result column ** Sheet1 that could catch addition formula from Sheet2 dynamically and display the result ** column A & B in Sheet1 .
And what type ** formula should be use in Sheet2 to add two columns dynamically.
The same concept that we are using in any functions like as below:
Sub Function Plus (value1 As Double, value2 As Double)
Plus = (value1 + value)
End Sub
View 3 Replies
View Related
Aug 16, 2006
I have 1 workbook which summerizes 3 other workbooks. My question is, is there a way that if I hide a column(s) in 1 workbook (by either using Hide or Outline), the corresponding column(s) will also automatically be hidden in the Summery workbook?
Or, is there a way that I can make the Summery Workbook only display / Link to cells that is been displayed in the source workbooks? I hope to do this dynamically, so if something is hidden by Outline or Hide in one of the workbook, the affected cells in the Summary workbook will also go in hiding.
I am hoping to do this by some magical Excel Function, if not then by VBA (I'm not that good a VBA programmer), if not even that, then maybe some free Excel Tool someone knows?
View 6 Replies
View Related
Aug 23, 2006
I am facing a problem in using a dynamic range(name) defined in a different workbook, when that workbook in not open. I have defined a dynamic range, 'complete' , in Main Assumptions.xls as shown below
= offset( sheet1!$A$16, 0,0, counta(sheet1!$A:$A)-6, 5)
I am trying to use 'complete' in another workbook, RCL.xls, as shown below.
=VLOOKUP(C11, 'C:Chein[Main Assumptions.xls]Sheet1'!complete,5)
This works well as long as Main Assumputions.xls and RCL.xls are open. However, When Main Assumptions.xls is closed and I try to open RCL.xls, I get a warning saying, Excel can't find 'complete' in Main Assumptions.xls. There are two possible reasons: 1. the name you specified may not be defined. 2. The name you specified is defined as as something other than a rectangular cell reference. Does that mean that I can't use dynamic ranges defined, in other workbooks?
View 3 Replies
View Related
Jan 12, 2007
I made a VLOOKUP formula referring to another workbook. The workbook name was 1099.XLS and there was only one worksheet in it, also called 1099.
So the reference I used was [1099.XLS]1099! but I have since realised that Excel changes that as I enter it, to '1099'!
What I would like to know is, is this a one-off action by Exel, and why? Have I used the correct reference in general -
[workbookname.XLS]worksheetname!
and did Excel's actions come about because there was only one worksheet, or because the worksheetname was the same as the workbookname, or what?
View 5 Replies
View Related
Aug 11, 2009
is to have a folder where I drop multiple workbooks from different stores. There can be 1 or more workbooks at any given time in that folder. Each workbook is a different size as far as the information inside. Next I need to transfer certain columns to the Master Workbook and also have the store location added to the Master workbook (File Name). I have highlighted the store information that needs to be merged to the master, Store 1, Store 2. For reference I have attached a copy of the Main Folder which is on my desktop C:Documents and SettingsDesktopInventory.
View 5 Replies
View Related