Vlookup function imbedded in the table_array value of a vlookup function

Hi,

I am creating a go-karting championship management system for my
a-level and I am having a few problems.

Basically there are 6 races and They are set out as Race1,2,3,4,5,6 and
the data for the drivers is entered under that. However, the spreadsheet
is organised via the dates of the races which means that the data
entered as race 1 could be for race 2,3,4,5.

I have already got it to calculate this and it displays the date, the
original race number and the race number after organisation from the
dates of the races.

Now, this is all hidden from the user in a hidden sheet and on the user
viewed side I want the races to be viewed by date position and not by
their original name/position.

To do this I want to use a Vlookup function which looks up b34 within
the table array value which will change, relative to the dates of the
races.

It's probably best to give you my formula and see if you  can see
anyways around it:


=VLOOKUP(B34,VLOOKUP(1,C122:D127,2,False),2,False)

when I use this, in theory it should work and give a correct value but
instead it just gives a #value error because I think it is looking up
"VLOOKUP(1,C122:D127,2,False)" as the name of a table array but I want
it to lookup the result of this formula as the table array name.

just to confirm:

=VLOOKUP(1,C122:D127,2,False) on it's own gives "races_1" which is the
name of one of my table arrays.

and

=VLOOKUP(B34,races_1,2,False)



I'm sorry if I've confused you but any help would be greatly
appreciated

Thanks
Scott


-- 
snewham
------------------------------------------------------------------------
snewham's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28514
View this thread: http://www.excelforum.com/showthread.php?threadid=490124

0
12/2/2005 12:23:22 PM
excel 39879 articles. 2 followers. Follow

1 Replies
552 Views

Similar Articles

[PageSpeed] 21

Try using the INDIRECT(  ) function - look it up in Help.

Pete

0
pashurst (2576)
12/2/2005 1:33:57 PM
Reply:

Similar Artilces:

error C2661: 'CStdStr<CT>::Format' : no overloaded function takes 19 argumentswith[CT=char]
Hi, I am getting following error, error C2661: 'CStdStr<CT>::Format' : no overloaded function takes 19 arguments with[CT=char] If you have the solution please provide it. Thanks, Shilpa Did you compile as Unicode, but still put in an ANSI format string like ..Format("%d") rather than .Format(_T("%d")) Tom "shilpa" <bharatid@cybage.com> wrote in message news:1174467883.296564.83070@p15g2000hsd.googlegroups.com... > Hi, > > I am getting following error, > > error C2661: 'CStdStr<CT>::Format' : no overloaded ...

assign value based on criteria and total
I have cells from c9 to AB9 that are populated with 1 of 4 different letters. I have been able to get a count based on the criteria but what I need to do is this: If c9 to ab9 have an "a" in it count as 1 but if it has a "b" in it count as ..5. Is this possible. Thanks for any suggestions Hi, Maybe this =COUNTIF(C9:AB9,"a")+(COUNTIF(C9:AB9,"b")/2) Mike "rascal" wrote: > I have cells from c9 to AB9 that are populated with 1 of 4 different letters. > I have been able to get a count based on the criteria but ...

Count each group of values
I need a formula or vba code to count how many groups of "X" values and how many "X" in each group. Sample: a1 ah1 X X X X X X X A A A A X X X X X X B B B B B B X X X X X X X X X X X X Result i need with the code or formula 1 group 7 "x" 2 group 6 "x" 3 group 12"x" Thanks for help Paulo JP, Bob Umlas has all the answers at http://www.emailoffice.com/excel/arrays-bobumlas.html --- Message posted from http://www.ExcelForum.com/ Thanks "Kieran >" <<Kieran.1023bg@excelforum-nospam.com> wrote in message news:Kieran....

Free books on Charting, Basics, Data Management, Functions; Free Add-In "Excel Usability Enhancer"
You may download the free books and software from http://www.vgupta.com (offer good till Dec 31 2003) Excel For Beginners Charting in Excel Excel- Beyond the Basics Managing & Tabulating Data in Excel Financial Analysis Using Excel Statistics Using Excel Software: "Excel Usability Enhancer" Vijay Gupta ...

Vlookup in vba
Hi I have the following snippet of code: Sheets("mdata").Select Range("E1").Select ActiveCell.FormulaR1C1 = "RVU" Range("E2").Select ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2)),0,VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2))" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E" & LastRow), Type:=xlFillDefault This fills about 5,000 rows with look up data from the rvu ws, and puts in 0 if #N/A is obtained. HOWEVER, if I was ...

