Obtaining The Functions Cell

[BassMan449]

I'm trying to write a function that will do calculations based on other data in that cells row. Is there any way to figure out what Row the formula is located in? Everything I try is making everything come from the active sheet rather than the sheet the formula is on.

[MrShorty]

Can you provide more specifics? It's hard to make specific recommendations without specific problems.

My first thought was that you are trying to write a UDF to call from a spreadsheet cell. I would also guess that you are not passing arguments to the function via the argument list. If those assumptions are true, then my advice would be to structure the function to pass the desired cells through the argument list.

If those assumptions are incorrect, then please post details as to what your problem is.

[BassMan449]

The problem I have with passing the cell as an Argument is that I'm using this formula on many different worksheets and all of the worksheets are recalculated to the active worksheet rather than the worksheet they are on. There are dozens of sheets in these worksheets and it would be impractical to include the worksheet name in the arguments.

What I need is a way to obtain the information about the cell the formula is in.

Ex:
If I write "=LargeIncrease()" in cell A1 on sheet 1 Then the function would use the information in Cells A2:A5 on Sheet 1. What it is doing now is using the information in cells A2:A5 on whatever sheet is active.

[MrShorty]

I still don't see why you can't just pass the range to the function via the argument list.

If the function is always using data from the same worksheet, there should be no need to include the worksheet name in function call.

Can you better explain why you can't pass the range through the argument list? Perhaps post a sample of your code?

[BassMan449]

The problem is that I have many identically formatted sheets, but they have different data on them. If I pass the range as an argument, then every time it recalculates it does so based on whichever page I am looking at. I need it to calculate off the page the formula is on and not the page I am on.

[MrShorty]

How are you passing the range to the function? I've never had a UDF behave that way when I've passed the range to the function via the argument list.

[MrShorty]

Perhaps an example of what I'm expecting to see will facilitate the discussion. Here's a simple UDF:

Please Login or Register  to view this content.

This would be called from the spreadsheet as =udf1(a2:a5)

Done this way, this particular instance of the function will always use the values in A2:A5 as passed to the function, without regard to what sheet is currently active.

Is this the kind of scenario you are trying to describe (other than the details of the function obviously)? What are you doing differently?

[BassMan449]

I believe I got it solved. I really am not sure what I did, but some reworking of the code and now it appears to be working.

Thanks for your help