Is it possible to use the HYPERLINK function inside of a validation list?
Presently I have a dynamically named range on another worksheet that includes a hyperlinked term. When I attempt to create a validation list using this named range the term appears in the list without the hyperlink.
I have 3 sheets:
-Sheet 1
--is where all the sales people work from entering the details of the leads generated and the turnover for each deal
-Sheet2
--Is a single list where sales people can populate with the names of all their leads A4 – A 339, at the moment it is listed as “client 1”; “client 2’ ....the idea would be for the sales person to type over “client 1” with the real name of the client “Jim Smith”
-Sheet 3
--Is a contact tracker for each client, (date and time called, size of project etc...)
In Sheet 1, column C is the “company/lead name” column, I have made this column a data validation list that pulls through all the clients names from Sheet 2 (I have named the range in sheet 2 “ClientList”).
My request:
I would like the clients’ names in Sheet 1’s drop down list to be able to be linked to the specific clients profile in Sheet 3.
--Eg. Jim Smith links to Sheet 3 Cell A54
--Eg. Sarah Mulley links to Sheet 3 Cell E237
I want to select a Hyperlinked file.These files are named using Data in ColumnA,B & C. The realtionship between filename and data in columns is; A-B-C.ext. After selecting data A from the dropdown list,i should be able to select corresponding data B and then again another dropdown list for corresponding data C.This should show the desired already hyperlinked file. I want to select the hyperlinked file name by simply selecting all the three data independently.
View 3 Replies View RelatedIs it possible to set up a cell to either return a vlookup result or allow a specific data validation list? For example, if precedent cell is blank, then I want to perform a vlookup and return result, but if not, then only allow a choice from an unrelated list.
View 5 Replies View RelatedI need to compare 2 cell with 1 specification reference.But the function can't give return value as per required.Both 2 input cells using Data Validation List.
Please refer attachment for some examples : matching.xlsx‎
I have the following formula in a Data Validation List:
=OFFSET(B8,MATCH(J2,
(CHOOSE(B7,D1Array,D2Array,D3array,D4Array,D5Array,D6Array)),0),1,COUNTIF(CHOOSE(B7,D1Array,D2Array, D3array,D4Array,D5Array,D6Array),J2),1)
In B8 it is the formula =VLOOKUP(B7,LookupTable,2,0). and the lookup array is this 1$H$112$J$113$L$114$N$115$P$116$R$11
So the value returned in B8 will be a the start reference for the OFFSET function. This works but it retuns blank values and not the values in the arrays.
I am using a data-validation-list with =INDIRECT(B7) as the source. It works as expected except for after the file has been closed and reopened. When it is reopened, the source is changed to =INDIRECT(#REF!).
I have tried =INDIRECT('Worksheetname'!B7) but same result.
I have a dynamic validation list (see attached) which will not accept the indirect function as a formula in the listbox.
Using the named range on its own works fine, but using the indirect generates an error.
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies View RelatedI am having a slight problem copying hyperlinks. I have written some code that sorts data by date and then creates a simple diary. It originally placed the name of the event in the new diary sheet. What I would like to do is instead of copying the name into this new sheet copy a hyperlink that I have created to the company's website for that event.
I can create the hyperlink using hyperlink(B1,A1) where A1 is the company name and B1 is the web address but since the new sheet will not have the underlying data I need to actually copy the values and format of the hyperlink rather than the formula.
What I have attached is spreadsheet I've built to quickly generate an army list for a tabletop game. Anyone can click on the Force Organization Slot (FOS) and choose the type of unit and from there, select the specific unit name attached to that FOS. Each individual unit has a list of options/upgrades currently attached to the sheet from cells AD through AX and the points cost listed next to each upgrade.
For example, when selecting the Rhino unit from the Dedicated_Transport FOS, there should be 4 drop down lists of selectable upgrades (Storm Bolter, Hunter-Killer Missile, Dozer Blade, and Extra Armor). Currently, I have only named the ranges for the Rhino and Razorback units in order to get this working properly without having to go through the entire list of units and change names/create ranges/ect.
What I'm aiming to do is to create an individual drop down list that is directly related to the Unit Name cell in order to populate a complete and selectable upgrade list for each unit. I have played around with some functions that work in the spreadsheet (see cells X10:X12) but do not work properly with Data Validation.
Oddly enough, when I enter this formula under Data Validation:
=IF($A$3<>$AD$1:$AD$182,VLOOKUP(A3,Options,2,FALSE),"")
I do not get an error, yet the drop down list does not allow me to select anything as I thought it would.
I have a from that work great in PC but if opened in a MAC, some of the functions will not work.
For example, There is one cell with Data validation that based on the value selected in another data validation cell, it should only display all possible options pertaining to value chosen. These are the values: a user select a customer based on customer name. The Data validation will the put the customer number in cell AF3. In another call with Data Validation I have this formula:
"=OFFSET(SHIPTO,MATCH(AF3,SHIPTOColumn,0)-1,2,COUNTIF(SHIPTOColumn,AF3),1)"
SHIPTO is the Name range for the tabs called SHIPTO with "Shipto!$A$1"
SHIPTOColumn is the Name Range for "SHIPTO!$A:$A"
Column A is where the customer numbers are.
This work fine in a PC but not in the MAC using excel 2011
I'm trying to calc a Simple Moving Average of financial Fund data (row=fund type, column=date) on a very large matrix for a particular fund and date range. I locate the Fund of interest and retrieve the Start and End dates of interest and retrieve their addresses using the ADDRESS function. Since ADDRESS returns a text string, HOW DO I incorporate it's results into the following array function (which ignors blanks and 0 cells): {=AVERAGE(IF($L$10:$V$10>0,$L$10:$V$10,FALSE))}?
View 5 Replies View RelatedIs it possible to make a function inside a textstring?
E.g. - The area of the building is [=A1] m² and contains [=A2] apartments.
=StacServer|Windham! '40720'
This is the format of an external call that I am trying to run. I need to change the number (40720) based on the day of the month (i.e. if its the 5th, then use 40725.
I am attemting to use a function to do this and passing in the device name (Windham) and location (40720). Unfortunately, I have been unable to get a String to send back as a formula. My attempt was as follows :
= Location ("Windham", 40720, "D7")
Function Location(name, num, cell)
Dim psDay As Integer
Dim psLocal As String
psDay = Day(Now)
Location = (Range(cell).Formula = "=StacServer|" & name & "!'" & (num + psDay) & "'")
End Function
I have the following formula that has ceased to work in excel. I don't think there are any errors in it so I'm unsure as to why the link no longer works. Neither of the files I'm using has moved location on the server.
=IF(H7>0,(I7*H7)+((I7*H7/100)*VLOOKUP(B7,'T:FILEFILEFILE'!$B:$E,4,FALSE))+0.25,0)
I'm not sure if this makes much of a difference but people opening the file use various versions of Microsoft Office.
how i get this to work?
B4 = BMEF3
function:
=CONCATENAR("=TZMS|";B4;"!VAR")
and this its what should do:
=TZMS|BMEF3!VAR
how im supposed to do that function work?
inside cell a30 i have a date function which displays: Mon 07/22/2013 3:30:14 AM. i realize that excel actually uses 41477.146 Can i search for something kin the display, i.e. "Mon"?
View 2 Replies View RelatedAnother interesting dilemma to solve. Using this formula:
View 2 Replies View RelatedSolution for all: {=SUMPRODUCT(SUMIF(Sheet1!$A$2:$A$16,$F$34:$F$40,INDEX(Sheet1!$B$2:$K$16,0,MATCH(T$10,Sheet1!$B$1:$K $1,0))),U34:U40)}
Say I have an Excel workbook with two sheets: Employee Hours and Employee Wages. On the Hours sheet I have 31 columns (Jan1 - Jan31) with inputs for the number of hours worked by each employee by day. On the Wages sheet I have listed the same 31 columns with their wages each day (I do this to factor in wage increases that occur fairly often).
My goal is to use an INDEX/MATCH function to calculate the total daily compensation for each day on the Wages sheet by multiplying hours*wages for all employees that day. Here's the code I'm using:
i have written a code in VBA to interpolate the value of Y0 corresponding to X0 using a set of (X,Y) points. (I have written this in module1) . This function works when i call it through a Sub or another Function in VBA. but it does not work when I try to use it as a function in my excel worksheets (when I type : =interpolate(A1:A10,B1:B10,30) .... 30 is an arbitrary value). in this case I get #value! error
The function is known in within the worksheets because when I start typing its name, the Auto Name Complete feature of excel, finds this function.
I am new to VAB for excel. Please give me a hint to see my mistakes of if something is missing inside my code.
PHP
Public Function Interpolate(ByRef X() As Double, ByRef Y() As Double, ByRef X0 As Double) As Double Dim I As Integer, Slope As Double, NData As Integer NData = UBound(X) For I = 1 To UBound(X) - 1 If (X(I) = X0) Then Interpolate = Y(I) Exit Function ElseIf (X0 < ListMax(X(I), X(I + 1)) And X0 > ListMin(X(I), X(I + 1))) Then Slope = (Y(I) - Y(I + 1)) / (X(I) - X(I + 1)) Interpolate = Y(I + 1) + Slope * (X0 - X(I + 1)) Exit Function End If Next I End FunctionPublic Function ListMax(ParamArray ListItems() As Variant) Dim I As Integer ListMax = ListItems(0) For I = 0 To UBound(ListItems()) If ListItems(I) > ListMax Then ListMax = ListItems(I) Next IEnd FunctionPublic Function ListMin(ParamArray ListItems() As Variant) Dim I As Integer ListMin = ListItems(0) For I = 0 To UBound(ListItems()) If ListItems(I) < ListMin Then ListMin = ListItems(I) Next IEnd Function
I want to use the function VLOOKUP at my VBA program inside a FOR Loop
View 3 Replies View RelatedI'm starting a project where Excel will be used as the main UI for defining a table of data. I'm expecting to define a "template" - xlt i guess - that users can open and save as an xls, without over-writing the template. Users will populate the spreadsheet with a lot of help from user defined functions. I'm just getting started and would like to populate a particular cell with the current date, but only the first time the sheet is opened. I tried checking whether a cell was empty before assigning a value to it, of course this meant recursion!
Also: It might be nice to use the Today() function inside a UDF, but TODAY isn't a member of Application.WorksheetFunction - is there no way to reference TODAY() from within a UDF?
The first three formulas give valid results but the fourth example throws in an error after hitting Enter. As you can see I put in the HOUR function. As far as my knowledge goes the HOUR function generates an integer.
So why does C4 not work? Formulas go in column C
A
B
C
1
22
20
1
=COUNTIF(A1,">"&B1)
[Code] ........
I have a spreadsheet that uses a function defined in VBA code that is housed in an add-in which is stored in the default location (C:Documents And Settings{user_name}Application DataMicrosoftAdd-Ins). To share this spreadsheet, I first install the add-in on the other person's computer. Then, when the other person opens the spreadsheet, the function doesn't work because the function call on the worksheet contains the whole path of the add-in (and the path contains my {user_name} rather than the other person's). So, the immediate solution is to just delete the path of the add-in from the function call (so that only the name of the function remains, plus the arguments of course). But this is quite inconvenient given the number of times the function appears in the worksheet, and it's not practical as we continue to share files like this.
My question is: is there a way to make sure that the 'C:Documents...' never gets added to the function call in the first place? I mean, why doesn't Excel just try to run the function locally before it adds that path that refers to a separate computer? I do realize that there are other methods of sharing VBA code. For example, I know that I could include the code in the specific spreadsheet that I'm trying to share, and this would work. But, that's not the answer I'm looking for (for one, the function refers to a very large amount of data stored in the add-in, and there are other reasons that make the aforementioned method impractical). I'm wondering if I'm missing something, and if there is a way to stop Excel from tacking on the path string to the function call, so that the function would just work on any computer as long as they have the add-in installed?
I'm trying to create a simple VBA function in Excel so that I can sort and calculate results based on various conditions. I can get everything to work except changing the color of the cell where the output is placed. Here is what I have so far:
Function nameOfMyFunction(arguments)
If conditions Then
nameOfMyFunction = "No GRP"
ActiveCell.Font.Color = RGB(255,0,0)
End If
I'm sure that its something simple, but I've spent long enough on my own trying to figure it out and nothing seems to work.
Working on a user form... I have a combo box, with a list of the items available.... then I have a text box that inputs the quantity. Is there a way that I can put a list underneath in such a way that every time I select a item, put the quantity in the text box, it will list down in that list?
In other words... If I have 5 products for the same order I would pick one, qty and click add, then another one, qty, and add... so on. That way I would be able to see all of the items that I have picked.
I am getting a #REF error when using an INDIRECT function within a MATCH function to check against a dynamic named range. Basically, I am trying to get the row reference so that I can go back and extract other data from the row (in a table contained in another sheet) into the current worksheet.
I attach an example file for reference. The issue arises when a Dynamic Named Range is used. In the example file, if a value from a static range is chosen, the match with indirect function works, but it fails with the dynamic range.
Dynamic Ranges INDIRECT v2.xlsb
The following code worked for me until,I put inside a list box.
View 4 Replies View RelatedIs it possible to do a seach in a lot of excel files (>500) to find the excel files wich have an embedded objects inside. If so how can I do this with VBA ?
View 3 Replies View Related