Hello,
Let's say I have two primary columns in my spreadsheet, x and y. Depending on what I want to do with the data, zeroes need to be handled differently. Currently, I have three cases. This results in me needing additional 'dummy' columns that duplicate the data except for how zeroes are handled. These cases are:
1. Function 1: In the first function that manipulates the data I need a numeric zero in the cell that is zero. This can be accomplished simply with the as-is x and y columns, no problem here.
2. Function 2: In the second function that manipulates the data in, say, column x I need a blank cell if the value is zero. I have a 'dummy1 x' column that checks the value of x. If it equals zero, I return a blank cell in 'dummy1 x'.
3. Plotting: I am making a scatter plot with the data. In this case, I do not want zeroes to plot. I have a 'dummy2 x' column that checks the value of x. If it equals zero, it returns '#N/A'.
In summary I have 3 'x' columns where each one handles a zero differently: 0, #N/A, and a blank cell.
Currently, in order to clean things up I have all the dummy columns grouped (outline) and can hide them with a button to make the spreadsheet easier to view. However, I was wondering if there is a better method for accomplishing my goal - maybe have one 'x' column and then have VBA code that will output the correct format for zeroes depending on what is using the data. While this may take more time to implement, my goal is for the user not to get confused by all of the additional columns (since the real spreadsheet with have 20+ instances of this procedure needing to be done and thus 40+ dummy columns).
Thanks
Let's say I have two primary columns in my spreadsheet, x and y. Depending on what I want to do with the data, zeroes need to be handled differently. Currently, I have three cases. This results in me needing additional 'dummy' columns that duplicate the data except for how zeroes are handled. These cases are:
1. Function 1: In the first function that manipulates the data I need a numeric zero in the cell that is zero. This can be accomplished simply with the as-is x and y columns, no problem here.
2. Function 2: In the second function that manipulates the data in, say, column x I need a blank cell if the value is zero. I have a 'dummy1 x' column that checks the value of x. If it equals zero, I return a blank cell in 'dummy1 x'.
3. Plotting: I am making a scatter plot with the data. In this case, I do not want zeroes to plot. I have a 'dummy2 x' column that checks the value of x. If it equals zero, it returns '#N/A'.
In summary I have 3 'x' columns where each one handles a zero differently: 0, #N/A, and a blank cell.
Currently, in order to clean things up I have all the dummy columns grouped (outline) and can hide them with a button to make the spreadsheet easier to view. However, I was wondering if there is a better method for accomplishing my goal - maybe have one 'x' column and then have VBA code that will output the correct format for zeroes depending on what is using the data. While this may take more time to implement, my goal is for the user not to get confused by all of the additional columns (since the real spreadsheet with have 20+ instances of this procedure needing to be done and thus 40+ dummy columns).
Thanks
