Free Microsoft Excel 2013 Quick Reference

How to use a cell value as Table Array in VLOOKUP worksheet function

I need to use VLOOKUP for a spreadsheet I'm running and need to use the
value in a cell as the name of the table array for VLOOKUP.

Is this possible ?

--
willydlish
------------------------------------------------------------------------
willydlish's Profile: http://www.excelforum.com/member.php...o&userid=19985
View this thread: http://www.excelforum.com/showthread...hreadid=345737


Post your answer or comment

comments powered by Disqus
I need to use VLOOKUP for a spreadsheet I'm running and need to use the value in a cell as the name of the table array for VLOOKUP.

Is this possible ?

I would like to use the value of a cell, in this case a stock ticker, to
refer to another worksheet that is named identically. For instance, I have a
cell value UPS and a worksheet named 'UPS', I would like to pull a value, say
D21, out of UPS! without hard-coding UPS! into my formula.

I'd eventually like it to reference a column of different tickers and pull
out of an equal number of different worksheets, all named the values of the
tickers. I think it should be a simple syntax thing, something like
='value(B1)'!D$21$, if my reference is in cell B1. Is this possible?

I want to create a sorted list based on an input I enter. Here is the formula I am using:

=INDEX($S$5:$S$41,MATCH(H5,TEXT(N3,0)&"$5:"&TEXT(N3,0)&"$41",FALSE),1)

The value of N3 is X

Therefore, I would expect it to treat this formula as:

=INDEX($S$5:$S$41,MATCH(H5,X$5:X&$41,FALSE),1)

Instead, I get a #VALUE! error

Is it possible to use a cell reference as part of another cell reference with the MATCH function?

Hello,

I would like to set the sheet unprotect VBA formula to use a cell value as the password to unprotect...purpose of this is I want to be able to edit that cell at anytime to update all instances of the "unprotect" password that are used over dozens of macros....

I was trying,

----------
Sub click()
Dim SH As Worksheet
Const PWORD As String = ("Sheet1!b1")
Worksheets("Sheet1").Protect Password = PWORD

End Sub
--------

But when it locks, I can not unlock it using the value in B1....

Any ideas?

I have a list of names stored in a worksheet and I want to use these to
define a set of variables.

I there anyway to do this

I tried this

Dim Sheets("names").Cells(1,1).Value As String

but with no luck

Ian B

Greetings all -
I have a workbook that consists of a summary sheet, and several worksheets
which contain monthly data. In the summary worksheet, I have the names of the
other worksheets in column A.

I want to use the value of a cell (ie: the worksheet name) in a formula to
reference the monthly sheet. Ex:

Summary sheet
A__________B___C__D__E
1 Sheetname Jan Feb Mar Apr
2 Sheet1
3 Sheet2
4 Sheet3

Assume the target data in Sheet1!D4 is the value 45.

Now:
In cell B2 of this Summary Sheet, I want to enter a formula that combines
the name of the worksheet in ColA with the referene to the target cell (D4),
and then resolves to the value of the cell in Sheet1D4, ie: 45.

I've tried a direct reference:
=A2!D4 - but that doesn't work - Excel prompts me to "Update Value" and
opens a file dialog box.

Tried various combinations of CONCATENATE, such as:
=&B2&'!'&D4 (and variations on that theme)
....but can't seem to make this approach work either.

I'm sure this is a simple syntax thing, but I'm beating my head against a
wall trying to guess what it is - any ideas would be appreciated!

Best,
S2

Greetings all -
I have a workbook that consists of a summary sheet, and several worksheets
which contain monthly data. In the summary worksheet, I have the names of the
other worksheets in column A.

I want to use the value of a cell (ie: the worksheet name) in a formula to
reference the monthly sheet. Ex:

Summary sheet
A__________B___C__D__E
1 Sheetname Jan Feb Mar Apr
2 Sheet1
3 Sheet2
4 Sheet3

Assume the target data in Sheet1!D4 is the value 45.

Now:
In cell B2 of this Summary Sheet, I want to enter a formula that combines
the name of the worksheet in ColA with the referene to the target cell (D4),
and then resolves to the value of the cell in Sheet1D4, ie: 45.

I've tried a direct reference:
=A2!D4 - but that doesn't work - Excel prompts me to "Update Value" and
opens a file dialog box.

Tried various combinations of CONCATENATE, such as:
=&B2&'!'&D4 (and variations on that theme)
....but can't seem to make this approach work either.

I'm sure this is a simple syntax thing, but I'm beating my head against a
wall trying to guess what it is - any ideas would be appreciated!

Best,
S2

I want to set up a workbook with several values from linked workbooks. I
would like to enter a value in column A and then use that value as the
filename in the rest of the cells that link to the external workbook.
For example:

I want to enter A122 into colum A - and have the cell in column B pick that
value up and link to a cell in an external workbook with that name

Obviously, the following function links correctly to the external file and
returns the correct value:
='G:Local filesSample Tracking[A122.xls]Sample Request'!$E$2

