linking previously calculated data - #value! error

[jcavigli]

In cells A1, A2, and A3 of Sheet1 I have data. This data is the result of some calculation else where in Sheet1. Nevertheless, I would like to link this data to Sheet2 in cells A1, A2, and A3. Now normally, you can't do this. But is their a way around this error message?

[rylo]

Hi

What is the error message?

rylo

[jcavigli]

I am thinking there has to be a way around this.

[VBA Noob]

Try a if statement

http://www.j-walk.com/ss/excel/usertips/tip010.htm

or a conditional formatting

http://www.contextures.com/xlCondFormat03.html#Errors

VBA Noob

[jcavigli]

I need the data to link over to Sheet2. If A1, A2, and A3 in Sheet1 are the result of a calculation then to simply use this doesn't work:

'Sheet1'A1
'Sheet1'A2
'Sheet1'A3


Naturally, an error message is going to pop up instead of the actual value. I am not interested in supressing this message but in getting the actual value to appear.

I could do a paste special an select 'value' but this is a problem as well. My data won't change with a change on Sheet1.

Any advice?

[broro183]

hi jcavigli,

Please note that if one of the precedents of a1-3 is an error then this error is carried all the way through into all dependent cells ie if an error exists that's what you'll get - to overcome this you need to fix the initial error. See below for the Help files info about the "value" error...

Correct a #VALUE! error
Occurs when the wrong type of argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) or operand (operand: Items on either side of an operator in a formula. In Excel, operands can be values, cell references, names, labels, and functions.) is used.

Can you please attach an example file with your problem clearly shown & also the results that you would like to see?

hth
Rob

[jcavigli]

sorry for the double post

please delete

[jcavigli]

1.) Data is entered in F5:511 and H5:H12 of Fee Input worksheet. However, not all cells might have data. What you see is an example.
2.) From there calcuations are made for fees on various account sizes. This exists on the Fee Input worksheet in Z6:AB12. [Calcuations are made my a custom function called Fee() locate in a module.]
3.) I would like to link the '% charged' data in AB6:AB12 of 'Fee Input' to B7:B11 of Rogerscasey.

[VBA Noob]

Change your code to include the sheet

Please Login or Register  to view this content.

VBA Noob

[jcavigli]

I never knew about that argument.

Thank you.

[VBA Noob]

The reason you couldn't link was the Volatile statement recalc the function for the activesheet.

By adding the sheet it always looked at the right sheet

VBA Noob