Pass cell address or text to formula/function? (Ex

Topic

New Reply

Peter,
I think at this point, you might as well just write a UDF to return the validation range – it will be a lot simpler than what you seem to be trying do do via formulae and defined names! (particularly if you need a UDF to make your formulae work!)
HTH

Reply | Quote

Replies

Hi Rory

Good.. I’m glad we agree…. so could you/anyone else give me a starter for 10 on writing such a UDF? I’m an Excel UDF ‘virgin’, though I have LOTS of programming experience in VB, Wordbasic, basic, COBOL, dBase/Clipper, Some others I’ve forgotton I knew, etc, etc.

Cheers
Peter

Reply | Quote

We can help write a UDF, but you will need to be specific on what you want/need

One problem with a UDF in validation is that it can not be used directly in a validation formula. You will have to have cells with the intermediate calcs of the custom function and have the validation read these cells. If you have a lot of validations, this is many intermediate calcuations….

Steve

Reply | Quote

Function GetRange(varRange As String) As Range
    Set GetRange = Application.Caller.Parent.Parent.Names(varRange).RefersToRange
End Function

Note that as Steve said, you can’t use this directly in DV. You can define a name as e.g. DataList and have it referto =GetRange(B2) or =GetRange(“Test1”) or whatever else you want. This will work with dynamic ranges too.
HTH

Reply | Quote

Thanks Rory..

I get a #NAME? error if I try to insert in a cell the command =GetRange(“lstWorksheet”).. Which I suspect is finger trouble on my part perhaps having put the UDF code in the wrong place? Just did Alt+F11, inserted code, closed and returned… Is there something I should be reading up to learn to do do this properly?

Also, I suspect I’ve confused everyone, myself included! I’m NOT actually looking to return a cell range that is refererred to via a Defined Name. I’m looking to return the contents of a specific cell in column 3 of the array/Defined Name tblLists. Although I use this formula to point at the correct cell:

=INDIRECT(INDEX(tblLists,MATCH(B2,INDEX(tblLists,0,1),0),3))

It’s actually the portion in red that needs reducing to a UDF, and the part in blue that I’d like to pass as a parameter.

Apologies for any confusion.

Cheers
Peter

Reply | Quote

Peter,
That function will return the range referred to by whatever you pass to it, whether that be a cell containing a range name or an actual range name – it was based on my original point that if you are using a UDF, you might as well bypass all these formulae and simpy use it to return the range you are interested in (rather than using INDIRECTs and OFFSETs). That is why you can’t simply use it in a cell like that.
And yes, you have definitely confused me as to what exactly you are after and why!

Reply | Quote

(Edited by j.peter.orourke on 24-Sep-06 09:33. Fixed typo!)

Thanks Rory… Well. perhaps if I can get the UDF to work I can demonstrate my ‘confused’ solution… Any pointers/other threads worth looking at to help me understand why I’m getting #NAME? when I try to call the UDF?

As an aside.. this model does exactly what I want it to do already – self maintaining extendable lists which can easilly be linked as nested sub lists – the UDF thing is just a refinement to aid clarity, it won’t alter/improve functionality.

Cheers
Peter

Reply | Quote

You get #name error when you have a name that it can not find. Where is the UDF located? If the UDF, for example, is in your personal.xls file and you don’t preface it with the filename it will get this error.

Steve

Reply | Quote

Hi Steve

Located in….. entirely the wrong place! i.e. Finger trouble on my part. Found another thread User Defiend Functions that helped – Insert Module… Doohh..

The UDF doesn’t appear to do what I ‘think’ i want it to do, which doubtless has more to do with my inability to explain what I want… So, I think I’ll leave this for now – the model does what I set out to achieve anyways – and come back to it when I’ve had time to learn more about VBA.. Any good tutorial/reference links out there? I’m far from being a ‘virgin’ programmer – just not overly familiar with VBA, though I did fo a lot of VB stuff up to version 2 and WordBasic, back in the mists of time I know!

Cheers and thanks again to all for your help and support. Fantastic place!

Cheers
Peter

Reply | Quote

Check out post 320,321 it has some links to articles.

I would presume that your “virgin-ness” is more to inexperience with what the excel object model is and not so much the programming itself. My recommendation for that is to use the recorder to do some things and look at the code to get an idea of the object model. I think once you see code written, if you have VB experience, you will see what it is doing: it is just learning the object model.

I still find, on many occasions, recording a macro can get me 50-80% complete. Editing existing code is often easier than creating…

Steve

Reply | Quote

Thanks Steve.. Much appreciated.

Cheers
Peter

Reply | Quote

Steve/Thread Watchers

Attached is final version of this model, for now. Added a section at the top of the “User Input” sheet which generates sample formulas for accessing/manipulating lists. (Requested by a colleague.) I’ve parked the UDF idea for the time being.

Cheers
Peter

Reply | Quote

I think I need help writing a UDF/VBA? The attached spreadsheet shows a technique I’m using for manipulating extendable and related lists. It pretty much does what I want with one exception which is that I have to hard code the name of the target list I want to use via this kind of command:

=INDIRECT(INDEX(tblLists,MATCH(B2,INDEX(tblLists,0,1),0),3))

The “B2” in the above is the hard coded bit. I can change it to the specific name of the list I want to use, “MyList”, “ThisList”, etc, etc. I am using a Defined Name formula, “FindList”, in places, and this is OK provided the cell I’m calling the FindList formula from has the same relative position to the cell containing the name of the list I want to use. What I’d like to arrive at in say a Data Validation cell setting (Allow set to List), is for the Source to be something like:

=FindList(B2 or Defined Name) or =FindList(“Hardware”) or =FindList(=AnotherFormula)

i.e. Either a cell containing the name of the list, the hard coded list name or, a formula that resolves to the list name. Ergo, assuming B1 had something I was looking for, and B2 had the name of the list that ought to contain it, I could then write a formula in B3 like:-

=MATCH(B1,FindList(B2),0)

Can anyone give some pointers as to how to start writing a suitable formula/UDF to achieve the above? N.B. tblLists is an “n” by 3 array. “n” will expand to match the number of entries in the array.

Cheers
Peter

Reply | Quote

I know this is related to your previous post, and it will have the same problems, but the function you want is INDIRECT.

I am not sure I completely understand what you want the “FindList” function to do that INDIRECT does not and your example does not seem to come from your workbook
I can use eg:
=MATCH(C2,INDIRECT(“lst”&B2),0)

without any UDF.

Note: If you were going to use a UDF for this, I would make the MATCH part of the UDF:
=FindItemInList(B1,B2)
And give the index number….

Perhaps I am just a bit confused on what you are after exactly. Do you have an example that is directly relatable to your sample file?

Steve

Reply | Quote

(Edited by j.peter.orourke on 22-Sep-06 17:05. Hopefully to aid clarification…. )

Hi Steve

Thanks, and sorry.. I appear not to do a good job of explaining myself. Decided to start a new thread since this is specifically about how to write a formula/UDF. OK.. The MATCH example I gave is not an ‘issue’ as such. What I think I need to do is write a UDF that replaces the formula:-

=INDIRECT(INDEX(tblLists,MATCH(B2,INDEX(tblLists,0,1),0),3))

The ONLY issue I really have with the above formula is that it’s a bit longwinded. If I had a universal formula that replaced all of the above, like so:

=FindList(B2) – Albeit the formula needs to take cell addresses, defined names, and hard coded text as it’s paremeter.

Then it just makes it easier on the eye when you do want to do something like a MATCH, INDEX, VLOOKUP etc, etc. Currently I would write the MATCH command, for example, as:

=MATCH(B1,INDIRECT(INDEX(tblLists,MATCH(B2,INDEX(tblLists,0,1),0),3)),0)

Rather than:

=MATCH(B1,FindList(B2),0)

i.e. The formulas I have work, they’re just a tad long winded. A Defined Name is inflexible because it depends on relative addressing and there is no way that I know of to pass a parameter, like the cell address B2, into a formula defined as Defined Name? So.. everything works as is.. Just looking to make it easier to read. A UDF seems the only option. Am I making myself clear yet?

EDIT – In fact Steve, maybe I’m complicating matters by including the INDIRECT() portion of the above formula. The portion I actually want to shorten/replace is:

INDEX(tblLists,MATCH(B2,INDEX(tblLists,0,1),0),3)

Which is the part that finds the correct cell in the tblLists array. B2 being the part I would ideally like to pass as a parameter to a UDF.

Cheers
Peter

Reply | Quote

Another option, since you are going the “code route” is not to use validation, but to use comboboxes instead:
You have much more control
You won’t need to define the names: The ranges can be read directly when the combobox is called
The dependent lists can be reset (something validation fails at!)

post 537,206 has a demo with 1 combobox doing the work of many validations whose technique could be adapted to multiple and dependent lists…

Once you make the leap to decide that code is possible, you gain much more control by using code…

Steve

Reply | Quote

If you put in Cell C2 the following formula (with your defined “FindList”)
=MATCH(B1,FindList,0)

It does what you “want” without a UDF…

[Your “FindList” list looks up the name to the left of the cell the formula is in as you have currently defined it..]

Steve

Reply | Quote