Insert Filename In A Cell Into Formula In Another Cell
Feb 3, 2014
I have tried =INDIRECT but that will not work.
I have a file name typed into cell C3. I want the formula in cell C4 (C5,C6,C7......) to include the name of the file typed into cell C3. The formula in cell C5 shows what the content should look like.
I want the users to be able to type the file name in Row 3 (C3,D3,E3.......) and have the formulas below them insert the file name and access the information from the files (yes the files can/will be open when we do this).
I need to to have at least twenty different formulas (rows) referencing specific cells in these files.
The formulas would look like this ='["File name from C3"]Audit Information'!$D$35.
I am hoping it may only be a syntax thing and someone here can "show me the way" I have tried &$C$3& with and without quotations with no luck.
Is it possible to insert the filename of another excel file based on the information in a cell within your current file?
I have a file named 12-07.xlsx, which has a sheet Data 12-07, and that sheet has information all through it. I want to populate a cell in a new file with inform from the 12-07.xlsx cell B3.
I know the Formula is ='[12-07.xlsx]Data 12-07'!$B$3
Is there a way that I can use a cell within the current file, say cell B1 (which has a value of 12-07 - which is the same as the file I want) to populate the filename? Something like
='["=B1".xlxs]Data 12-07'!$B$3 (whereas "=B1" would be 12-07)
I know that will not work as is, but is this concept doable?
I have a formula that looks like this : ='G:STOCKS[1001 Stock.xls]Matl%'!$I$32
There are hundreds of similar formulas, all referencing different cells in the same file. The “1001” bit refers to the date, and next month it will need to say “1002” to pick up the new month's file. I would like to hold the “1002” bit in a cell (say A1), so that at month end, I just change that one cell, and all the formulas will point to the new file, but I’m not sure how to work that into the formula.
I have a situation where I have to curve fit data, this can lead to different formulas being used with varying constants.
Is it possible to pickup a TEXT based formula and related constants from other cells, and then place this into another cell as a functioning formula. For Example
Cell A1 contains the formula as a text string whether it be y=a+bx+cx^2, or y=a+b/x, etc Cells A2:A6 contain the individual constants, a, b, c, etc
I would then want the VBA to read the text based formula and put it into an output cell as a functioning excel formulae.g
In cell B10: =a+b*A10+c*A10^2
I understand picking the constants up and putting the formula should not be too much of an issue, however trying to insert the variable form of the curve fit is the part that I am struggling with, and am unsure if possible.
I'm trying to insert the following formula into cell AA11. For some reason I keep getting a 1004 Runtime Error. I'm assuming there is some type of syntax error. I'm not really sure what I'm doing wrong.
I am trying to use the code below to enter formulas into two different cells.
The first code "ActiveCell.FormulaR1C1 = "code" is running ok.
But the second one "ActiveCell.FormulaR1C1 = "=IF(IFERROR(IF(AND(1*LEFT(D2,2)>=61,1*LEFT(D2,2)<=65),VLOOKUP(AA2,'abc'!A:C,3,FALSE),""),"")="",D2,D2&AA2)" is getting error.
Sub Combine() Dim wbk1 As Workbook, wbk2 As Workbook
How can a formula perform the equivalent of the keyboard alt enter in a cell? ie Make =A1&A2 where A1 and A2 are cells containing text become a1text a2text in the new cell.
I want to make changes to a cell's formula when a combobox option is clicked, I can make the changes using sheet1.cells(x,y) = "=WHATEVER(bla,bla,false)" The problem is that when I run the program and make a change in the combobox, false is written as 'false' in the cells formula, which drives it into not working and displays ####### as the cells result.
I am making a deposit sheet schedule, which has 10-15 batches of deposits that are entered one after the other, vertically. Each deposit entry can be allocated to many different expense accounts, so a deposit will be 500 with 400 to rent, 50 utitilies, etc. So I need multiple summation rows
So, I want to add a sum row beneath each set of deposits.
My problem is that each deposit is a different amount of rows long.
I was thinking of doing something like this:
'totalRow() is each row where the summation formulas will go totalRow(0) is not an actual sum row, but tells me where the first row of data lies.
I don't know how to reference the ranges I want to sum, because I don't know how to increment the reference to the column (i.e. changing A to B to F to G, etc.) with the Range().Formula construct.
I was thinking of using R1C1 somehow but I'm not as familiar and I don't think you can use r1c1 & Range() at the same time.
I am trying to display the Filename in a cell on my worksheet. However, when I enter the =Cell("filename"), it provides the full path (C:/Documents......Cost Summary.xls) How can I display only the filename without the path?
i'm sure this can be done but i cant get an angle on the method. i want to use VBA to put a formula into cell G3 based on the users entry in cell D3 so, for example the user enters M in cell D3 and the VBA code puts the formula "if D3="M",A3,0"into cell G3
i know i could use a formula in the cell but i want it to work with multiple entries so i figure VBA is the way to go.
I want to insert a new row that contains the formulas of a fixed row (1:1). The inserted row is changeable and is determined by whichever is the current active cell.
Eg:
Active cell is something random like E16
I want to add a new row but don't want a blank row - rather want a row that contains the properties of 1:1
1. search A1:AZ1 to find the cell that has the text "VBA Test" in the cell. There could be other text in the cell as well - this is not an exact match - but these two words are the common text.
I am using a For Next statement that doesn't return the results for all the rows. The statement is as follows:
For Row = 1 To 100 If ActiveCell.Value = "CHANGE" Then ActiveCell. Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=RIGHT(""0000""&RC[-1],20)" ActiveCell.Offset(1, 0).Range("A1").Select ElseIf ActiveCell.Value <> "CHANGE" Then ActiveCell.Offset(1, 0).Range("A1").Select Else: Range("A1").Select Exit For
End If Next
Range("A1").Select
I hope I did that according to the rules. It only returns the result in the first cell that does have a value of "CHANGE". It seem to be going through the entire range of cells, but I'm not getting any results.
I need to do two things in my Excel spreadsheet: 1) I want it to insert a blank row everytime a value in the 1st column changes. 2) In the blank lines, I want to do a COUNTA for each of columns G through N. If I can get the program to insert the blank rows...they will not be a set # of records apart...some will have 3 records & some might have 17 records. Is there a way to automatically cause #2 to happen instead of having to choose the function icon and then tell Excel the first and last cells in each range?
Hello I'm looking for some clarification about how to use "= SUM(RC:RC)". I don't understand what the RC:RC represents. I thought that this was the directions for where to "position" the formula, but I think I'm missing something.
I was able to include the correct cells that I need summarized, but the answer is not right; it's zero.
CELL("filename") gives you the name of the current workbook but if you switch to another workbook and then come back, the name has switched to the other workbook. You have to refresh to make the name switch back to that of the workbook you used the formula in.
What alternatives exist, short of writing some VBA, which is an option, to fetching the name of the workbook via a formula and having it remain the name of that workbook when you go away and come back again?
GOAL: Have an Excel file renamed automatically using the contents of a cell in the first sheet. DETAILS: The cell with the new file name is always D8. The cell is blank initially (although it doesn't have to be). OTHER INFO: OS= WIN 7 Excel= 2007 EXAMPLE: If I input "Mike T" into cell D8, the name of the file will be Mike T.xls
When the user goes to File, Save As, I would like the filename to be populated from the contents of cells B2 and B3, i.e. 11/18/09-23. Is this possible? Also in the same code, can I specify that I want the destination folder to be K:Sheets?
