Free Microsoft Excel 2013 Quick Reference

Reference a Cell for Tab name in VLOOKUP Table Array

Hello all -

I am trying to do the following:

Sheet 1 contains:

Item Sheet 2
ABC =vlookup(A2,'Sheet 2'!$A:$B,2,false)

Sheet 2 contains:

Item Data
ABC 2

I'm trying to get the vlookup to return the value "2"

Right now, I'm manually entering the tab name in the vlookup function, even though it's contained in cell B1.

The tabs are contained in the same workbook if that matters. Since this workbook is growing rather quickly, this is a painful process and doesn't feel very scalable. Since I'm using a mac, I need to do this with functions vs. macros. Does anybody know how I can reference a cell for the name of a tab in the vlookup function?

I was thinking I could maybe somehow do this with the INDIRECT function but I'm stumped.

I apologize if this wasn't clear but it seems to be a slightly convoluted question.

Thanks in advance for the help.


Is their a way to reference a cell thats value changed in an if statement.

What I want to do is every time a cells value changes in a certain row I want to set the value of a cell adjacent to it to a certain formula. Somthing like

CellThatsValueChanged.Offset(0,1) = Formula

Working in Excel 2003. I would like Excel to search a cell for text contained in the formula for the link to that cell, and then return "y" for TRUE. Right now I have figured out only how to search for text in the cell, not in the formula for the link.

For example, the link in cell B2 is "'[Sales]New Starts'!B6" but the text of cell B2 is "John" (the text of the cell the link points to). I would like to search cell B2 for "New Starts" and return a "y" in cell g2 if TRUE, and leave the cell G2 blank if FALSE.

