Passing a worksheet name and cell address, and retreive the value of the worksheet's cell

My bad. I thought this example was so simple that I didn't need to follow protocol by posting the file. I can see that I was wrong and apologize.

You will see my problem if you open the attached file, go do the 'Repeating Risks' worksheet and look at C21. You will find the function there and it evaluates to #VALUE!.
Instead, I want the function to return the value of C7 from the worksheet 'Remove Identical'.

C7 doesn't have a cell address in it. If you want the value from C7 on the other sheet, then you should use "C7" in the formula, not C7. Also, I don't know why you wouldn't just use INDIRECT. Also, since you cannot pass a worksheet object from a cell (you're just passing the name), it needs to be:

Please Login or Register  to view this content.

Yeah . . . you're missing a lot. I apologize for not knowing how to explain this more succintly, but you did ask . . . so here goes.

I've developed a method to show whether the benefit or risk of a healthcare process is greater. Fortunately, my process avoids the problems inherent in representing risk or benefit by numbers. I represent one risk with one ordered pair of strings of text, where the two parts of the ordered pair are text strings that describe the probability of a risk occurring and the severity of a risk occurring. I then represent the overall risk by the set of all of the individual risks. And, I've worked out four operations that can be used to simplify two sets of risks (one set representing the patient's risk before the healthcare proces and one set representing the risk after the healthcare process) until it is intuitively clear which risk is greater.

Even though what I've developed is a significant improvement over existing methods (most of whom fail right out of the gate because they use numbers to represent risk), a whole lot of people are going to freak out about the prospect of simplifying an equation with two sets whose elements are ordered pairs of text. To demystfy this process, I've created a worksheet that implements each of the four operations. I'm hoping this will make clear to people that each operation is logical and intuitively correct.

Once people get comfortable with having a spreadsheet perform one simplification operaion, I plan to copy one of the four spreadsheets to performs a simplificaiton operation. And I plan to repeat this 10 to 50 worksheets in order to simplify an initially quite complex equation of risks until one set is empty and the other is not. The empty set will have the lower risk.

The difficulty is that the order of operations will change when the two sets of risks change. So, I need a way of applying operations in whatever order people want, using tools that most people who are familiar with Excel can do. This requirment knocks out doing this in VBA, but most people can create and copy worksheets.



I can finally answer your question:
These worksheets will have formulas in them and each worksheet will get its input from the last, previous worksheet. The cells in the worksheet copies will never change, but the cells that each new worksheet will always reference cells from the last previous worksheet.
The point of this function is to enable me to enter, in just one place in a new worksheet, the name of the previous worksheet and have all of the formulas in the new worksheet point to the last previous worksheet instead of whichever worksheet it used to point to. There could be 150 references to a previous worksheet, so doing this by hand is out of the question. i need a function that looks at a single cell for the worksheet name that all of its cells should point to and updates all of the formulas with this worksheet name.

As for why didn't I use the 'indirect' command: Perhaps I should have. I'm only slightly familiar with that command. Attached is the file containing my latest attempts, including with the indirect command.

Again, if D7 is the cell you want the value from, you need to pass its address "D7", not to pass it as a reference (which will mean you are passing the contents of D7 - i.e. 1. Also that code is nothing like the code I posted.

In any event, all you need is:

=INDIRECT("'"&F2&"'!D7")

No code required.

Thank you all for your persistance.
Because of the target audience, I was trying to avoid the use of helper cells, but, because I can't get the INDIRECT command or anything else to to work inside a function, I'll need 70 helper cells per worksheet.
I've been stuck long enough so I'll bite the bullet and just create a block of hidden helper cells off to the side.