Copy Record Button Function
Hi. I have two forms that I would like to add a copy record button to. But I get error messages [Run-time error 3414] when I try. It points to this line of code when I click debug: If Not rs.EOF Then Me.Bookmark = rs.Bookmark After troubleshooting for a bit, I realized that both forms have lookup boxes in the form header section. If I delete the lookup box from the form I can add a duplicate record button without a problem. Is there anyway to have both the lookup box and the duplicate record button on the form simulatneously? Here is the code for one of the lookup boxes ...

What kind of function should I use?
Hi there, I was wondering what type of function should I use in the xml reports for the receipts. I need a rounding function but I can't find info on which language or standard they use. Please help! DO NOT DELETE THIS QUESTION PLEASE! Thank you Julien ------=_NextPart_0001_207E425D Content-Type: text/plain Content-Transfer-Encoding: 7bit Good morning Julien, Thank you for posting your rounding question. Can you please let me know what you are trying to round? Or is this related to your question about discounts? Todd Berger Microsoft Online Support Engineer Get Secure!...

Format Function
Below is a snipplet of code that gets a recordset. I'm trying to output to a text file and keep my columns of data straight by using tabs. Because of the length of records in the field objRS!sShift, the vbTab code wasn't lining my column correctly, so I used the Format() function with the "@@@@@@" parameter and my data from the objRS!sShift now right-aligns. My new problem is I have a new field called "percentData" that is a percent that I need to display as 54.62% for example. Without the Format() function, the raw output of this field looks like 0.5462625 f...

