Refering From Another Workbook By Using The Concatenate Formula
Aug 2, 2006
I would like to reference information from another workbook. Instead of writing the Name of the other workbook I would like to reference through a Concatenate formula (e.g. CONCATENATE("Test"&G2&".xls")). I always get an error message. Is there any way I can reference from another workbook by using the Concatenate or any other formula?
View 5 Replies
ADVERTISEMENT
Apr 17, 2007
I'm trying to check if a workbook can be used in some processes later and whithout opening the other workbook. is it possible. i have done this
str_prod_PT = Application. GetOpenFilename
If str_prod_PT <> "FALSE" Then
If Workbooks(str_prod_PT). Sheets(1). Range("A1").Value <> "Ordem" Then
MsgBox "Ficheiro incompativel", vbCritical
Else
TextBox1.Value = str_prod_PT
End If
End If
View 3 Replies
View Related
May 24, 2007
I have to refer the content of a text box in a formula.
The Text box and the cell containing the formula will be in
the same worksheet.
Is this possible first of all? I searched the excel help in and out
and I could not find a way for this.
View 11 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
Dec 5, 2009
Currently I have a code that names a worksheet automatically with the date the first entry was keyed in. The problem is that I'd like to create a formula that would not be affected whatever the names of these sheets might be. There are 21 sheets, the last sheet would be the one containing the formula adding cell values from the preceeding 20 sheets. I've been scratching my head with this for a while now trying to come up with a workaround solution.
HTML Private Sub Worksheet_Change(ByVal Target As Range)
Dim dateTemp As Date
ActiveSheet.Names.Add Name:="timestamp", RefersTo:=Now()
dateTemp = Val(Mid(ActiveSheet.Names("timestamp"), 2))
'Sheet is given a default name until the first entry.
'The name is changed to the date the entry was made.
If ActiveSheet.Name = "DAY01" Then
ActiveSheet.Name = Format(dateTemp, "MMM dd.hh.ss")
End If
End Sub
View 5 Replies
View Related
Dec 9, 2009
I have a problem with bringing up the data I need to cells in one row.
In row 1 I have dates:
23-Nov 24-Nov 25-Nov 26-Nov etc.
In row 2 Quantities:
2000 1500 250 750 etc.
In row 3 I have dates again (I need to start working towards these qty's): 20-Nov 22-Nov 25-Nov 25/Nov. I would like in column A of 4 row to sum all the qty's (row 2) if the date in row 3 is the same or smaller than in row 1 column A. But in column B and forward I want cells to sum qty's if the date in row 3 is the same as the one in row 1. I have tried : =sumproduct((A3:D3<=A1),(A2:D2)) for cell A4
and: =sumproduct((A3:D3=B1),(A2:D2)) for cell B4 and consequently for others. It comes back as zero value even if I see matching dates/qty's. Does anyone know what shall I change in my formula to make it right?
View 3 Replies
View Related
Feb 20, 2007
Is there a preferred way of referring to the Workbook or worksheet which is hosting the macro.
For example say I have the following Macro:
Sub test()
Dim MyHostMacroWorksheet As Workbook
'''''''''''''''''''''''''''''''''''''''''''''''''
Set MyHostMacroWorksheet = Workbooks(1)
MsgBox (MyHostMacroWorksheet.Name)
'Works only if macro was inside first workbook opened
'''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''
Set MyHostMacroWorksheet = Workbooks("foobar.xls")
MsgBox (MyHostMacroWorksheet.Name)
'Works but have to modify every time I copy code from workbook to workbook
'''''''''''''''''''''''''''''''''''''''''''''''''
End Sub
I am looking for explicitly saying "The Workbook/Worksheet that is specified by VBAProject"
View 9 Replies
View Related
Sep 19, 2008
I have a string variable that I want to make available to all modules. The string variable is a listbox.value. In a Userform I have users select a file from a list of files. This particular selection should then be used in a module of the project. Unfortunately, a Public statement can't be used in a class module to declare a fixed-length string variable. Is there any other way to make this variable available to the module?
View 9 Replies
View Related
Mar 20, 2009
I want to Vlook a value from a cell in another workbook then before I get the formula result I would like it to concatenate the result with another cell.
I have attached an example
AlEXAMPLE.xls
View 2 Replies
View Related
Aug 24, 2014
I am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx
View 3 Replies
View Related
Jul 25, 2009
I'm trying to use a macro to write a formula within a column of data.
Here's the
View 3 Replies
View Related
Oct 14, 2008
I need to do a vlookup with the array starting on a row determined in another cell, so I've tried concatenating the number into a formula but this doesn't seem to work. Is there something else that I can do?
E2 is 134
=CONCATENATE("=VLOOKUP($B2,$C$", E2+1, ":$F$2571,3,FALSE)")
returns: =VLOOKUP($B2,!$C$135:$F$2571,3,FALSE) but not as a formula, as text and I need the formula...
View 3 Replies
View Related
Apr 19, 2014
I am using CONCATENATE formula to make summary of my data, but i m using long CONCATENATE formula. I want to use short formula instead of CONCATENATE
formula is here :
[Code]....
How to short this formula and accuracy is first ...
View 7 Replies
View Related
Nov 17, 2011
Is this possible because when i try to use the Substitute formula my Concatenate formula is doesn't work. This data in cell A1 is linked from another sheet
PHONE
WIRE
CANDY
INDIA
When I use the Concatenate formula only, it works fine but when i try to add the Substitute to this, it looks like this
PHONE WIRE CANDY INDIA
I want it to look like this, removing any blank lines within cell A1
PHONE
WIRE
CANDY
INDIA
Something wrong with the formula? how come it doesn't do both?
=SUBSTITUTE(CONCATENATE(A1),CHAR(10),"")
View 1 Replies
View Related
Oct 14, 2009
I am trying to merge cells A1:GR1 in to one cell by using the concatenate formula and the copying and pasting the values in to the same cell.
Is this possible? I've tried to use the formula below with no luck,
=Concatenate(A1:GR1)
View 9 Replies
View Related
Feb 24, 2010
I have a DDE code that returns a stock symbol value. The code is as follows, where @ES# is the symbol:
=NeoTicker|q!'@ES#,last'
I am trying to create a cell so when I put in a symbol (H10), it automatically gives me the quote. So I split up the code and then concatenated it as follows:
G10: '=NeoTicker|q!'
H10: '@ES#
View 9 Replies
View Related
Aug 7, 2006
I have been able to create concatenate formulas for numbes (% and $), but have been unable to create one that will display a date, I. E. for the number formula, I used, =(A8&" "&TEXT(B8,"$0")). What would be the formual so the second cell show the value as a date?
View 2 Replies
View Related
Aug 10, 2007
I am writing a macroI have three variables named "name", " book", and "count". In previous statements these variables have all been defined as String, String, Integer respectively. I am trying to uses all of these variables in the same formula and am encountering issues. The formula I wish to use it in is:
ActiveCell.FormulaR1C1 = "=COUNT('[" & book & "]" & name & "!'""B6:B" & count & ")"
Where if book = Book1 ; name = "Sheet1" ; and Count = 100 I would want the following result: "=COUNT('[Book1]Sheet1!B6:B100)"
View 3 Replies
View Related
May 20, 2014
I have a time in cell C10 in hh:mm format, in cell D10 I want to add the text "DFL" in front of the time, remove the ":" and add "HRS." at the end of it. For example "DFL0715HRS." I have been able to get the following formula to work:
=CONCATENATE("DFL",(TEXT(C10,"hhmm")),"HRS.")
However sometimes it may not be a time, it could also be "Rest Day" in cell C10, if it says "Rest Day" I need it to add the text "DFL" in front as before and then change "Rest Day" to "RDFLEXI." For example "DFLRDFLEXI." I have been able to get the following formula to work but only if it is "Rest Day", if it is a time such as 07:15 it doesn't work.
It shows "DFL07157R0.": =CONCATENATE("DFL",IF(C10="Rest Day","RDFLEXI.",(TEXT(C10,"hhmm"&"HRS."))))
I think it is trying to continue doing something with the text function
View 3 Replies
View Related
Sep 21, 2009
I'm looking for a formula that utilizes an "if/then" condition. I'm attaching a sample workbook of what I'm dealing with. I need it to end up so that column "E" holds: if a equals c, then e = b, "and", d, c Does that make sense? I manually entered my desired end result into column E in the sample workbook.
View 3 Replies
View Related
Oct 14, 2009
I am trying to merge cells A1:GR1 in to one cell by using the concatenate formula and the copying and pasting the values in to the same cell.
View 4 Replies
View Related
Jan 24, 2006
I receive daily reports from the people that work for me. The files are
saved in a known location and a known naming structure (initials - date.xls)
In a summary report I CONCATENATE their initials and the date along with a
path but cannot get the INDIRECT command to recognize the text line.
View 10 Replies
View Related
Jul 21, 2006
Is there a way to make the CONCATENATE formula put all the text on different lines? It keeps combining some on the same lines and I want them all on separate - like a list.
View 13 Replies
View Related
Nov 28, 2008
Looking for a little help with the following formula =CONCATENATE("August 07 -",TODAY()," Evening Instructor") After entering this formula today is displayed as a serial number. I'd like it to display as current month/current day/current year (11/28/08)
View 2 Replies
View Related
Feb 6, 2009
Is there a way to nest IF & And statements. I'm looking to concatenate a number of cells and seperate them with a space and/or comma but only if they contain data so need something along the lines of
View 3 Replies
View Related
Jan 17, 2007
way of joinng the contents of cells to create a formula?
path month subdept
x:path oct [dept.xls]sheet1!a1
x:path dec [dept.xls]sheet1!a1
is there any way of joining these to create a cell reference such as x:path oct[dept.xls]sheet1!a1
i've tried the concatenate and "& &" method to no avail.
View 9 Replies
View Related
Mar 13, 2009
Is there a faster way to combine all the P.O# with a formula automatically, no matter how many P.O# there are? Its unpredictable on how many invoices will get pay per wire.
Log # Amount P.O. Numbers 1-a 45,678 1234 1-b 89,173 5678 1-c 66,526 9123 2 22,113 2345 3-a 66,654 6789 3-b 88,650 2468
1 201,377 1234/5678/9123 2 22,113 2345 3 155,304 6789/2468
View 10 Replies
View Related
Oct 19, 2007
I want to "calculate" a worksheet name by concatenating 2 cells. Then based on this concatenated word perform a " lookup". Basically I have a form for people to fill in and want to extract their answers automatically.
View 4 Replies
View Related
Oct 6, 2007
I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.
ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C
I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.
ColA ColB__ColC
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C
View 5 Replies
View Related
Sep 8, 2012
I am attempting to use concatenate to combine two cells of data, but to only display the information if both cells contain information.
Currently using the following simple formula:
=concatenate(a1,a2)
However, I only want it to work if both a1 and a2 cells contain data. If only a1 contains information, but a2 is blank, I want it to calculate a blank cell.
View 3 Replies
View Related