But, I do not want to have to change the syntax in a large number of cells
everytime I add to the list. I only want to enter the value (A122) in the
first cell and have the others retrieve linked values correctly.
In other words, I want a dynamic, external link.

Thanx in advance for any help.

I was wondering if it is possible to use a value that the user inputs into a cell, in a filename for linking to another spreadsheet in VBA.

For example, if the user enters "C-11838" in cell B4, I want to be able to set the forumla in C4 to ='K:GMGNets on FloorFinished Nets[C-11838.xlsm]Net Summary'!$B$4

I can't figure out how to set it up so that the number before .xlsm can be a variable.

Any help would be greatly appreciated!

I am trying to create a worksheet that consolidates data from a set of other
worksheets. The other sheets are all formatted identically. What I am having
difficulty with is using the name of the other tabs in my formulas in the
summary tab. I have the name of the tab in the a cell but can't figure out
how to use that cell value as a worksheet reference. For example, in the
summary tab, A2="Source1" but I get an invalid formula error when I enter
this into B2: =(a2)!b17. Any help would be appreciated...

How to use a cell that can have both a formula or a directly entered value. Specifically I'm trying to have one first cell to enter an english unit of measure and the metric formula conversion in a second cell; while also being able to enter a metric value in the second cell and convert to english units in the first cell. This requires each of the two cells to have either a directly entered value or a formula, perhaps using some sort of switching mechanism.

I am trying to create a worksheet that consolidates data from a set of other
worksheets. The other sheets are all formatted identically. What I am having
difficulty with is using the name of the other tabs in my formulas in the
summary tab. I have the name of the tab in the a cell but can't figure out
how to use that cell value as a worksheet reference. For example, in the
summary tab, A2="Source1" but I get an invalid formula error when I enter
this into B2: =(a2)!b17. Any help would be appreciated...

I am trying to use a cell value in a vlookup command in order to perform lookups on several documents without re-typing / editing the command. The cell value I want to insert in the command string is a document name (the data to examine is in separate documents and not in the destination document.

If I use a straightforward vlookup command in 'Document 1' to examine data in 'Doc2' I would have something like this:

=VLOOKUP(C1,'[Doc2]Worksheet1'!A$1:H$31,1,FALSE)

As I want to perform this operation for several documents of the same format I want to insert the 'Doc2' name in cell A1 in 'Document 1', so theoretically like:

=VLOOKUP(C1,'[Value from current document cell A1]Worksheet1'!A$1:H$31,1,FALSE)

I have tried various ways to achieve this but am failing each time. Is it possible to use the command in such a way as to achieve the desired result?

Any help appreciated.

Is it possible to use a cell reference (on the same worksheet as the
vlookup function) as the table array argument in the vlookup function
in place of an explicit table array argument?

I'm trying to build a simple reporting tool that would allow me to
specify the file location, file name, worksheet name and array, and
then have the vlookup function recognize these arguments as the "table
array".

In other words, Here's the typical vlookup:

VLOOKUP(D10,Sheet2!$A$1:$P$100,5,FALSE)

What I want to do is to be able to replace the "Sheet2!$A$!:$P$100"
part of the formula with reference to a single cell on the same sheet
as the Vlookup formula. This cell would contain the information on the
table array to use (using the concatenate formula or something
equivalent).

The situation is that I have several different versions of a very large
file with 50+ tabs. I need to be able to specify the file name and the
tab name in order to extract data from these files quickly. Something
more elegant than doing a find & replace each time.

Thanks in advance!

Hi All

I have a command text line

" .CommandText = Array("exec XXXX_sp [Need Cell Text Here]")

I need to insert the text content of cell A1 from the spreadsheet in [Need Cell Text Here]

Any ideas appreciated

Regards
Alison

Hello,

I need to make a cell value generate a cell value in another cell.

Example.

If cell A1 contains "Apple" I want B1 to have the value
"fruit". If I write beatles in A1 I want B1 to spell out
"music"

I have a list of two columns
Apple fruit
Strawberry berries
Saw hardware
Beatles music
..
..

My original solution to this problem was to use the if-function in B2
like this

=IF(A1="Apple", "fruit", IF(A1="Strawberry", "berries"
IF(A1="Saw", "hardware",....))))

This may not be very elegant but it worked until I had a list that was
longer then 7. Apparently Excel can't handle more then 7
if-functions.

There should be an easier way to accomplish this. I could probably
write the If-functions directly into the macro that I'm building but
I would appreciate a easier solution. Perhaps something containing the
VLOOKUP-function?

Hi all,

An earlier thread(help required with using cells to generate a filename) nearly helped but not quite.

I want to use a cell value as a filename with the workbook to be saved in a specific folder but I want the Dialog box to open first with the filename in it so the user has to actually click on Save As.

So far, so bad: what I have got doesn’t work at all and I’d be very grateful for any help at all

Many thanks in advance,

DS