I have tried =IF(ISNUMBER(SEARCH but it works only for the cell text.

Thank you so much for your help!

I am attempting to simplifiy some template files I created for other
non-Excel users. I would like to make it easier to have users update
formulas, by having the formulas reference a cell for the tab or file
to pull data from. Eample formula:
COUNTIF(*_'DATAFILE.XLS'_*!$J$2:$J$8000,C4). I would like to repalce
DATAFILE.XLS to reference a cell vaule. This way all formulas would
ppoint to one cell where the user would type in the name of the file to
use.

Hope this makes sense....

Anyone ever try this?

Thanks!

--
mgarcia
------------------------------------------------------------------------
mgarcia's Profile: http://www.excelforum.com/member.php...o&userid=30584
View this thread: http://www.excelforum.com/showthread...hreadid=502342

I am attempting to simplifiy some template files I created for other non-Excel users. I would like to make it easier to have users update formulas, by having the formulas reference a cell for the tab or file to pull data from. Eample formula: COUNTIF('DATAFILE.XLS'!$J$2:$J$8000,C4). I would like to repalce DATAFILE.XLS to reference a cell vaule. This way all formulas would ppoint to one cell where the user would type in the name of the file to use.

Hope this makes sense....

Anyone ever try this?

Thanks!

Hi,

I the attached file I have VLOOKUP formula which looks up customer number on sheets List_A and List_B and returns the customer name. Instead of writing the sheets names "List_A" or "List_B" in the formula I need to make a reference to the worksheet name in column A. Is it possible to do this?

Thank you in advance.

Hi Excelling,

Look on Chip Pearson's formula page under Miscellaneous for an example of
how to do this:

http://www.cpearson.com/excel/excelF.htm

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Excelling in MN wrote:
> Version: Microsoft Office Excel 2003
>
> How can I repeat a sheet's tab name in a cell?
>
> I have a summary sheet which has data fed from multiple other
> supporting sheets. The supporting sheets are updated by other users
> and they modify the tab name so it reflects their specific subproject
> effort.
>
> Just as the summary sheet reflects the supporting sheet data, it
> would be very helpful to state the tab name so it is clear what the
> data is representing (i.e.- the subproject sheet name). I wish to
> create a link so if the subproject (tab) name is changed, the summary
> sheet is automatically updated.
>
> I had hoped this would be similar to adding the file name and path in
> a cell: =cell("filename") however, it is not.

Version: Microsoft Office Excel 2003

How can I repeat a sheet's tab name in a cell?

I have a summary sheet which has data fed from multiple other supporting
sheets. The supporting sheets are updated by other users and they modify the
tab name so it reflects their specific subproject effort.

Just as the summary sheet reflects the supporting sheet data, it would be
very helpful to state the tab name so it is clear what the data is
representing (i.e.- the subproject sheet name). I wish to create a link so
if the subproject (tab) name is changed, the summary sheet is automatically
updated.

I had hoped this would be similar to adding the file name and path in a
cell: =cell("filename") however, it is not.

I am trying to reference a cell with worksheet names which is populated from
another formula.
Once I have the worksheet name I am then trying to use it in a formula as
follows to be used for a dynamic chart. I posted in the Charts forum but got
no answer. Is this possible and if so am I on the right track?
this would be used for XValues

=OFFSET((INDIRECT("’"& M2 &"' !$B$4")),0,0,COUNTA(INDIRECT("’"& M2 &"'
!$B:$C”))-1,2)

Thanks for any assistance

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

I am using SQL in Microsoft Query to import data from a ODBC data source
into Excel. I am linking mutiple tables from the source and this seems
to prevent from being able to reference a cell in the spreadsheet as a
parameter value. The import works when I 'hard-code' the date in the
code, but I would like to be able to reference a cell in the spreadsheet
so the end user can refresh the data after just changing the date in the
spreadsheet. Here's a portion of my code:

SELECT trim(gltrans.ref), GlTrans.Value
FROM GlTrans INNER JOIN SYSFIL
ON (GlTrans.SUB_ACCT = SYSFIL.DB_SUB_ACCT)
AND (GlTrans.ACCT = SYSFIL.DB_ACCT)
AND (GlTrans.DEPT = SYSFIL.DB_DEPT)
AND (GlTrans.BRANCH = SYSFIL.DB_BRANCH)
AND (GlTrans.COY = SYSFIL.DB_COY)
WHERE GlTrans.Period_Date < '2006-06-01' AND
(SYSFIL.Module_Type='VHS' AND SYSFIL.Acc_des='RT') AND
GlTrans.Year_End_Date =
(SELECT current_year_end_date FROM company WHERE coy = (SELECT db_coy
FROM sysfil WHERE acc_des = 'debtor'));

What I would like to do is replace the '2006-06-01' date with the date
supplied in cell C1 in my spreadsheet. I do have the date formatted
correctly in the cell.

I have searched for resolutions on the Internet, trying several
examples, but have not found the correct resolution yet. Any help is
appreciated.
Brian
Business Analyst
JDIS

*** Sent via Developersdex http://www.developersdex.com ***

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 ?

Version: Microsoft Office Excel 2003

How can I repeat a sheet's tab name in a cell?

I have a summary sheet which has data fed from multiple other supporting
sheets. The supporting sheets are updated by other users and they modify the
tab name so it reflects their specific subproject effort.

Just as the summary sheet reflects the supporting sheet data, it would be
very helpful to state the tab name so it is clear what the data is
representing (i.e.- the subproject sheet name). I wish to create a link so
if the subproject (tab) name is changed, the summary sheet is automatically
updated.

I had hoped this would be similar to adding the file name and path in a
cell: =cell("filename") however, it is not.

I am using SQL in Microsoft Query to import data from a ODBC data source
into Excel. I am linking mutiple tables from the source and this seems
to prevent from being able to reference a cell in the spreadsheet as a
parameter value. The import works when I 'hard-code' the date in the
code, but I would like to be able to reference a cell in the spreadsheet
so the end user can refresh the data after just changing the date in the
spreadsheet. Here's a portion of my code:

SELECT trim(gltrans.ref), GlTrans.Value
FROM GlTrans INNER JOIN SYSFIL
ON (GlTrans.SUB_ACCT = SYSFIL.DB_SUB_ACCT)
AND (GlTrans.ACCT = SYSFIL.DB_ACCT)
AND (GlTrans.DEPT = SYSFIL.DB_DEPT)
AND (GlTrans.BRANCH = SYSFIL.DB_BRANCH)
AND (GlTrans.COY = SYSFIL.DB_COY)
WHERE GlTrans.Period_Date < '2006-06-01' AND
(SYSFIL.Module_Type='VHS' AND SYSFIL.Acc_des='RT') AND
GlTrans.Year_End_Date =
(SELECT current_year_end_date FROM company WHERE coy = (SELECT db_coy
FROM sysfil WHERE acc_des = 'debtor'));

What I would like to do is replace the '2006-06-01' date with the date
supplied in cell C1 in my spreadsheet. I do have the date formatted
correctly in the cell.

I have searched for resolutions on the Internet, trying several
examples, but have not found the correct resolution yet. Any help is
appreciated.
Brian
Business Analyst
JDIS

*** Sent via Developersdex http://www.developersdex.com ***

Is it possible to create a macro using goal seek that will automatically
reference a cell in the worksheet for the "To value" field, rather than
having to enter a value? I've tried copying/pasting a value into this field
while recording a macro, but the dialog box doesn't allow that.

My goal is to allow a user to enter a target value (retail price, in this
case), then have a macro activate solver to find the minimum number of units.

Any ideas?

Hi all,

was wondering if anyone could tell me how to automatically generate a list of tab names, possibly using VBA that also sorts the list either by...

(i) the order of which the list of tabs appears in the summary sheet or
(ii) alphabetically

I would appreciate any help and assistance. Is probably an easy query for you guys but I am 7 days into using VBA and learning lots with all your kind help!!!

Is there a way to use a variable for the table_array in a VLOOKUP, specifically, where the table_array is in another workbook? I'm not afraid to use a VBA function if necessary but it'd be nice to have the function all 'self-contained' using standard formula commands.

This is an example of what I'm looking to do:
Since a string is returned, this function fails.  I'm guessing I need a Range returned?

The "CONCATENATE("..." part is the piece causing me the issue. Depending upon what Form (radio) button the user clicks (that part is done), the table_array range will change.

Suggestions?

How to format a cell to display numbers in lakhs with a cama separation
instead of millions

I have a table with a name column in it (among others).
There are multiple records for each name and they all have
different numbers of records.

I need to pull a random sample of 10 records for each name
in the table and put them into a spreadsheet. How do I go
about this query/queries to accomplish this?

When I tried to do it, I could come up with a random
sampling of the whole table, but I couldn't divide it up
to give me 10 of each name.

Thanks for any help you can provide.

Hello all,

I have two separate workbooks with an equal number of worksheets. In workbook 1, each sheet has a person's name (John Doe, Susan Smith, etc.) in cell c6. In workbook 2, each sheet is named after one of those people. That is, workbook 2 has worksheet tabs John Doe, Susan Smith, etc. The worksheets in workbook 1 do not have cooresponding names, but they could have if it made the solution simpler.

I want to perform the same set of calculations on each sheet in workbook 1, by including in the formulas the related data from workbook 2, based on the match between the name in cell c6 of each sheet in workbook 1 and the tab name in workbook 2.

I would prefer a link formula, but would be willing to use a macro. I do not think I need anyone to write the macro for me. Whether link or macro, I only need to know how, if possible, the reference is coded.

I don't know if what follows will help or confuse, but essentially, I want to incorporate a set of calculations into each sheet of workbook 1 of the following nature:

content of wb1 sheet 1 cell m20: = value of wb1 sheet 1 cell m7 * value of wb2 cell a20 on sheetname with value of wb1 sheet 1 cell c6.
content of wb1 sheet 1 cell m21: = value of wb1 sheet 1 cell n7 * value of wb2 cell a21 on sheetname with value of wb1 sheet 1 cell c6.
content of wb1 sheet 1 cell m22: = value of wb1 sheet 1 cell o7 * value of wb2 cell a22 on sheetname with value of wb1 sheet 1 cell c6.

content of wb1 sheet 2 cell m20: = value of wb1 sheet 2 cell m7 * value of wb2 cell a20 on sheetname with value of wb1 sheet 2 cell c6.
content of wb1 sheet 2 cell m21: = value of wb1 sheet 2 cell n7 * value of wb2 cell a21 on sheetname with value of wb1 sheet 2 cell c6.
content of wb1 sheet 2 cell m22: = value of wb1 sheet cell o7 * value of wb2 cell a22 on sheetname with value of wb1 sheet 2 cell c6.

Thanks in advance to anyone who can help.

Conor

I have a "CURRENT MONTH" tab in a workbook; in that Workbook, I have a standard report that pulls Total Revenues from cell C6 of the tab I want to reference.

For example: In my "CURRENT MONTH" tab B6 has the label "CURRENT MONTH REVENUES" C6 has the formula "='May 08'!C6"

Every month I have to change that formula to "='Jun 08'!C6" as an example for June. (it's not just one formula, there are dozens referencing May that I need to change to Jun).

I could do an Edit/Replace, but I'd rather just type in the current month tab name in another cell and have the formulas pulling the data referencing that cell.

Can anyone help???
THANKS IN ADVANCE!

What is the syntax for referencing a range name in a =vlookup formula? Instead of typing the range name in the formula - such as =vlookup(a5,table_1,5,false), where table_1 is one of 10 tables, I would like to reference a cell that contains the table name. This would permit me to look up data in various tables without having to retype the table name in =vlookup.

Is there a way to reference a sheet name as a variable from a cell?

For instance, I am trying to set up a way to color the sheet tab based on what name is input in say Cell A1. So if "sheet1" is selected (from a list of sheetnames), then sheet1 tab color would change to red. Is this possible?

How may I reference the current worksheet name (the name on the tab) in a
formula on that worksheet.

In other words, I want to compare the string in a cell to the name of the
current worksheet. If it matches then I want to raise an error.

Thanks in advance!

Janet

Have a worksheet with specific data (calls, $, employee) in the columns and
the weekly data (Week 1 Week 2 Week 3) for the specific data in the rows.
Each week I add new data to the next row and need to be able to sum or total
the data at the bottom (Year to Date information). Currently, I highlight the
area containing the year to date formulas and do a find/replace to advance
the row to the next one down with the new data for the current week.

Problem: Can I put a value in cell and have the sum or average formula
reference a cell the contains the row number that I would like to end on? I
have tried to use name function for the row, and the r1c1 format but can not
use a cell reference for the # in the r1c1 format. here is an example:
=sum(a1:a+currentweek) Current week is a named cell for the row with the
current weeks data.

I also tried in the r1c1 format =sum(R1C1:R'currentweek'C1) In this
example current week would a cell holding the value 10 next week it would
hold the value 11 and so on.

I have several formula that very with the week. So if there is any easy way
to reference a cell containing the a vaule to be place in the cell reference
for a row would be a big help. Otherwise, find repace of the hightlighted
area is the quickest way I know to do this.

Thanks,

David