ms excel expert

D

dg123

Joined
15 Jun 2005
Messages
366
Reaction score
0
Country
United Kingdom
Any ms excel experts on here? I want to create a dynamic vlookup by using a list of filenames from a range of cells i.e Doc1 range A1:A5 contains filenames for docs to be accessed.

So e.g cell value will be =vlookup(B3,[Doc2]Worksheet1!A1:J20),1,false)

I want to replace the [Doc2] value with that from a cell within the current document. e.g Cell A1 = "Doc2" so the command would resemble

=vlookup(B3,[Cell A1]Worksheet1!A1:J20),1,false)

but I can't figure how to get the Cell A1 value as part of the lookup string.

Any help would be appreciated (tried the usual support sites).
 
Sponsored Links
Can you explain some more, can't quite get my head around what you want from the formula.

At the mo you want to lookup B3 and compare it to data in
Worksheet1!A1:J20 and return what is in the correspoding row in column 1.

So if you enter Doc1 into B3 and it matches in your lookup range it might give you the date of the document if that is what is stored in column 1.

Don't get the extra argument [Cell A1].

:confused:
 
Doc Lenny said:
Can you explain some more, can't quite get my head around what you want from the formula.

At the mo you want to lookup B3 and compare it to data in
Worksheet1!A1:J20 and return what is in the correspoding row in column 1.

So if you enter Doc1 into B3 and it matches in your lookup range it might give you the date of the document if that is what is stored in column 1.

Don't get the extra argument [Cell A1].

:confused:
Click to expand...

Cell A1 contains the name of a document - this document contains the data to be examined by the lookup. I want to insert a list of filenames in one document to use in a lookup string to examine other documents. The Worksheet1 is in Document 2 , Document 3, etc. So in the string the lookup command apperas as [doc1]worksheet1!, I want to replace [doc1] with a value fromn a cell (A1).
 
Sponsored Links
Tis not easy to see what you are trying to do ..
Dynamic range now .....
There is plenty of stuff on this site ..
http://www.ozgrid.com/Excel/DynamicRanges.htm

Dynamic Named Ranges Examples

For ALL examples you need to:

Fill Column A with a mix of text and numeric entries.

Go to: Insert>Name>Define and in the Names in workbook box type any one word name (I will use MyRange) the only part that will change is the formula we place in the Refers to box.

1:Expand Down as Many Rows as There are Numeric Entries.
In the Refers to box type: =OFFSET($A$1,0,0,COUNT($A:$A),1)

2:Expand Down as Many Rows as There are Numeric and Text Entries.
In the Refers to box type: =OFFSET($A$1,0,0,COUNTA($A:$A),1)

3:Expand Down to The Last Numeric Entry
In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1)
If you expect a number larger than 1E+306 (a one with 306 zeros) then change this to a larger number.

4:Expand Down to The Last Text Entry
In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)

5:Expand Down Based on Another Cell Value
Put the number 10 in cell B1 first then:
In the Refers to box type: =OFFSET($A$1,0,0,$B$1,1)
Now change the number in cell B1 and the range will change accordingly.

6:Expand Down One Row Each Month
In the Refers to box type: =OFFSET($A$1,0,0,MONTH(TODAY()),1)

7:Expand Down One Row Each Week
In the Refers to box type: =OFFSET($A$1,0,0,WEEKNUM(TODAY()),1)
Requires the "Analysis Toolpak" to be installed. Tools>Add-ins-Analysis Toolpak
Click to expand...

BTW this is pretty powerful stuff in a spreadsheet !!

:D
 
I'm not sure I can explain it any further, what I am trying to achieve is to write a vlookup command using a value from a cell to complete the lookup range. e.g. the lookup is

=vlookup(E1,[X]Worksheet1!A$1:J$30,1,false)

[X] is the filename value I am trying to insert from a cell. e.g. Cell A1 contains the text "HolidayStats", if I create this manually the command would read

=vlookup(E1,[HolidayStats]Worksheet1!A$1:J$30,1,false)

but I need to do this for multiple lookups and source documents so I want to be able to get the value [X] from a cell, that way I can create all of the lookups and merely alter the value of the cell each time.

The range is arbitrary in this example, the problem is how to make the cell value part of the command string.
 
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

In place of table_array use, for example, Indirect(A1) Where the cell A1 holds the name of the array or data to reference.

Cell A1 to contain the text "HolidayStats" in your example... Think I have it now !!

Excel Help said:
INDIRECT

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Syntax

INDIRECT(ref_text,a1)

Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.

A1 is a logical value that specifies what type of reference is contained in the cell ref_text.

If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.


If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.
Remarks

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
Examples

If cell A1 contains the text "B2", and cell B2 contains the value 1.333, then:

INDIRECT($A$1) equals 1.333

If you change the text in A1 to "C5", and cell C5 contains the value 45, then:

INDIRECT($A$1) equals 45

If the workspace is set to display R1C1-style references, cell R1C1 contains R2C2, and cell R2C2 contains the value 1.333, then:

INT(INDIRECT(R1C1,FALSE)) equals 1

If B3 contains the text "George", and a cell defined as George contains the value 10, then:

INDIRECT($B$3) equals 10

When you create a formula that refers to a cell, the reference to the cell will be updated if the cell is moved by using the Cut command to delete the cell or if the cell is moved because rows or columns are inserted or deleted. If you always want the formula to refer to the same cell regardless of whether the row above the cell is deleted or the cell is moved, use the INDIRECT worksheet function. For example, if you always want to refer to cell A10, use the following syntax:

INDIRECT("A10")
Click to expand...

PS .. Don't forget, the dynamic named ranges they really make the thing flexible!
:cool: ;)
 
not entirely sure what you are trying to achieve or if i have got the idea of what you are trying to achieve but here goes, if the value in A1 is going to be both numerical aswell as text then perhaps you need to use the convert to text formula to make the contents of that cell always text, the formula may not be working as you cannot have two different formats in the same cell at the same time ie it will be formatted for number or text but not both.
 
Indirect() is the function dg123 seeks ..... Tested prior to posting, works a treat on separate tables in the same sheet, hopefully on others too !..

Appears to be the only way to grab Table_Array as a variable character string from a remote cell.
Twas easy as '123' ;) once I knew where dg was going... or was that coming from?

;)
 
OK I have the answer:

=VLOOKUP(A10,INDIRECT("'C:\Documents\Analysis\["&A1&".xls]Worksheet'!A$1:H$31"),5,FALSE)

Thanks all for the help.
 
You must log in or register to reply here.
Sponsored Links
Back
Top