Search results

Hello, Can you check if the following article on non-contiguous ranges helps? Cheers, Sajan.

Hi Xiq, Which character is that? I do not see any reference to any such characters in the OP's post. What am I overlooking?

Hello Jcambareri, I am not sure I am following your description above. Can you add a few rows of manually worked out examples for the data you supplied? That might shed some light on what you want to do. Cheers, Sajan.

Hello, Here is one more approach (for a string in A2) =LOOKUP(LOOKUP(LEN($A2),FIND(ROW($1:$10)-1,$A2)), FIND(" ", " "&TRIM(A2),ROW(OFFSET(A$1,,,LEN($A2)))), MID($A2, ROW(OFFSET(A$1,,,LEN($A2))), MMULT(FIND(" ",{""," "}&TRIM($A2)&" ", ROW(OFFSET(A$1,,,LEN($A2)))),{1;-1}))) Cheers, Sajan.

Hi Maggie, Sounds like you would be able to flatten B:K columns on each worksheet to generate the summaries you need on your summary tab. That would also eliminate the need to handle multiple 2-D ranges in a single formula. If you do not get any other feedback, I can take a look in another...

Is your question about a product offered by Chandoo? If not, please describe your question in detail.

Hi Pam, Thanks for the feedback! Glad to help! Glad to hear that the instructions worked in Excel 2003! -Sajan.

Hi AjayxInc2, While Xiq's formula helps your current situation, I would still encourage you to restructure your data since it might allow you to use basic lookup functions. Cheers, Sajan.

Hi Lori, Thanks for this DGET formula. I have never used any of the database functions in Excel, but had assumed that functions like DGET would be like a SQL Select statement, returning one or more items that match criteria. I was surprised and disappointed to read that it only returns one...

Hi Bob, I can only speculate, but the VBA author might have been trying to determine the number of elapsed days between two dates, and used the number of weeks since 1/1/1900 as the basis for that calculation (i.e. number of weeks divided by 52 to get years, then the number of years times 365 to...

if you divide a date by 7, you get the number of weeks since 1/1/1900, which could be what the VBA author was attempting. (Not sure regarding the rationale, etc.!) -Sajan.

Hello Pam, I am not sure what the menu options are in Excel 2003... but in Excel 2010, I am able to right click on "Item Code" in Row Labels to get the "Field Settings" menu, and from there, I am able to choose "None" for Subtotals. That removes the subtotals lines. Hope that helps. -Sajan.

Hi SirJB7, Indeed... Unless someone points out that they are using a different version of Excel, I typically assume that US English version is being used. It was interesting to see a "Georgian" version of an Excel file the other day! Certainly not something that I encounter in the normal...

Hello, Try the following array formula: =TEXT(INDEX($B$7:$B$16, MATCH(TRUE, $B$7:$B$16<1,0)), "0%") & " " & INDEX($A$7:$A$16, MATCH(TRUE, $B$7:$B$16<1,0)) enter with Ctrl + Shift + Enter. Cheers, Sajan.

Hello nejjoan14, From the image file you uploaded, I am gathering that you want to display the multiple job #s in a single cell. Doing that with formulas can get messy (and would involve a series of helper cells). I suggest that you pursue a VBA based solution. Since VBA is not my forte, I...

Hi Bob, Thanks for the feedback! Glad your issue is solved! -Sajan.

Hi SirJB7, It is not just a theoretical limitation... COUNTA's functionality is clearly documented in the Help. COUNTA counts cells, and the count does not include blanks. Folks need to be aware of that. That is all. -Sajan.

Hi SirJB7, Debraj, Good review of how regional settings can affect calculations! Something for folks to consider when attempting to apply a formula from one Excel version into another. -Sajan.

Hi SirJB7, Since COUNTA counts the values while excluding empty cells, it may not be reliable if there are blank rows for any reason. -Sajan.

Hi SirJB7, Since the title of the post, as well as the samples in the initial post all mention decimal point, and not a decimal comma (is there something like that?), I think it is a good assumption that a "point" is involved! -Sajan.

Hi Debraj, I am not sure if regional settings play a role in this case. I am able to enter a date as "Jan 6" or "6 Jan", even though my regional date setting is month-day-year. And yes, when a date is entered, the year defaults to current year. Are you also seeing a different output when...

Thanks for the feedback! Glad to help! -Sajan.

Hi, Your results seem to be different than mine: See attached doc. Cheers, Sajan.

Hi, For the sample data you posted, you can try the following formula: (put in cell B1 and copy down) =NETWORKDAYS(TRIM(RIGHT(A1,3)&" " & MID(A1,4,3))+0, TRIM(RIGHT(A1,3)&" " &LEFT(RIGHT(A1,6),3))+0) Cheers, Sajan.

Without some samples, I am not able to help you. Feel free to upload a workbook with your sample data. (You can do so using the "Upload a File" button.)