Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Using a cells contents to reference a sheet.

Is it possible to use the contents of a cell to reference a different
sheet?

Sheet 1 (Named Master)
Sheet 2 (Named Test)
Sheet 3 (Named Development)
Sheet 4 (Named Production)

I'd like to be able to perform a VLOOKUP on Sheets 2,3,& 4 using the
contents of a cell from sheet 1 as the array.

For Example:
Sheet 1, cell A1 would have one of the names of sheets 2,3,or 4.

=VLOOKUP(A5,*A1!*$A:$F,5,TRUE)

I know the above example won't work. Can it be done?

Thanks!

--
JoeM
------------------------------------------------------------------------
JoeM's Profile: http://www.hightechtalks.com/m491
View this thread: http://www.hightechtalks.com/t2307541


Post your answer or comment

comments powered by Disqus
Is it possible to use the contents of a cell to reference a different
sheet?

Sheet 1 (Named Master)
Sheet 2 (Named Test)
Sheet 3 (Named Development)
Sheet 4 (Named Production)

I'd like to be able to perform a VLOOKUP on Sheets 2,3,& 4 using the
contents of a cell from sheet 1 as the array.

For Example:
Sheet 1, cell A1 would have one of the names of sheets 2,3,or 4.

=VLOOKUP(A5,*A1!*$A:$F,5,TRUE)

I know the above example won't work. Can it be done?

Thanks!

--
JoeM
------------------------------------------------------------------------
JoeM's Profile: http://www.hightechtalks.com/m491
View this thread: http://www.hightechtalks.com/t2307541

Hi..I am new to this group. I am self learing how to code macros. Here
is my question

can I use a cell content as a Criteria1 reference in the below
Selection.AutoFilter statement?

Selection.AutoFilter
Selection.AutoFilter Field:=29, Criteria1:=Cells(29, 2)

I have a Workbook with about 20 worksheets (say, 'ABC', 'DEF', etc). I
also have a summary sheet within this workbook. So, *B1='ABC'*,
C1='DEF', etc. Column A is a list of all the information in my
worksheets. The rest is a tally of quantities of each row in each
worksheet.

