Embed An INDIRECT() Into A VLookup()
May 21, 2009
I just learned how to do an Indirect.
So that i can make many pages without having to go through and change everything.
I want have excel search for a name contained in A1 in a table (so i use vlookup).
then but i want be able to change the sheet name easily.
so what i need is something like this:
=VLookup(A1,(INDIRECT("'" & B2 & "'!")),$b$6:$S$23,2,)
this does not work.
But i want the sheet name to be the thing INDIRECT looks up, while the name in A1 is the thing that the VLookup is trying to find.
View 9 Replies
ADVERTISEMENT
Sep 4, 2013
My vlookup works.
My IF(ISNA works.
Now I just need to incorporate a formula for if is blank. Not sure where to incorporate it or how.
=IF(ISNA(VLOOKUP(F2,Contacts!H:J,3,FALSE)),"Unknown",VLOOKUP(F2,Contacts!H:J,3,FALSE))
View 4 Replies
View Related
Aug 18, 2009
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
View 3 Replies
View Related
Sep 17, 2008
I have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.
This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.
I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:
View 14 Replies
View Related
May 8, 2014
I am having issues using the INDIRECT function to lookup data from a sheet with the same name as that appearing in a given cell. For example, in cell D27 i have the text "S1_358_810" (Not including quotations). I also have a sheet named "S1_358_810". My formula is as follows;
Formula:
[Code] .....
However this is returning #N/A. There is a list of numbers in sheet S1_358_810 in column N and from that I want the value in column Q (thus 17).
View 11 Replies
View Related
May 11, 2014
I have two workbook, test1 (master file) and the other one is test_10_05_2014 with 100 some of them being updated daily. The last portion of the file "test_10_05_2014" is the date and we have different file on daily basis.
using Indirect function in vlookup should give corresponding value for the particular name. Formula used is giving error,
[VLOOKUP(A2,Indirect(" ' "&E1&" ' !$A$2:$X$1000),6,FALSE)]
View 2 Replies
View Related
Jun 21, 2007
IF(ISNA(VLOOKUP($B$2;INDIRECT("'G:2007Projekt opfølgning[OMKLIST.xls]kreditor!'"&J24&":"&L24);2;FALSE));"0";VLOOKUP($B$2;INDIRECT("'G:2007Projekt opfølgning[OMKLIST.xls]kreditor!'"&J24&":"&L24);2;FALSE))
It is the part regarding til indirect that gives me problems
This form I can get it to work
IF(ISNA(VLOOKUP($B$2;INDIRECT("[OMKLIST.xls]kreditor!"&J25&":"&L25);2;FALSE));"0";VLOOKUP($B$2;INDIRECT("[OMKLIST.xls]kreditor!"&J25&":"&L25);2;FALSE))
But here I need the Omklist.xls to be open. And that I dont want.
The what i want is that the user can use the sheet with out oping the data sheet..
View 9 Replies
View Related
Mar 12, 2008
I'm having difficulty referencing named ranges indirectly in a VLOOKUP formula (Excel 2003).
Cell A1 contains a fuel cost.
Cell A2 contains a formula that returns a RangeName.
In Cell A3, I want to enter a VLOOKUP formula that uses the range returned as the result in Cell A2 as the Table_array. In other words: =VLOOKUP($A$1,$A$2,2,FALSE). The result is #NA!.
If I use =VLOOKUP($A$1,INDIRECT($A$2),2,FALSE), I get #REF!.
Among other things, I've also tried =VLOOKUP($A$1,TEXT($A$2,"@"),2,FALSE). No cigar.
View 9 Replies
View Related
Nov 23, 2012
I want to extract/generate data from a database (same as like we can do with filter or pivot method) with H/VLookup or any other method.
Like:
Sheet 1 (Database)- 23-column & 690-Rows having record of 9 Different Groups)
Sheet 2 (Report)- A2 contains List of 9 Different Groups, If I select Group 3 from that list then I want to show all relevant data of Group 3(23-Column) in this sheet.
View 9 Replies
View Related
Mar 19, 2013
Code:
=D5-VLOOKUP($C5,$C$45:$F$80,2,0)
I have formulas such as above in my Worksheet. So the above is in Cell H5
For the Range $C$45:$F$80, I am inserting rows (So moving down the data) and copying data from $C$5:$F$40 as values into the new space in $C$45:$F$80
I am doing the above using a macro, but when I run it I want this
Code:
=D5-VLOOKUP($C5,$C$45:$F$80,2,0)
to stay static, but instead I end up with
[CODE=D5-VLOOKUP($C5,$C$85:$F$120,2,0)[/CODE]
So my Table Array $C$45:$F$80 changes to $C$85:$F$120
How can I keep it as $C$45:$F$80
The other references in the formula seem to stay as I want them.
View 2 Replies
View Related
Dec 31, 2008
troubleshooting dependent validation list formula. I found the Ozgrid instructions for the Dependent Validation Lists to be verfy informative and I downloaded the "MatchingLists.xls" to use as a starting point.
My challenge is this: The MatchingLists.xls example does not take into account that there may be MULTIPLE dependent lists that have spaces, numbers and other unique characters in the list's source celles. Therefore, I have decided to reference a NameLookup (named range) that has an abbrevieated name for each each item (without numbers & spaces) I am nesting VLOOKUP within the INDIRECT formula to achieve the results I need - but for some reason it fails occasionally and I do not know how to troubleshoot!
Data Validation Source =INDIRECT(VLOOKUP(SUBSTITUTE(E5," ","_"),NameLookup,2,0))
Excel's Data Validation simply returns the message "The source currently evaluates to an error". This is frustrating becuase it is not consistent. It works with some lists and not with others. How do I troubleshoot???
My worksheet is attached for your reference. The source data for the dependent lists is on the Lists sheet. The dependent drop-downs are in columns E-H on the drop-down sheet. The formula that I am having trouble with is in "Subcategory" (column F). For some reason, I cannot choose category "26_Heat_Exchangers" to populate the subcategories. There are a few other categories that this formula breaks on and I don't know why. Please tell me this is not some crazy limitation with Named Ranges.
View 3 Replies
View Related
Aug 18, 2009
I am trying to develop an Indirect Indirect Validation drop down list. Example, Building - Floor - Room, i.e. Select Building from a Validation drop down list. Then based upon the Building selected, select only the Floors applicable to the Building Selected. I am able to achieve this via an Indirect Validation drop down. However, when I attempt to then select the Rooms applicable to the Floor of the Building I selected, I can not produce an Indirect Validation off a previous Indirect Validation.
In the attachment, I have used Plant - Location - Room. I have name ranged the selections, and have used Validations Lists for Plant, and Indirect Validations for Location. The error occurs where I attempt to do an Indirect Validation for Room.
View 3 Replies
View Related
Mar 4, 2007
How do I embed a pdf file into a worksheet. I've got the following code so far:
ActiveSheet. OLEObjects.Add(classtype:="AcroExch.Document.7", link:=True, DisplayAsIcon:=False).Activate
The code works but it doesn't allow me to specify the filename (i.e. C: est.pdf)
View 2 Replies
View Related
Jan 4, 2008
I have a need to embed a wav file into a workbook. This is fairly simple to do through the insert>>object>>create from file, but I see no way to do this through VBA. I want the entire process to be simple so the user just has to click a button, pick their file from explorer, then play the sound file through the little speaker icon that is generally created.
In the end the one sheet (filename.xls) is the only thing sent through email and it must contain the wav embedded inside without using the insert>>object>>create from file route.
View 9 Replies
View Related
Nov 19, 2013
I am creating a large excel file with multiple tabs and I need one of the tabs to contain a PDF. I have tried going to insert - object - browse - found the PDF file but it imports very blurry and it's quite hard to read. I tried inserting the object/PDF as a "link to file" and/or "display as icon" and all that essentially does is insert a shortcut to the PDF so that only works if you're on the same computer you used to create the link. I need to be able to send this file to another computer and be able to access the PDF somehow. Am I going to have to buy Adobe's ability to convert PDFs to .xls?
View 3 Replies
View Related
May 29, 2014
I am trying to show the select file window, choose a pdf, and insert it into a new sheet. i'm using this code:
[Code] .......
I am receiving an error: Run-time error: 1004 Cannot insert object.
View 1 Replies
View Related
Oct 25, 2008
why there is no option for adding a picture manually to the comment of a cell? when editing a comment the Insert picture from file from the Insert menu is disabled ! (Office XP)
and can you please edit the code so the Width and Height of the comment box is set exactly to the dimensions of the picture? (that is how to get the exact width and height of the picture being inserted from code in excel ?)
View 2 Replies
View Related
Oct 23, 2008
I've already nested INDEX and MATCH sucesfully
I wish to embed/nest (I'm not sure what the difference/ correct syntax is)
These Funtions:
1. "=INDEX($H$4:$H$9,MATCH(G14,$G$4:$G$9,0))"
2, =HLOOKUP(Arg!D10,Arg!$D$10:$DA$50,5,FALSE)
The HLOOKUP returns a text value that the Index and Match convert into a number. I wish this to be done in one cell - is this possible?
I'm using INDEX with MATCH because HLOOKUP can return a 'not applicable' that equals '6' this need to be discounted in my results but that is a digression FYI.
View 2 Replies
View Related
Oct 18, 2012
I am working on a financial tracking sheet for somebody in my organization. The totals have to be entered as number of bills, but he wants to read it afterword as $ amount. So is there a way to type in the number of bills in a cell (ie, 15) but have the $ amount (ie, $300.00) show up in the same cell when he hits enter or tab? This is my least favorite solution (assuming it is even possible), but it is what he is asking for. I feel like I just need to know that it won't work before he agrees to try something else.
View 6 Replies
View Related
Jul 4, 2008
I have an Excel spreadsheet with Harvey's Balls font and need to get the file distributed amongst coworkers. Is there a way to embed the font into the spreadsheet so they don't have to install the font?
View 9 Replies
View Related
Oct 29, 2008
i have a sheet that autogenerates a .csv file and uploads it into a webform. In order to do this I have to open an Internet Explorer window go to webpage and use the sendkye function, my code looks like this:
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.Navigate "http://uploadsite/upload.form"
'Check connection til web loop!
Do
If ie.ReadyState = 4 Then
ie.Visible = False
Exit Do
Else
DoEvents
End If
Loop
if I instead could EMBED the webbrowser into my sheet and then go to the upload site and upload the file directly from the sheet. I have some basic vba skills, but I have never worked with the embedded objects.
View 9 Replies
View Related
Dec 30, 2006
I am trying to embed part of a webpage into my worksheet. Specifically, I want to include a form from a web-site that allows me to enter a flight number. I then want the results to pop-up in a new web-window.
So, I want the search box (or a section of the webpage) to appear in my excel worksheet so that I don't have to always open IE when searching for data.
[url]
That's the web page.
View 3 Replies
View Related
Jan 7, 2007
I have a file with two sheets - Main and Reference. The file has a macro that when used from Main sheet displays some data (images to be specific) from the Reference sheet. The main sheet is of a specific format and has some buttons to call the macro.
I want to have the capability to create any number of main sheets of the same format. I know this can be done by saving the Main sheet as a template and right clicking on a sheet and clicking 'Insert..'. But that would require the template to be stored in the excel start file on the local computer.
I want to know if there is any way the worksheet template can be stored within an excel file, so that if the file is sent to people on a different computer, the user can easily add more worksheets using the template?
View 9 Replies
View Related
Jun 1, 2007
I've just written up an SOP for a reconciliation process. Part of it requires the reader to use some Excel UDFs that I've created. Rather than having someone look for the UDF file on the network, I tried to embed it as an Excel Worksheet Icon within my procedure document. However, when I double-click the icon to launch the add-in, I get the following error message:
"Microsoft Word can't start the application required to open this object. An error occurred and this feature is no longer functioning properly. Would you like to repair this feature now?" This does not happen when I embed a regular Excel workbook, or even a template.
View 5 Replies
View Related
Jan 4, 2008
I have a need to embed a wav file into a workbook. This is fairly simple to do through the insert>>object>>create from file, but I see no way to do this through VBA. I want the entire process to be simple so the user just has to click a button, pick their file from explorer, then play the sound file through the little speaker icon that is generally created. In the end the one sheet (filename.xls) is the only thing sent through email and it must contain the wav embedded inside without using the insert>>object>>create from file route.
View 3 Replies
View Related
Apr 6, 2008
I am not sure whether "embedding" is the right word. Please have a look at this excel file and tell me how to make such a file? .....
View 7 Replies
View Related
Aug 27, 2009
The field is a concatenation of three other fields and only the first of three parts should be boldfaced -- the second and third part should be normal faced.
Is there an easy way to do this in excel 2007? Right now, the formula is:
=CONCATENATE(UPPER(TRIM('Raw Data'!H26)),CHAR(10), TRIM('Raw Data'!G26)," [",TRIM('Raw Data'!J26),"]")
and the part in red, is to appear boldfaced.
View 9 Replies
View Related
Feb 22, 2008
I am attempting to merge a scroll bar and an activeX text box. I currently have a horizontal scroll bar that links to a cell with values from 1-20, A dynamic label in the adjacent cell changes when the scroll bar moves. I need the label to be inside the scroll bar track/tray. I was told an activeX text box would do the trick. However, i am new to VBA and activeX text boxes any code or solution to this problem. Conditions:
The slider and the track should overlay the text box label
The track should be transparent but on top of the label
The label should be a perfect fit to the slider's tray
Here is a reference to the scroll bar labels. I have posted an example file of what i am attempting to achieve.
View 2 Replies
View Related
Mar 29, 2008
How do you embed a flash file into excel 2003?
And is it userfriendly? For users to use the excel file, they must have flash software installed right?
View 5 Replies
View Related
Sep 15, 2014
I have a named range called ReportMonth and I want to embed it into this Table reference to make the number of columns that are summed dynamic:
=SUM(tblFcastTemplate[@[Column1]:[Column28]])
I tried ="SUM(tblFcastTemplate[@[Column"&(ReportMonth+1)&"]:[Column28]])"
View 4 Replies
View Related
