I have a Sheet named Main, Juz and another called wordforword.
Currently on Main sheet I have a vlookup that displays the results from wordforword. Instead of showing the result, I would like the user to be directed to the wordforword sheet result, with the click of a hyperlink.
I do prefer formulas over macro if possible.
I have "Sheet1" with thousands lines of data like following
ID Customer value date etc,......
2434 ABC 500 2012 data
2654 DEF 600 2013 data
and another sheet "sheet2" to hilight attention to a risk or issue like following
ID description status to do by etc,....
2434 missing items follow-up and fulfill Eng. A
I need to add a hyperlink to the ID number in BOLD to link me to the line matching in sheet1.
I'm having a problem getting a hyperlink to work when adding it to a vlookup. The vlookup is looking at a page number and returning the named drawing from an Auto Desk Inventor File. I'm trying to get the returned drawing name to become a hyperlink to open the drawing. I would rather have this added to the formula so if a page number ever changes the hyperlink will change to the correct drawing also. Here is what I have so far but it is just returning an error of " cannot open the specified file". =HYPERLINK(IF(E5="","",(VLOOKUP(E5,'Inventor Import'!$A$2:$E$200,5,FALSE))))
View 4 Replies View RelatedIn cell A1, I have contents extracted from a vlookup formula. eg 64021
I would like to add a hyperlink to this value, so that the user can click on it to get additional information by accessing an outside document.
I have dropdown lists across the top of my sheet that contain possible links (in text from defining multiple lists) from another sheet, and I want the cell below the dropdown list to return the active hyperlink from what the user has selected right above it. I have a another sheet (kind of a master data sheet) that has the text version of the links in column A and then the active working link (what I want) in column B and other info in the rest of the columns. I tried =hyperlink(vlookup(~~..etc)) and it returns the link but when clicked, gives me an error.
View 9 Replies View RelatedI am using vlookup to return data that i have hyperlinked in another sheet. But when it returns the data, the hyperlink has gone.
VLOOKUP HYPERLINK RETURN PROBLEM.xlsx
I have developed a spread sheet that when I click on a dropdown list I can picka certain item and it will show several colums of information. I want to include a hyperlink that is stored in the vlookup range in one of those colums. It will show the text in the link when i use the vlookup to see data however i cant access the link from this cell.
View 7 Replies View RelatedI have Data as give below in Sheet 1
srno
model name
total scope
drilling
lagging
skid
corrugation
1
HP 50BB/10.54
1023
24
22
12
200
[Code] .......
In sheet 2 i want to create a hyperlink such as "HP". If I click on HP it should extract all Model Names which start from HP and give me all corresponding results. Similarly for CM or SM
Note: HP / SM / CM are my Standard Names.
Result e.g.
Sheet 2 cell A1 contains HP
If i click on Hp it should return this table.
SR NO
model name
total scope
drilling
lagging
skid
corrugation
1
HP 50BB/10.54
1023
24
22
12
200
[Code] .......
This should be my result if i click on HP.
I am having when trying to preserve a hyperlink when using VLOOKUP. I have two worksheets within the same workbook. Worksheet 1 contains a lot of information for internal use(many of the cells in all of the columns contain hyperlinks to web addresses), and Worksheet 2 should be a version identical to this, showing only the selected columns suitable for external use. This is to avoid using two different 'work trackers'; so, when info in Worksheet 1 is updated, Worksheet 2 should automatically be updated and reflect this.
I am currently using the following code:
Function GetHyperLink(r As Range) As String
If r.Hyperlinks.Count Then
GetHyperLink = r.Hyperlinks(1).Address
End If
[Code].....
I am performing a vlookup in a worksheet and want to retrieve a hyperlink displayed on another worksheet in the same workbook.
The Vlookup finds the item I am looking for, but the hyperlink comes in as text. Do I need to perform the vlookup differently when trying to retrieve a hyperlink?
I simply want to perform a vlookup which can hyperlink to an adjacent cell.
View 9 Replies View RelatedI have a macro which returns the results for
=IF(ISNA(VLOOKUP("EMPLOYEE",Z:AA,2,0)),0,VLOOKUP("EMPLOYEE",Z:AA,2,0))
As of today, the entry EMPLOYEE is sometimes written as it was EMPLOYEE, and sometimes as EMP. I have to add these two together. How do I change the code of the macro?
Before it was:
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(""[EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"
I tried "=IF(ISNA(VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"+
"IF(ISNA(VLOOKUP(""EMP"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMP"",C[-16]:C[-14],2,0))"
as I would in Excel, but of course it doesn't work.
I have this function trying to added 4 VLOOKUP results together, but it only works for 2 rows and all others show as #NA.
=VLOOKUP(A8,MA!$A$8:$H$10,3,FALSE)+VLOOKUP(A8,PQ!$A$8:$H$10,3,FALSE)+VLOOKUP(A8,ON!$A$8:$H$19,3,FALS E)+VLOOKUP(A8,TW!$A$8:$H$22,3,FALSE)
I wan to create a table using 2 column of data.Below are the data
Type Name
A Danon
B Chris
C Hilton
A Jin
A King
B Ling
C Seng
D Aaron
B Halim
I wan to convert these data to a table which have four dimension. A, B, C, D. With Vlookup function, i can do it. But, i find difficulty when the result is overlap, when in the type A, got Danon, Jin, and King. My result only can lookup is King. Other cannot display.
I have a vlookup worksheet like described in this thread: [URL] .... This works perfect. I would like to be able to jump to the vlookup result using a hyperlink.
In the thread worksheet the formula in C2 is like
=VLOOKUP(A2,INDIRECT("'"&INDEX($G$2:$G$9,MATCH(TRUE,COUNTIF(INDIRECT("'"&$G$2:$G$9&"'!C2:C100"),A2)>0;0))&"'!C2:D100"),2,0)
In cell D2 I placed the formula (font: webdings )
=IF(C2="","",HYPERLINK("[Workbook1.xls]"&("Sheet2!"&(ADDRESS(MATCH(C2,Sheet2!$D:$D,),4))),"i"))
(Due to the Dutch Excel I use I replaced ";" to ",")
The link works because I hardcoded "Sheet2" into the formula where the result can be found. But it would be much nicer if I didn't had to put the sheetname into this formula.
Is it possible to use range G2:G8 like in the first formula to achieve this? I guess you have to use INDEX and/or INDIRECT but I barely understand the first formula ...
Worksheet1 has data.
Worksheet2 uses VLOOKUP back to worksheet1 for data to populate cells on worksheet2.
The VLOOKUP command below returns a value of 126 to cell C6. I want to use the number result of the VLOOKUP in C6 (126) as part of a calculation for another cell. In thiscase, cell D6 is keyed in. I want to get the result of =(N(D6)-N(C6)) and put it in cell F6. To further complicate things, if C6 is blank, I want F6 to be blank.
VLOOKUP formula that returns a value of 126 from worksheet1 and puts it in cell C6 on worksheet2. It also returns a blank if there is a blank on worksheet1.
=IF(ISNA(VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,0))+(VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,0)=""),"",VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,FALSE))
Add together D3+G3+J3+M3+Q3+U3 and put the result into W3? I think the reason it wont work is because there will not always be a value in D3/G3 and J3/M3
New KS4 grades Markbook.xlsx
I have a large spreadsheet of data, with lab results by date. Sometimes, there are multiple data points for a single date.
I need to produce monthly reports that list all the results for specific tests in a given month.
So, for example:
Date SG Property Tank
1/5/12 1.015 567324 2044
3/15/12 1.002 568210 2103
3/18/12 1.025 568056 2044
3/18/12 1.036 565200 2102
4/1/12 1.019 566713 2103
I would like to make a report for March that looks like this:
Date SG Tank
3/15/12 1.002 2103
3/18/12 1.025 2044
3/18/12 1.036 2102
And the next month, make a report for April, then May... etc.
I tried to think of a way to do this using vlookup or index/match, but couldn't figure it out.I also tried using filters and then automating some kind of copy/paste, but there is an extra line between the heading and the data, so that the date column is filtered as text instead of date. The source data is not my spreadsheet, so that would be difficult to change.
I would like to do a lookup function, in which multiple results are returned.
i.e.
VLookup Result 1
Result 2
Result 3
Etc.
I know how to do it if there is only one answer but say I have a unique identifiers in which I want all the results in column B:B displayed one after another, say locations have numerous products sold/manufactured at it.
I had someone look up a price on a website, for a list of part numbers, and copy the link to the website page into my spreadsheet.
Now I want to transfer the price and link to my main spreadsheet.
I know how to use VLookup to get the info to my main spreadsheet but when I transfer the link using VLookup it does not come as a hyperlink that I can click on and be sent to the webpage. It is just text.
How do I convert the links to a hyperlink that I can click on and be sent to the webpage?
I have 1200 records.
I am looking for Vlookup function, which returns multiple values without duplicates.
Please find the attachment : Vlookup Unique.xlsx
I want to set up a formula which automatically does vlookup or same sort of function on numbers given out by a link through a formula.
As a example i have attached the sheet.
I am making a company wide estimate sheet and having trouble with the drop down box. I have a drop down to pick the branch, but when I name the source it only says branch, even though I named the data on the branch sheet. I would like to be able to pick the branch initials and have it populate the phone and fax number under the main heading but am getting a error there.
Also for the labor is it possible to have it insert the correct labor into the formulas at the labor lines according to which branch is picked from the drop down.
Hi, I'm sure this will be an easy one for most of you. I need to extract data from an array for each time I have recorded a specific vehicle registration. I have used VLOOKUP but this only gives me one result. I need excel to display every enrty for the registration I am looking for. I have attached a sample file which should make more sence.
View 7 Replies View Relatedi put in a vlookup formula and it doesn't display any results, it just diplays the formula with the = sign in the cell. it does that with any formula i enter. i tried hitting it and everything.
View 4 Replies View RelatedI have a problem with VLOOKUP function and I am looking for a workaround.
SheetA I have a column of business names and another column of the category.
SheetB has a list of credit card transactions and a column of categories - The results obtained from incorrect results from SheetA...
I have a cell that I must remove the first 2 characters "RO" for each value in a column on a sheet called RAW DATA and put into a cell on a sheet called ROSS DATA. Some of the values in that cell have 3 digits after the RO and some have 5 digits. To do that I used
=REPLACE('RAW DATA'!A3,1,2,"")
Then I need to use this new resultant string as the lookup value in a VLOOKUP. The VLOOKUP will be looking at a named range called DAP on a sheet called DAP, in column 5 for an exact match and I need it to return that value to the cell.
I have tried using the indirect to no avail in different ways, and not sure that I fully understand the usage.
I have an excel workbook which requires us to enter the log details of some sites and then a report is generated using these logs (report attached) . [URL] .......
I want that excel should automatically identify the site and calculate its outage with the following formula:
Outage %age= Total Outage(min)/100.80
and add it in the respective column while identifying the outage.
e.g. if outage is of power then the calculated %age outage should be pasted in the power outage column in front of the respected site.
e.g. in the sheet attached above the first row in the first sheet states that SKU2326 was down for 50 mins
Now %age outage will be
Outage %age= 50/100.80
Outage %age=0.496
Now as the outage is related to power so this outage should go into power column in sheet number 3 labelled as "Network Total Up time Week#17"
Now the next time SKU2326 becomes down it should be added with this 50 mins and the outage calculated as presented above.
im trying to create a drop down menu that changes depending on the results of a vlookup. example: I have a vlookup function that populates a field with a product name based on the part number, however in some cases there are multiple products with the same part number. is there a way to create a drop down menu that contains all the product names shared by the same part number based on the part number that was entered?
View 1 Replies View RelatedI have a sheet that uses V lookups to return names, there are 10 diff names throughout the sheet it could return
I want to change the background colour of the cell depending on what the name is. the v lookup is run in collums A - L and 100 rows down
How can I code it so each name has its own background colour so the Cell changes according what the V look up returns?