I am performing an HLOOKUP of the info in Col A, and want to refer to
the worksheets in row 1 [=HLOOKUP(Cell A2,*ABC!*...]. My question is:
How can I use cell B1 (ABC), as part of my formula to reference my
worksheet, so when I drag my formula to column C, it will reference
cell C1 [DEF], and therefore, worksheet DEF? Directly using cells in
that function does not work [=HLOOKUP(Cell A2,*B1!*...] looks for sheet
B1, which does not exist...

Thank you.

Tim S.

--
timsantiago
------------------------------------------------------------------------
timsantiago's Profile: http://www.excelforum.com/member.php...o&userid=28656
View this thread: http://www.excelforum.com/showthread...hreadid=483242

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 have a Workbook with about 20 worksheets (say, 'ABC', 'DEF', etc). I also have a summary sheet within this workbook. So, B1='ABC', C1='DEF', etc. Column A is a list of all the information in my worksheets. The rest is a tally of quantities of each row in each worksheet.

I am performing an HLOOKUP of the info in Col A, and want to refer to the worksheets in row 1 [=HLOOKUP(Cell A2,ABC!...]. My question is: How can I use cell B1 (ABC), as part of my formula to reference my worksheet, so when I drag my formula to column C, it will reference cell C1 [DEF], and therefore, worksheet DEF? Directly using cells in that function does not work [=HLOOKUP(Cell A2,B1!...] looks for sheet B1, which does not exist...

Thank you.

Tim S.

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

Hiya, I'm trying to use a macro activated on a worksheet open that will copy row 1 from a sheet called "data" and paste it into row 1. (make them the same) I've sucessfully used the below to make the column colours the same but am not sure what to use to do the same for a cells contents!

Any help appreciated.

Thanks
hugh

Private Sub Worksheet_Activate()
[Bird!B:B].Interior.ColorIndex = [Data!B:B].Interior.ColorIndex

[Bird!1:1] = [Data!1:1]
End Sub


For example, if i have the value 5 in B1 and i want to use that value to reference a cell in column C, ie the formula for A1 is something like:

=C(B1)

obviously that doesn't work, but i hope you get the just of it.
Thanks!

any help on this? I would like to use the contents of a cell to auto-populate
the header. Thanks for any assit.

This should be simple but the functions that would appear to be the answer
don't work. I have a worksheet name that is contained in column one of
another worksheet row. I want to use that cell content to access the named
worksheet and populate a cell in the same row of the first worksheet. For
example, in worksheet A the content of cell A2 = B. I want to populate cell
B2 in worksheet A with the contents of cell B200 in worksheet B (the content
of A!A2). Then for other contents of column A of sheet A, Bx will be
populated with the contents of B200 of the sheet named in Ax where x is the
row number. The INDIRECT function does not appear to work in this case but
maybe I am formatting it wrong.

Thanks

This problem has been pondering on my mind.

Can you use a cell on one work sheet like an option to hide or unhide other work sheets.

Example, My work book contains sheets that should only be used in certain cases, so not to confuse the user, I was wondering is an cell can be used on the first worksheet to turn on/off a number of other worksheet..??

Gareth

Good evening,

I am having difficulty to get my code on the straight arrow. I believe my code writing is accurately preforming what I want it to do. I want to use a source file to copy a sheet into a existing workbook. My problem is that I'm changing the name of my active workbook to run the code because the filename will be a variable (last,first) depending on person. Can you please help me with this code

	VB:
	
 copyexam() 
    activesheet.Copy After:=Sheets(Sheets.Count) 
    ActiveWorkbook.SaveAs Filename:="processing" 
    activesheet.Name = "working" 
    Workbooks.Open Filename:="Users:matty:desktop:input.xlsx" 
    Application.EnableEvents = False 
    Workbooks("processing").Sheets("input").UsedRange.Clear 
    Workbooks("input.xlsx").Sheets("input").UsedRange.Copy _ 
    Workbooks("processing").Sheets("input").Range(Workbooks("input").Sheets("input").UsedRange.Cells(1).Address) 
    Application.EnableEvents = True 
    Windows("processing").Activate 
    ActiveWorkbook.Sheets("working").Select 
    activesheet.Name = Format(Date, "mmm-dd-yyyy") 
    ActiveWorkbook.SaveAs Filename:=Range("d3") 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi.

I have a spreadsheet which has 13 worksheets. One for each month and a calculation worksheet. The calculation worksheet has a column for each month. The calculations are identical from month to month with the exception of the worksheet being referenced. I currently hardcode the worksheet references.

=SUMIF(DEC!$B$14:$B$33,"<>CITI",DEC!$G$14:$G$33)

Every time I modify a formula I have to manually change it twelve times. I added a cell in row 1 of each column of the calculation worksheet to store the month value. Cell R1 contains DEC.

Is there a way to reference R1 rather than hardcode DEC in the above formula? This would allow me to copy updated formulas through the 12 columns and not have to manually change each one.

Thanks for your assistance.

Tony

Dear All,

I have been wondering about this for a while and seem to keep coming across the same problem.

Before i explain the problem i'd like to give you an idea of my level of experience in this area, i have some very ver very basic VB programming knowledge, i am aware what a macro is but have never written one and i have some basic knowledge on formulas but i would definatley put my experience in the novice category.

Now for the problem.

What i would like to do is import data from a particular cell in another workbook, sounds simple i know.

However i would like to import this data using the file and path name which is stored in another cell.

I have been sucessful in importing the data from another workbook but i have to type the pathname into every cell where i would like to use a syntax similar to the following.

=c4Sheet1B47 Where the value of c4 is "c:myworkbook.xls"

The reason for this is i am trying to make a simple summary sheet which draws its information from different workbooks. I will not be filling this in myself so i would like the end user to simple enter a client name and a file path and the worksheet imports the information from that file path.

e.g.

A B C D
1 Client1 C:Client1.xls Imported data Imported Data
2 Client2 C:documentsClient2.xls Imported data Imported Data
3 Client3 C:Old DocsClient2.xls Imported data Imported Data
4 Client4

Where Imported data is an imported cell value from the document specified in column B

Hopefully i have explained what i am trying to do in a roundabout way and any help would be greatly appreciated.

Oh one last thing i am using Excel 2007

Many Many thanks to anyone who helps.

In the following formula (from SHEET2!) rather than Index just the ANSI150
array, i want to Index an array named in another cell that might be anyone of
a number of arrays names. eg I have both a ANSI150 and ANSI300 array and i
want to return the result based on which one of these is in another cell -
say B18 on the SHEET2!

=IF(A18>0,INDEX('Gasket
Calculation.xls'!ANSI150,MATCH(C18,Sheet1!$A$6:$A$26,),MATCH("iD",Sheet1!$A$6:$H$6,)),"")

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

If I have a worksheet called "Sheet1", how can I make a cells contents in that sheet, e.g. cell A1 = "Sheet1" ?

Regards, Simon.

Hello,

How do I export a cells contents to a specific word file as a new line?
I also want to be able to label each line with a counter.

Say for example my cells value is "cell value" and my word file is
"destination.doc" I want to be able to do something like

counter = 1
Do until activecell.value = empty
{"destination.doc" value = counter &
activecell.value}
{new line so that next string isnt added to end of prev string in
word doc}

activecell.offset(1,0).select
Loop

Any ideas?

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

Hi, on "Sheet1" in cell A1 I have the string "Tony". I also have a
sheet named "Tony"
I want to display the value of cell A1 in the sheet "Tony" on
"Sheet1".
Instead of using =Tony!A1 I would like to somehow use the the value in
A1 ("Tony") to refer to the cell. I can then copy the formula to other
cells easily to reference other sheets..
Thanks

Hi, and thank you for your help.
I have run across this need many times...is there any way to use to contents of one cell, to construct a formula in another cell?

Here is my particular requirement. I have worksheets named January, February, March...etc.

On a separate Summary Worksheet, I reference the same cells on each of those sheets.

For example, I may have in 3 adjacent cells, =January!A1 =February!A1 =March!A1 and then in the next row: =January!A7 =February!A7 =March!A7 etc.

It would, obviously, be much easier to "build a formula" by creating a row with the months name and then reference that row in another formula. Is this possible? Or is there another way to accomplish this?

Thanks for your help.
Pradhan

I have a spreadsheet that performs vlookups from several other sheets. From
time to time new sheets are added and I would like to be able to change one
cell and have all the vlookups change to that sheet.

For example,

On sheet1 I have the following vlookups:

A10 =vlookup(D10,'My First Sheet'!A,4,false)
A11 =vlookup(D11,'My First Sheet'!A,4,false)
A12 =vlookup(D12,'My First Sheet'!A,4,false)
and so on...

I would like to have a cell on sheet1 that I can enter the name of the tab I
want to use. In the above case it would be "My First Sheet". If I changed
this cell to "My Second Sheet" I would want the above references lines to
become:

A10 =vlookup(D10,'My Second Sheet'!A,4,false)
A11 =vlookup(D11,'My Second Sheet'!A,4,false)
A12 =vlookup(D12,'My Second Sheet'!A,4,false)
and so on...

I have done this before using VBA, but I would like to do it without coding.

What I've tried is using the following:

A10 =vlookup(D10,"'" & $A$1 & "'!A",4,false)
A11 =vlookup(D11,,"'" & $A$1 & "'!A",4,false)
A12 =vlookup(D12,,"'" & $A$1 & "'!A",4,false)
and so on...

Where A1 would be where I type in a tab name.

This results in an error. Using the "Show Calculation Steps" tool I find
that the range reference in the vlookup still has quotation marks around it.
So instead of evaluating =vlookup(D10,'My Second Sheet'!A,4,false) it is
trying to evaluate =vlookup(D10,"'My Second Sheet'!A",4,false), which of
course doesn't work. Is there any way to remove the quotation marks, or any
other way of using a cell reference to a tab name in the vlookup function?

All help is much appreciated.

Mike

I have a spreadsheet that performs vlookups from several other sheets. From
time to time new sheets are added and I would like to be able to change one
cell and have all the vlookups change to that sheet.

For example,

On sheet1 I have the following vlookups:

A10 =vlookup(D10,'My First Sheet'!A:D,4,false)
A11 =vlookup(D11,'My First Sheet'!A:D,4,false)
A12 =vlookup(D12,'My First Sheet'!A:D,4,false)
and so on...

I would like to have a cell on sheet1 that I can enter the name of the tab I
want to use. In the above case it would be "My First Sheet". If I changed
this cell to "My Second Sheet" I would want the above references lines to
become:

A10 =vlookup(D10,'My Second Sheet'!A:D,4,false)
A11 =vlookup(D11,'My Second Sheet'!A:D,4,false)
A12 =vlookup(D12,'My Second Sheet'!A:D,4,false)
and so on...

I have done this before using VBA, but I would like to do it without coding.

What I've tried is using the following:

A10 =vlookup(D10,"'" & $A$1 & "'!A:D",4,false)
A11 =vlookup(D11,,"'" & $A$1 & "'!A:D",4,false)
A12 =vlookup(D12,,"'" & $A$1 & "'!A:D",4,false)
and so on...

Where A1 would be where I type in a tab name.

This results in an error. Using the "Show Calculation Steps" tool I find
that the range reference in the vlookup still has quotation marks around it.
So instead of evaluating =vlookup(D10,'My Second Sheet'!A:D,4,false) it is
trying to evaluate =vlookup(D10,"'My Second Sheet'!A:D",4,false), which of
course doesn't work. Is there any way to remove the quotation marks, or any
other way of using a cell reference to a tab name in the vlookup function?

All help is much appreciated.

Mike

Hi All

I want to use a simple vlookup function but want the function to use the contents of a cell in relation to which worksheet to look in...

e.g.

In worksheet 'Summary', Column A will hold 4.1 / 4.2 / 4.3 / 4.4 and so forth
I will have seperate worksheets named 4.1 / 4.2 / 4.3 / 4.4 and so forth

In worksheet 'Summary' in another cell I want to use =vlookup(1,4.1!A55:A70,2,0)
I want the worksheet name i.e. 4.1! to be dependant on what is in Cell A1 on the summary worksheet.

Is this possible please?

Thanks


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