Refering to cell value within a worksheet name within a form

[Guest]

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.

 

[Tom Urtis]

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

 

[Guest]

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

 

[Guest]

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