custom function in excel

[cwunderlich]

Is there a way to have a custom function look at another sheet within the same wkbk and manipulate data within that other sheet and return a value back to the original cell??

I thought there was an easy way, but I must be missing something...

Thanks!

 

[Yahya]

could you post small sample from your data
and what is the expected result

 

[Ruddles]

Yes, this is possible, but it depends on what you mean by "manipulate".

You cannot modify a worksheet from a function or from a subroutine called from a function.

If you already have some code written, please feel free to post it here. If you do so, please use CODE tags - the # icon in the advanced editor toolbar.

 

[cwunderlich]

could you post small sample from your data
and what is the expected result

Ok, so I have a program which "sends" data to tables for each year. Once this data is "sent", a row on sheet2 titled "Sent to .... " is populated with the date the data was sent. This row number varies from case to case.

On sheet1, I am making a checklist of sorts. One of the things in the checklist is to notify the user if data from sheet2 has been "sent".

I was trying to come up with a function that could look for the "Sent to ... " row within sheet2 and then look to see if any fields within that row are populated. If there are fields populated, then I want the function to return "Sent" or "Yes" or "True" or something like that. If the function does not find any cells populated within that row on sheet2, I want it to return "Not Sent", "No", or "False", etc...

hope that helps...

thanks

 

[Ruddles]

I'm not sure you need a custom function for this but you'd need to post a sample of your data for anyone to be able to advise you for sure. You'd also have to include the row & column references if you wanted someone to write you the necessary formulae.

 

[cwunderlich]

I'm not sure you need a custom function for this but you'd need to post a sample of your data for anyone to be able to advise you for sure. You'd also have to include the row & column references if you wanted someone to write you the necessary formulae.

That's the point, the row & column reference will vary from account to account.

I was just hoping for some sample code of a function which looks at another sheet and, for example, looks in Column A for "Data Sent" then goes to the row where it finds "Data Sent" and then looks in that row to see if any other cells are filled out. I was hoping if I could just get a generic example, I could take it from there.

Thanks

 

[cwunderlich]

bump

 

[Ruddles]

I realise the position of the data can vary but functions such as MATCH and VLOOKUP do exactly what you're describing.

It's difficult to write a generic sample of VBA or suggest a possible formula without a sample of the code to base it on.

 

[cwunderlich]

I realise the position of the data can vary but functions such as MATCH and VLOOKUP do exactly what you're describing.

It's difficult to write a generic sample of VBA or suggest a possible formula without a sample of the code to base it on.

Here is what I originally tried, but the function isn't working.

Function CheckSent() As Boolean

Sheets("Sheet2").Select
Columns("A:A").Select
Selection.Find(What:="Date Sent to xxxx:", After:=ActiveCell, LookIn _
:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.End(xlToRight).Select
I
f Selection.value = "" Then
CheckSent = False
Else
CheckSent = True
End If
End Function

-how could this be done with vlookup?, since multiple columns need to be checked, are you saying that vlookup combined with match() would do the job? I toyed around with these originally, but to no avail.

thanks

 

[cwunderlich]

bump