Refering to cell value within a worksheet name within a form
Refering to cell value within a worksheet name within a form
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Refering to cell value within a worksheet name within a form

  1. #1
    Guest

    Default

     
    Hello,

    I would like to refer to a cell's string value within a reference to another worksheet. A specifical example would be...

    Say Cell A1 has the text "Test1" and there is a worksheet in the same workbook that has the name "Test1".

    I would like to have the following function:

    =MAX('Test1'!D:D)

    However, I would like to refer to the value in cell A1 rather than typing 'Test1' in the formula. Does this make sense? Let me know if you have any questions.

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's one thought, and it gives you the flexibility to change functions, sheets, and ranges without needing a UDF each time.

    Let's say in A1 you enter the sheet name, Test1 in your example.
    In B1 you enter the function name, MAX in your example.
    In C1 you enter the range D:D in your example.

    This macro will return the result you want, and you can assign a keyboard shortcut to it, so it'll work in any active cell. Then, if you want to find the MIN of E:E on sheet Test2, you just need to change the cell values in A1:C1.

    Sub FormYouLa()
    ActiveCell = "=" & [B1] & "(" & [A1] & "!" & [C1] & ")"
    End Sub

    I'm sure there are other ways to approach this, and maybe this is a bit off the beaten path...just a thought for something different on a Friday night.

    Tom Urtis

  3. #3
    Guest

    Default

    I like the suggestion. I added the two single quotes to the macro below. Is there some way I can make it refer to a "relative" cell? That is to say, I want the macro to do something different depending on what the active cell is. Using my example, if Im in row one I want it to look at A1 so that it will look at worksheet 'Test1'. If Im in row 2 I want it to look at A2 where it would say 'Test2' and therefore refer to that cell. Make sense?

    Thank you for your input!

    Sub FormYouLa()
    ActiveCell = "=" & [B1] & "('" & [A1] & "'!" & [C1] & ")"
    End Sub

  4. #4
    Guest

    Default

      
    Ok,

    I figured out how to do what I was saying in my last post using the "Offset" property (see below). My last question is this - Is there a way to make the macro run on a range of cells? Ie - I want the max to be filled in on all cells for which there is data in the corresponding offset rows, without having to run the macro on each individual row. Thank you so much for your input!

    Sub FormYouLa()
    ActiveCell = "=" & ActiveCell.Offset(0, -2) & "('" & ActiveCell.Offset(0, -3) & "'!" & ActiveCell.Offset(0, -1) & ")"
    End Sub

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com