Passing a value in VBA using an offset
I have code that executes when I double click on a cell. The code inserts the data validation source into a combobox to ease the input choice. It refers to the cell I double clicked as "target" I want to add a new feature. Before anything else happens, I want to filter the data validation source (many of the entries are for dates that don't apply. the cell above "target" has a date. I want to copy the month of that date into a fixed cell ("A1") and then use that date in an advanced filter on the data validation source. Hope you're with me so far. T...

returning two values
I have a function in which two indexes are calculated. I would like to return them both. Is this possible? "Steve Russell" <srussell@removethisinnernet.net> wrote in message news:eg8tCGKwFHA.2516@TK2MSFTNGP12.phx.gbl... > I have a function in which two indexes are calculated. I would like to > return them both. Is this possible? If they will fit, you could use something like MAKELONG. Otherwise you could use OUT params. -- Jeff Partch [VC++ MVP] "Jeff Partch [MVP]" <jeffp@mvps.org> wrote in message news:%23a2ShLKwFHA.2348@TK2MSFTNGP15.phx.gb...

Outlook.exe r6025 pure virtual function call
I was using Outlook 2002 with SP3 and I was creating an e- mail and it shut down and gave me the following error: OUTLOOK.EXE performed and error R6025 - Pure Virtual Function Call Does anyone have an idea what is this. Thanks; ...

VLookup question #3
I've tried searching for an answer to my question, but with no luck. I'm working on a sheet to compare to lists of numbers, using this formula: =VLOOKUP(C6,A4:B392,1,TRUE) I've tried to use a constant in the formula, but it doesn't work. Assuming that {} defines a constant, why can't I do this? =VLOOKUP(C6,{A4:B392},1,TRUE) so the array I need to search is constant? Peter, You need absolute cell references ($A$4:$B$392), not relative (A4:B392). The F4 key will switch this, if the reference is selected while you're in Enter or Edit mode. =VLOOKUP(C6,$A$4:$B$39...

Converting formulas to value
I have a workbook in Excel 2007 that contains cells which are external references of another source. This is a monthly workbook that I now wish to save and transfer to a disk for storage. I believe that I will lose the amounts shown if the formulas are not converted to value first. If this is correct how can I convert the formulas to value? Thanks very much for your help. Gene L. "Gene L." <elieve@tampabay.rr.com> wrote in message news:ebpcaMl2IHA.3920@TK2MSFTNGP02.phx.gbl... >I have a workbook in Excel 2007 that contains cells which are external >references of a...

Set default values marco
I have a model where there are several user defined entries. The ones I can't seem to get to work are resetting a value from a drop-down menu. When recording the macro it does not acknowledge a selection from the drop-down menu. Due to the complexity of the model I want to add a reset defaults button to reset all the values on a worksheet. If anyone knows how to get a macro to reset a value from a drop-down menu please let me know. Thanks! Hi something like activesheet.range("B1").clearcontents -- Regards Frank Kabel Frankfurt, Germany RJ wrote: > I have a model wher...

Where are functions stored in CRM
I have been manadated to alter an already modified Opportunity form, I will be adding some fields that will be used in some defined calculations. Those calculations are being completed through a function which is placed in the <<On Change>> event. I have to access the function to add the additional values that will be calculated, where would I find it?? -- Thank you for the help!! Richard The OnLoad code can be found in the Form customization access via the Settings menu. You need to open up the Form for the record, then access the properties of the form. -- Matt Parks...

cell formula to change value by one
excel 2003 in ( I9 ) there is a value 1000 I want to be be able to + 1 on close can anyone help Hi Alan This will do it... Private Sub Workbook_BeforeClose(Cancel As Boolean) Range("i9").Value = Range("i9").Value + 1 End Sub If you don't know where to place it.select the small XL icon to the left of the File menu on top, right click select View Code. that should bring you to the VBA editor in the Workbook Event module. Just paste the macro on the big white page. HTH Cimjet "Alan R" <mastergasfitter@bellsouth.net> wrote in message news:fdc3a5b1-5da...

Fill values for formula
I am trying to write a formula that will do the following: if a cell i fill in with color, do not sum values, otherwise sum values. However I do not know what the fill variable is to put in the formula. Hop this make sense. Please help. Thank You -- Message posted from http://www.ExcelForum.com Hi gardener! See: Chip Pearson: http://www.cpearson.com/excel/colors.htm Watch out for colors that result from conditional formatting as they are treated differently from "manually" imposed colors. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Exc...

Validation for Numeric values
I want to add validation to a cell as follows: Cell is formatted as Accounting, 2 decimal places Cell can have either whole nunmbers, or decimal entered into it However, some staff are also entering text So, what I want to do is, add validation to the cell that will accept a numeric value, with or without decimals, and prohibit the entry of anything else. Can it be done? TIA Duncs Select the cell; Use Data | Data Validation; specify Allow Decimal Optionally, open the other two tabs on the Data Validation dialog to add messages for the fools who type text. Typing text will now cause the P...

Macro to check data from excel list against access query and return value back to excel
(I posted this yesterday but am tryign to make it clearer) I have an excel list with "name" and "title" and there are many erros and mis-spellings. I havbe an acess quesry that has the correct names and titles and also some mis-spellings and foreign language spellings pointing to the correct english name and title. I would like to write a macro in excel to do a lookup of the excel value in the access quesry and find EITHER an exact match or close match and return the value of a specified field back into an excel column. Here is a hypothetic example: Access list: PK=Van G...

Portfolio Review--After tax Value is *greater* than pre tax value
Why would the pre-tax value for long term and short term holdings be less than the after tax value? I just discovered the same issue and find your post while investigating. I have no idea what is going on. "WOppenhe" wrote: > Why would the pre-tax value for long term and short term holdings be less > than the after tax value? > > > ...

VLookup 05-27-10
I have a workbook with two worksheets. On the first - it has the following columns: Inv # Employee Fees Paid on the second worksheet i have the following: Inv # Employee Fees Billed Hrs Billed I want to be able to pull the information from the 2nd worksheet onto the first one. I know how to do vertical lookups but in this case, I need two. I need the system to first look for the invoice # and then look for the employee. Once it sees those two matches, i want it to populate the fees billed and hours billed. Is this possible? Thanks! I almos...

Incorrect values in cost tracker
I am using the cost tracker to track clothing expenses. The amounts reflected in tracker seem to capture only expenses, but not returns. Hence the amounts are always off (we return a lot of stuff!). In reports, I can select to include payments or expenses or both, but I don't see any options for the expense tracker. Any ideas how to fix this? ...

adding functionality
I am building a web page and some how lost this option in the middle of doing it. Other than starting over can I retreave it? From Format Pub. open web options then add to your website . from here should find Add Functionality which open a list of option including SELL PRODUCTS which is what I want to do. But it is no longer there. -- Thanks James Sorry I failed to post the verson. I am using windows 7 and publisher 2007. -- Thanks James "James Kiefer" wrote: > I am building a web page and some how lost this option in the middle of doing > it. > O...

help with string functions please
Can anyone help with a formuila for the following, I'm struggling . I have a large number of text strings (in a column), many (not all) of which contain 2 words or phrases separated by a colon, eg. ABC : DEF . I want to split the string into two at the colon, giving 2 columns one with ABC and the other with DEF. The colon itself is redundant. I need to be careful not to corrupt those text strings which do not contain a colon, leaving the 2nd olumn blank in such cases. This seems messy to me, any help would be appreciated Thanks K On Mon, 29 Jun 2009 12:15:27 +0100, "...

How to IF function to return date if True, 0 if False
I am working on a spreadsheet where I need to determine the number of days from Date1 to Date2. If there is not a Date2, I want Excel to return a value of 0. Does anyone know how to do this? Laura, =IF(Date2<>"", Date2-Date1, 0) Format for ordinary numbers (not date), like General. Substitute your date cells for Date1 and Date2. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Laura M." <lcmerr01@louisville.edu> wrote in message news:039a01c38f64$e1b4cd10$a101280a@phx.gbl... > I am working...