Using cell text in a formula

I am trying to use derived cell references in a VLOOKUP formula to matc
data in several tables. For example, A1 contains the cell reference fo
the top left of my array (A3) whilst cell A2 contains the cel
reference for the bottom right of my array (D14). The array I'
checking against starts in column E3. 

However, when I use the formula =VLOOKUP(E3,A1:A2,4,FALSE) I get a #N/
error. I need to use the cell references in each VLOOKUP as the arra
sizes may vary in each case. (PS, I've used =INDIRECT(ADDRESS(A1,A2)
to derive the cell references.

Ji

--
Message posted from http://www.ExcelForum.com

0
6/2/2004 6:12:38 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
256 Views

Similar Articles

[PageSpeed] 9

Hi Jim,

You are on the track with INDIRECT.

Try this
    =VLOOKUP(E3,INDIRECT(A1&":"&A2),4,FALSE)

HTH
Anders Silven

"Dale Jim >" <<Dale.Jim.178jn0@excelforum-nospam.com> skrev i meddelandet
news:Dale.Jim.178jn0@excelforum-nospam.com...
> I am trying to use derived cell references in a VLOOKUP formula to match
> data in several tables. For example, A1 contains the cell reference for
> the top left of my array (A3) whilst cell A2 contains the cell
> reference for the bottom right of my array (D14). The array I'm
> checking against starts in column E3.
>
> However, when I use the formula =VLOOKUP(E3,A1:A2,4,FALSE) I get a #N/A
> error. I need to use the cell references in each VLOOKUP as the array
> sizes may vary in each case. (PS, I've used =INDIRECT(ADDRESS(A1,A2))
> to derive the cell references.
>
> Jim
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

0
6/2/2004 8:52:12 PM
Reply:

Similar Artilces:

Font set for reading plain text
I've set Courier as the font to read and compose plain text message. However, I don't have idea why my Outlook showed with Arial rather than Courier. The message was from Yahoo account. It seems no difference where messages came from. Can Outlook show plain text message with the font that I set? Thanks, Charles OL2003 SP2 on XP Pro SP2 ...

formula change problem #2
The columns that the formula is referencing can change locations on th spreadsheet, if a user inserts a new column. To simplify the questio I put the actual column numbers in the formula but actually I will hav an integer defined that will hold the column number. That integer wil get populated by grabbing the column number from a named cell in tha particular column. I hope I explained that good enough. I was using the offset's in the formulas because that is what I am mos familiar with. Can offset's not be used in formulas? Is there a better way? Thanks -- cparson ---------...

How do I copy data from one workbook to another whilst retaining formulas on original
I'm looking to run a macro from workbook SWT (sheet CLT) which will cu and paste data from a range of cells (A2,C2,E2,F2,G2,I2,K2.. A3,C3,E3,F3,G3,I3,K3... A4,C4,E4,F4,G4,I4,K4.... until end of data from workbook SWT (Sheet CS) to rows A to G in Workbook SWTS (Shee Stats). I have vlookup formulas (eg =IF(B2="","",VLOOKUP($B2,'Data Field'!$A$7:$B$12,2))) in place in columns A,C,E,F,G,I,K on sheet CS and I'm looking to keep the these formulas in place (unless someone ca think of better alternatives). When I paste the data on sheet Stats i must paste onto the...

How do I use the connection string to connect to a table on the database that I am working on.
I have two related tables. and one form of which one of the tables is a subform and i want to write the data on the subform to a textfile. I used this code to connect to the table but because I have already opened the MDB. I get the error that the file is already openned. objConn.CursorLocation = adUseClient objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Documents and Settings\exds006\Desktop\Electronic LIV;" & "Persist Security Info=False" objConn.Open Is there another way of accessing th...

Text Box Control contains generated hyperlink
Hi. I have a textbox control on a report that contains a generated hyperlink. I output this report to a PDF and when I click the link it only returns the first line of information. This link contains links to documents that reside in a database, so if the link should contain 10 documents, it only returns 8 because that's what fits on the first line of the link. Any ideas of what is happening? Cathy ...

how to use the search dialog box ?
Hello Everybody, I have a question concerning the opening of dialogs box. I would like to open the Search Dialog Box. My stupid code is like this : Private Sub CommandButtonSearch_Click() 'ouvre la boite de dialogue Rechercher Application.Dialogs(xlDialogFormulaFind).Show End Sub With this code, the problem is that the Search Dialog box is indeed open but whatever the text to find in the worksheet, there is no result. If I stop the macro, open by hand the Search Dialog box and type the same text, it works... Can you help me to solve this problem ? Thanks a lot in advance. ...

Countif using symbols
I have a worksheet that has a column containing icons, from the new options of conditional formatting, in Excel 2007 (Icon sets) I need to count how many of each icon I have. Can I do this using the function COUNTIF(). I couldn't figure out what should I use as criteria here. Thanks in advance. Regards, Emece.- ...

Using a common outlook address book on a home network
How do I configure my system so that all computers on my home office network can use the same list of contacts? Each computer has Outlook 2000. Two use Windows 2000 Professional; one uses Windows XP. Matt Arnett <anonymous@discussions.microsoft.com> wrote: > How do I configure my system so that all computers on my > home office network can use the same list of contacts? > Each computer has Outlook 2000. Two use Windows 2000 > Professional; one uses Windows XP. Create a network share, place your PST in that share, map a drive to it on the other machines and open the P...

An issue regarding the formula
Hi, It's OK to copy the formula from one cell to another one within the same sheet. How about that to copy the formula from one Excel file to another one? It seems only the value of that cell will be copied to that file but not the formula inside it. I even have tried to copy the corr. formula within that again but just* the copied formula will not work successfully in there. * Thks & Rgds, HuaMin -- HuaMin ------------------------------------------------------------------------ HuaMin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29423 View this t...

Creating and using a Deleted folder
I am using outlook 2002 and when an item is deleted it shows as the item with a line thur it. Is there anyway to have the deleted item moved to a deleted folder. I am running outlook with 3 connections. Two I IMAP and one with MS exchange. The exchange one does have a deleted folder but the others don't Scott Adams <adamsfour@aol.com> wrote: > I am using outlook 2002 and when an item is deleted it > shows as the item with a line thur it. Is there anyway to > have the deleted item moved to a deleted folder. No. That's the way IMAP works. -- Brian Tillman ...

using dmax
I want to find the max temperature for each day of the year over 10 years worth of data. I can do it by using a criteria where the first in the range of the criteria is the label ("Date" in this case) and under that cell is the date I want to look up (eg "Jan,3"). But it seems like I need to have 2 cells per day of the year in order to specify each day. That is, it looks like I have to do it this way: A B 1 Date 2 ="Jan,1" =dmax(H100:I465,2,A1:A2) 3 Date 4 ="Jan,2" =dmax(H100:I465,2,A3:A3) 5 ...

Sheet tab text color
...

how do i change the color of a cell auotmatically?
I am desiging a data shee and i want to changes the color of a cell based on the valuse of anthor cell. ex. if "cell m34" is equal to 0 then "cell p21" is white, if cell m34 less than 1548, then "cell P21" is yellow Can this be done? If so, please help me out. Hi! Select cell P21 Goto Format>Conditional Formatting In the dropdown select Formula is Enter this formula in the box: =AND(M34<>"",M34=0) Click the Format button and select the style to apply then click OK. Click the Add button In Condition 2 Formula is: =AND(M34>=1,M34<=154...

Is it possible to have text and currancy in one cell and ...
Is it possible to have text and currancy in one cell and be able to use the numbers in that cell to make an equation with another? The short answer .. No -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "thegen" <thegen@discussions.microsoft.com> wrote in message news:1552FF10-9CAA-42AD-B401-B0D229D6A343@microsoft.com... > Is it possible to have text and currancy in one cell and be able to use the > numbers in that cell to make an equation with another? well maybe depending on how the "text & currency" was ar...

How to skip cells with text strings.
What should I add to the code below to cause it to skip a cell tha contains a text string in the cell above it? I need it to only work o numbers in the cell above it. If ActiveCell.Offset(rowoffset:=-1) <> "" Then ActiveCell = ActiveCell.Offset(rowoffset:=-1) + 1 Application.SendKeys ("{Enter}") End If Thanks so much. mikebur -- mikebur ----------------------------------------------------------------------- mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2458 View this thread: http://www.excelforum.com/showthread.php?thread...

Diffrent cell on diffrent date?
I�m going to TRY and explain this the best I can. For work when we do our cash out at the end of the night we use a exce sheet. This sheet is to add up our change, checks, credit cards etc etc. etc. When we're done we print it out and than type the figure into another excel sheet. I pretty much know how to combine the sheets so instead of typing the numbers into SS1 and than entering i into SS2 I can have the numbers going from SS1 to SS2 automatically But my question is that how do I set it up so when I type the info i SS1 that it will go to in to SS2 but to have the cells change by dat...

Excel Formula Guidance. Formula need to determine if cell is popul
Hi, Hopefully someone will be able to provide some guidance. I have been tasked with setting up some spreadsheets at work to application coming into my department and the number of days it takes for us to deal with them. To work this out is easy enough using formula =NETWORKDAYS(B4,E4,$A$119:$A$158) with the numbers involved with the $ referencing bank holidays listed in the spreadsheet and B4 received date and E4 being the completed date. I now need to include some extra lines as I need to be able to monitor written enquiries as well so the headings would read Application ...

Excel text to Word text
How can I copy Excel text, then paste it into MS Word without carrying over the grid lines? Any help would be appreciated. Thanks Ed English I don't believe you can copy and paste without the gridlines. However, in Word, select the data and Table>Convert>Table to Text. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Ed" <ee15803@att.net> wrote in message news:3f7e21e9.53324199@netnews.worldnet.att.net... > How can I copy Excel text, then paste it into MS Word without carrying > over the grid lines? Any help would be appreciated. Thanks &g...

CCTV text insertion??
Is there anyone using text insertion from RMS? I need a way to insert text most times this is done off receipt printer or off a com port. The reason i dont like using a receipt funtion is rms dumps all the info at the end of the transaction i would rather have it while the cashier is ringing up that item. I would use it as a pole display but not everything is sent to the pole display. JUst wondering if anyone else was using text insertion on a rms system. thanks for the help Nope right now only way is printer. "Doug Pic-N-Pac" <DougPicNPac@discussions.microsoft.com> w...

Wildcards with Array Formulae
Hi, I'm looking for some help with a formula I'm using to pull statistic out of a client spreadsheet. The formula uses the SUMIF command to loo at one column, check for a text value then look at another colum within the same row and check for another text value. It returns value of one if both are true and a value of 0 if one or none are tru then keeps a total. It's a pretty standard array formula, but I need i to use wildcards because the second column can contain value A, value or value AB. I want the formula to pick up all instances where value appears, including where it a...

Convert Incoming Plain Text to HTML
Is there a way to have Outlook (2003) automatically convert incoming Plain Text messages to HTML without manually changing the formatting option? Thanks in advance, Jody As far as I know, no. You can go from HTML to Plain Text but not the other way. "J. Owens" wrote: > Is there a way to have Outlook (2003) automatically convert incoming Plain > Text messages to HTML without manually changing the formatting option? ...

Why does Outlook display text in yellow? How can i change it?
When pasting text into an e-mail, it appears in pale yellow. Sometimes, parts of meesages from others are also yellow. How can I prevent this? Are you using stationery by any chance? -- Kathleen Orland "Wise Dog" <Wise Dog@discussions.microsoft.com> wrote in message news:2D2FBAF7-2B2B-4C66-AE33-B2CA5A51B821@microsoft.com... > When pasting text into an e-mail, it appears in pale yellow. Sometimes, > parts of meesages from others are also yellow. How can I prevent this? ...

Coping numbers to next blank cell on another worksheet
I am just learning Excel (need SIMPLE help) but any help would be greatly appreciated. My OS is XL Professional and I have MS Excel 2002 SP2. I need to copy calculated numbers (I got this much done on my own) in specific cells on worksheet # 1 to the next blank cells in existing columns of numbers used in calculations on other worksheets in the same workbook. I other words, I have a formula that calculates numbers and places the results in the same column and row each time it is used. Those calculated numbers need to be copied and used on different worksheets in the same workbook. While...

Creating XML Text
Hello, I need to create some well formed XML text live in memory. What I really want is to use the XmlTextWriter object applied to some location in memory (Stream or something?), because it does a nice job of formatting/identing, etc. But I can't figure out how to instantiate the XmlTextWriter object correctly w/o a filespec. I'm not allowed to directly instantiate a Stream & therefore a StreamWriter for that matter since there's no filespec involved. Can someone show me some code to make this work? TIA, -- John C. Bowman Software Engineer Thermo Electron Scientific I...

Conditional formula with an if statement
I am wanting to create a "envilope" budget sheet and have ran into a problem. What I am trying to do is make everything in Column B all negative unless I put a figure in Column A, which would make the Column B Positive. Example: Column A Column B Deposit Car (nothing here) $50 (this will show up negative) $500 $500 (This figure will show up positive) I want to be able to add numbers in both column A and B but reflect the appropriate sign as an expense (with nothing being added into deposits) or as income, when ...