Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Selection of Non-Continuous Data
I posted this earlier in the programming group, but I think that this
is the right place. I am attempting to create a named range for a graph to display data from the end of each month. Ideally, the data for the range would come from the following set: A Jan Feb Mar Apr B g2 g8 g12 #N/A where each cell in row B contains a cell reference for the date of the last workday of a month. For months that have not occured yet, the formula I created returns the #N/A error. My problem is creating the named range that uses the INDIRECT() function to read in each cell reference that exists. The data needs to remain a cell reference (instead of the value it is referring to) so that I can later use an OFFSET() function on the named range to grab the performance values for each month. Any comments or suggestions would be greatly appreciated. - BGetson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Selection of Non-Continuous Data
My main problem is creating a base function that can recognize if I
receive the #N/A error. As you mentioned, the solution might be creating additional rows to only hold the data that I want/exists, but I don't know how I would get that to work. I guess in pseudocode (or pseudo-function) I want the named range to look something like: for B2:B5 =IF B2 < #N/A, INDIRECT(B2) I tried using something like this earlier using the union operator for each cell I wanted to analyze (12), but it became a mess and was unable to figure out where excel was giving me an error. Also, I posted here instead of in .charting because I'm having trouble with the worksheet function aspect of my problem. I hope that this might clear up my issue. -BGetson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Selection of Non-Continuous Data
The formula to test if a cell contains #N/A is:
=IF(ISNA(B2),"",INDIRECT(B2)) Bear in mind that, for charting, #N/A might be very useful because it is ignored and not plotted. Therefore you might want to keep it. HTH Kostis wrote: My main problem is creating a base function that can recognize if I receive the #N/A error. As you mentioned, the solution might be creating additional rows to only hold the data that I want/exists, but I don't know how I would get that to work. I guess in pseudocode (or pseudo-function) I want the named range to look something like: for B2:B5 =IF B2 < #N/A, INDIRECT(B2) I tried using something like this earlier using the union operator for each cell I wanted to analyze (12), but it became a mess and was unable to figure out where excel was giving me an error. Also, I posted here instead of in .charting because I'm having trouble with the worksheet function aspect of my problem. I hope that this might clear up my issue. -BGetson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
Retrieve multiple data rows data from a very long list and copy t | Excel Discussion (Misc queries) | |||
pivot table with selection values not included in the base data | Charts and Charting in Excel | |||
Reference multiple cells in if statement | Excel Worksheet Functions | |||
Macro data selection line delete | Excel Discussion (Misc queries) |