User defined function returning "VALUE result

[tfurnivall]

I have a User Defined Function that will move values from a summary area to detail rows in another spreadsheet. The application is an income tax calculator tool.

I have one sheet ("W2 Data") that stores information about income and taxes\, which allows for multiple W2 forms for up to 2 taxpayers. (Note for non US readers: Form W2 shows information about income, taxes paid, etc. There are five categories of interest, Federal Income Tax, FICA (Social Security), Medicare, State Tax and Local Tax). Here's what the W2 sheet looks like:

Please Login or Register  to view this content.

and the Summary area of the W2 sheet looks like this:

Please Login or Register  to view this content.

I want to move the summary values to another spreadsheet, called TaxesPaid. The problem (and the reason I need to use a UDF) is that if there is no Local tax, then I do not move the information, and if there is no data for Taxpayer 2, then I do not move data for that taxpayer. This means that the Taxes Paid sheet could have 1or 2 rows of taxes paid coming from the W2 sheet. My UDF, called CalculateDeductibleTaxes takes a taxpayer, and moves the appropriate data to the appropriate row:

Please Login or Register  to view this content.

I'm at a loss to figure out what could be wrong. Any ideas, insights creative suggestions would be appreciated,

Thanks,

Tony

[Norie]

Tony

You can only use a UDF to return a value to the cell it's placed in, you can't change any other cells.

That's why the code fails where you indicate.

[tfurnivall]

Hi Norrie

Well, that certainly explains it.

Not that I'm happy to learn the reason - and yes, I have been through all the safe-coding classes about not allowing side-effects out of functions So let me see if I can re-phrase the requirements of this problem.

I need to find a technique that will allow me to automagically copy varying pieces of data to varying numbers of rows in a different worksheet. Because there are two possible rows of source data, and two possible data items to copy, I am looking at anywhere from 1 to 4 rows of data in the destination sheet, emerging from 1 to 2 rows of data in the source sheet. Furthermore, there is no hierarchical relationship between any of the source values (any or all of them can be 0, and in that case must not be moved).

I need to be able to use the resulting rows as 'protected' sources for a listbox, in the UI for the spreadsheet, so I don't want to have any unnecessary rows around.

Are you - is anyone - aware of a technique that will allow me to do this. (The CalculateDeductibleTaxes routine that I have above, works, but the side-effect is apparently not allowed

Thanks again for the enlightenment!

Tony

[Norie]

Couldn't you use a sub instead of a function?

[tfurnivall]

I didn't think you could refer to a Sub procedure in the body of a work book. The goal, of course, is to have the calculation carried out every time the spreadsheet changes.

Tony