Solved Excel 2010 - How To Reference Sheet 2

February 9, 2011 at 07:45:27
Specs: Windows Vista
I have two Excel sheets within a workbook and I would like to be able to enter an x in a cell and automatically have it reference a number on sheet 2. What would I use?

See More: Excel 2010 - How To Reference Sheet 2

Report •

#1
February 9, 2011 at 08:04:19
What do you mean by "have it reference a number on sheet 2"?

Keep in mind that we can't see your spreadsheet from where we're sitting, so you need to be fairly specific in the description of what you are trying to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 9, 2011 at 08:12:12
So sorry about that. Say cell C2 (sheet 2) has the number 100 in it. I would like to be able to put an x in cell A2 (sheet 1) and have it return "100".

Report •

#3
February 9, 2011 at 08:44:29
✔ Best Answer
Unless you are willing to use VBA you can not manually enter a value in a cell and have that value change. You could put the x in a different cell, and use an IF statement, e.g. in B2 you could use:

=IF(A2 = "x", Sheet2!C2, "")

B2 would show the value from Sheet2!C2 only when there was an x in A2.

Your other option is VBA:

Right click the sheet tab for the sheet you want this to happen in and paste this code into the pane that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$2" Then
  If Target = "x" Then
     Target = Sheets(2).Range("C2")
  End If
 End If
End Sub

When you enter an x in A2, it will be changed to the value in Sheet2!C2.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 9, 2011 at 09:07:46
Thank you. I'm getting a debug error when I paste this code in.

Run-Time error '424': Object required

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$6" Then
If Target = "x" Then
Target = FoodList.Range("A8")
End If
End If
End Sub


Report •

#5
February 9, 2011 at 09:39:14
Target = FoodList.Range("A8")

should be...

Target = Sheets("FoodList").Range("A8")

VBA doesn't know anything about the "objects" within your workbook unless you specifically tell it what they are.

In other words, it has no idea that FoodList is a sheet unless you tell it that it's a sheet, just like it wouldn't know that A8 is a Range unless you tell it that it's a Range.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
February 9, 2011 at 10:17:39
I see, thank you very much. Another question if you have time, I just did that for cell B6, now I want to do it for C6-G6. Do I just copy the whole thing, paste it again and change it for each cell?

Report •

#7
February 9, 2011 at 11:04:46
A worksheet can only have one Worksheet_Change in its module, not because it can't monitor more than one change, but because the name (Worksheet_Change) must remain the same and you can't have more than one macro with the same name in the same module.

Therefore, you have to use one Worksheet_Change macro and simply include everything that you want to monitor in that one piece of code.

That said, your requirement is not clear to me.

You said: "I want to do it for C6-G6"

I'll assume you mean that you want to look for an "x" in B6:G6. That part is clear.

What I'm missing is whether you want to return Sheets("FoodList").Range("A8") for an x in any of the cells or will each cell in B6:G6 have its own Range to return.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
February 9, 2011 at 11:22:14
Yes I would like to return Sheets("FoodList").Range("A8") for each cell in the range of B6:G6 if I enter an x in any of those cells. This is sort of what my sheet contains

Row 6 Titled Proteins
Row 7 Titled Dairy
Row 8 Titled Fruits

So for the 6 cells after "proteins" (B6:G6) if I enter an X in one of those it should populate 100 (calories) from Sheets("FoodList").Range("A8"). If possible I would like to do the same for Row 7 "Dairy" B7:C7, if I enter an x in those it will populate 120 (calories) from Sheets("FoodList").Range("A30").

If it isn't possible to do more than one, that's ok. Thank you very much for your time and knowledge, I appreciate it.


Report •

#9
February 9, 2011 at 11:42:33
It's possible to do just about anything you want, but as you may have guessed by now, you have to be very specific in the code.

Just like a formula in Excel which has to be very carefully constructed in order to return the correct results, so too does VBA code.

This version will get you A8 for any cell in B6:G6 and A30 for B7:C7. Replicating the structure and changing the ranges will allow you to do multiple ranges.

If you have many, many rows to check, or if you are returning the same value for multiple rows, there may be more efficient ways to accomplish your goal, but based on your examples, this is "good enough".

Private Sub Worksheet_Change(ByVal Target As Range)
'Is the Target cell within B6:G6?
  If Not Intersect(Target, Range("B6:G6")) Is Nothing Then
    If Target = "x" Then
     Target = Sheets("FoodList").Range("A8")
    End If
   End If
'Is the Target cell within B7:C7?
  If Not Intersect(Target, Range("B7:C7")) Is Nothing Then
    If Target = "x" Then
     Target = Sheets("FoodList").Range("A30")
    End If
   End If
'Is the Target cell within B8:G8?
'Replicate the above structure here, using the correct ranges.
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
February 9, 2011 at 12:00:48
WONDERFUL! Thank you. I find the VBA interesting do you recommend a book or class to learn it?

Report •

#11
February 9, 2011 at 12:17:51
People ask me that all the time, and I never have an answer.

Everything I've learned is by trial and error and copious use of the VBA Help files and Google.

If I can't answer a question off the top of my head, I Google around and then use the info I find and incorporate it into what I already know.

It's rare that somebody asks for something that is absolutely, totally different than anything that someone else, somewhere else hasn't ask for before.

The fun part is when I go searching for a solution and find my own answer to a similar question from years ago. :-)

Early on, I just hung around a few forums and read the Q&A's and played with the code given as answers. Practice makes per...errr...better.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
February 9, 2011 at 15:04:53
Cool, well thank you again and I'll certainly be learning this, there are so many options. I love when someone has an Excel mission for me.

Report •

Ask Question