I am looking for a way to create a [ec]*[/ec] that as the data changes, the chart axis will change, leaving out the blank data. For example, on my spreadsheet, I am using a HLOOKUP formula to find up to 15 lines of data. Not all instances will have the total 15 lines; most are around 8 or 9, but I have to account for the 1 time that all 15 lines will be used. Is there a way to use a formula in the chart to only pull in lines that contain data? Thanks.
Re: Web Query of numbersQuote from alring
I'm not sure want you want. But I think it's this you want.
Right click on the cells were you want to have thoose numbers and under Custom you wright 0000###############0
What I am running into is this. When I query off the particular web based program, the output for the carton ID is 20 digits (for example, one would be 00004200170865987458). When I export this using Web Query, it only imports 15 digits - it cuts the first 4 zeros off & replaces the last number with a 0, no matter how I format the cells. If I do not format the cells prior to importing the info, it shows up as 4.20017E+15. So all in all, out of a 20 digit number, it only improts 15 of the 20. Is there a way to change this or am I destined to not use Web Query with this particular program?
I am wanting to use Excel to perform a web query into an online database our company uses. One of the columns I am wanting to import contains numbers that are 20 digits long & they begin with 4 zero's. The problem is that when Excel imports the query, if I fprmat the cells & preserve the formatting, it cuts off the first 4 zero's & replaces the last number with a zero. Basically, it looks as if it is only importing 15 numbers. Is there a way around this ; is there a way to force Excel to import all 20 numbers? If I don't format the cells, it imports in the following format - 4.20017E+15
Re: Chart building with ranked data
I tried the code in your reply & in the function box, it keeps telling me that it is Volatile & returns an error.
EDIT - I got the OFFSET & MATCH functions to work. Thanks again.
I need to build some charts using data that is being collected using the RANK function. My issue is that LOOKUP & VLOOKUP will not work because the data is not in ascending order. The attached image shows the data I am using. What I need to generate is the top ten, in ascending order on a seperate worksheet within the workbook. That data would then be used to generate the charts. Columns C & D is the first set of data. What I need is a formula that looks at column D, finds the number 1, & return the corresponding value from Column B, and so on to number 10. Same for columns E & F, and G & H, all returning the corresponding value from Column B. The reason the data is not ascending - first, because it is calculated YTD, it can change month to month & second, I can't sort ascending because it is three different sets of values for the same Column B. Any help or tips would be greatly appreciated. Thanks.