Is it possible to use a cell reference (on the same worksheet as the
vlookup function) as the table array argument in the vlookup function
in place of an explicit table array argument?

I'm trying to build a simple reporting tool that would allow me to
specify the file location, file name, worksheet name and array, and
then have the vlookup function recognize these arguments as the "table
array".

In other words, Here's the typical vlookup:

VLOOKUP(D10,Sheet2!$A$1:$P$100,5,FALSE)

What I want to do is to be able to replace the "Sheet2!$A$!:$P$100"
part of the formula with reference to a single cell on the same sheet
as the Vlookup formula. This cell would contain the information on the
table array to use (using the concatenate formula or something
equivalent).

The situation is that I have several different versions of a very large
file with 50+ tabs. I need to be able to specify the file name and the
tab name in order to extract data from these files quickly. Something
more elegant than doing a find & replace each time.

Thanks in advance!

Hello,

I need to make a cell value generate a cell value in another cell.

Example.

If cell A1 contains "Apple" I want B1 to have the value
"fruit". If I write beatles in A1 I want B1 to spell out
"music"

I have a list of two columns
Apple fruit
Strawberry berries
Saw hardware
Beatles music
..
..

My original solution to this problem was to use the if-function in B2
like this

=IF(A1="Apple", "fruit", IF(A1="Strawberry", "berries"
IF(A1="Saw", "hardware",....))))

This may not be very elegant but it worked until I had a list that was
longer then 7. Apparently Excel can't handle more then 7
if-functions.

There should be an easier way to accomplish this. I could probably
write the If-functions directly into the macro that I'm building but
I would appreciate a easier solution. Perhaps something containing the
VLOOKUP-function?

HELP! HELP! HELP!

I have a formula that I would like to reference a cell value to create a range. See example below:

I have 2 sheets: "E-mail" and "2010".

E-mail Sheet:
..........A...................B.....................C
1.....970.....=SUM('2010'!B2:B8).........2
2.....975.....=SUM('2010'!C2:C8).........8
3.....989.....=SUM('2010'!D2:D8)
4.....999.....=SUM('2010'!E2:E8)

2010 Sheet:
..........A...........B........C........D........E
1.....1/1/10......970.....975.....989.....999
2.....1/2/10......123.....222.....331.....441
3.....1/3/10......124.....223.....332.....442
4.....1/4/10......125.....224.....333.....443
5.....1/5/10......126.....225.....334.....444
6.....1/6/10......127.....226.....335.....445
7.....1/7/10......128.....227.....336.....446
8.....1/8/10......129.....228.....337.....447
9.....1/9/10......130.....229.....338.....448
10...1/10/10....131.....230.....339.....449

My objective is to be able to change the values in C1 & C2 and have the calculations in B1 through B4 update based on the values I enter. For example, if I change C2 in the "E-mail" sheet to 9, I would want the formula in B1 to change to "=SUM('2010'!B2:B9)", B2 to change to "=SUM('2010'!C2:C9)" and so forth...

Any help would be GREATLY appreciated!!!

Can it be possible to use sheet cell values as captions (lables) in user forms

Hi there,

I have a list of numbers for example 1-10 in column A. I want to use those value in column c (for example) as a row reference. Something like = b(ro w(a1)), this would return the value of b1 using the values from A as a reference.

A B C
1 2 2
2 3 3
3 4 4
4 5 5
5 6 6
6 7 7
7 8 8
8 9 9
9 10 10

This is not exactly the type of thing that I want to do, it is just a simple example of what I would like to do. I can't seem to figure out how to use a value as part of the reference in another cell.

Thanks for any help,

TJ

hi,
I want to use a cell in a worksheet to control a loop counter

cell A1 in worksheet `Test `= x
I also want to show the value of x as it increase / decrease on a
worksheet ( i beleive I use print but not quite sure how)

how do i display the cell link(my intial value of x) inside my macro

how do I show link to display the decrease or increase of the value of x
inside a worksheet
any assistance appreciated

My spreadsheet contains a list of names, with an auto-filter that allows me to choose one of the names in order to enquire on further data. I also have a cell on another worksheet that validates a list and permits the user to choose one of the names from the list. This cell is obviously dynamic (changes) because the user could choose any of the names from the list.

I need the value of the 'list' cell to be copied and used as the basis for determining the value on the filter cell. i.e. I need the user to choose a name from a list, and have this name used as the 'name = xxxx' custom filter.

I am attempting to do this by means of a macro; however, the value returned to the filter is now dynamic. The macro copies the value of the 'source' list cell (eg abcd) and pastes this value into the 'target' custom filter. If I then change the value in the 'source' list cell to 'wxyz' and re-run the macro, I find the value 'abcd' is still copied to the 'target' custom filter instead of the new value of the 'source' list.

I have tried using relative cell references as well, all without success.

Is there any way to make the value that I copy into the 'target' custom filter dynamic? The attached example spreadsheet contains a short example macro.


No luck finding an answer? You could always try Google.