Search results

Hi there! In your yellow input cell, use this formula (or check the attached file): =IFERROR(ROWS(INDEX($A:$A,B10):INDEX($A:$A,C10) INDEX($A:$A,F14):INDEX($A:$A,G14)),0)+IFERROR(ROWS(INDEX($A:$A,B10):INDEX($A:$A,C10)...

Hi Luke! Amazing use of ROWS()! I would never think of something like that, I just learned something today, thank you for that! :) Best

I'm not na expert in these type of situation, but I believe you may have diferente color palettes in each of the workbooks youre working with. What I mean is (and it happened to me before working with workbooks sent from diferente companies), what excel recognizes as red in your workbook may be...

Hi! I've uploaded na exemple file where I do exactly what I believe you want to do... In my exemple there's: 3 sheets named List1 List2 and List3 with sample of lists such as the one you want to join in a master list. A sheet named AuxiliarySheet with some auxiliary data I need to apply my...

Those custom lists worked perfectly Luke M. Thank you very much for your help!

Hi all! I tried to search for a solution to this as i'm pretty sure i must not be the first person with this problema, but couldn't find any. Sorry if reposting. So I wanted to use a slicer to dynamically filter some data from a pivot. This data relates to some values that can fit into some...

you can use something like: =MID(B3,10,1) & MID(B3,9,1) & MID(B3,8,1) & MID(B3,7,1) & MID(B3,6,1) & MID(B3,5,1) & MID(B3,4,1) & MID(B3,3,1) & MID(B3,2,1) & MID(B3,1,1) if you want to reverse the string in B3 AND YOUR STRING IS NO LONGER THAN 10 CHARACTERS... :P this is not very elegant...

no prob, my friend. RSS feeds keep me updated with whats going on so i respond quickly! :D

SOLVED!!! :D in the Custom option of Data Validation, you can write the Formula: "=AND(ISNUMBER(B3);SUM($B3:$F3)<=100)" note this is for cell B3, you can then copy cell B3 to range B3:F3 and the isnumber check inside the formula will propagate the cell address to be verified! ;) i...

hey there, you can use one of the data validation options which is "Custom" where we now have "Decimal" and in the Formula field put something like "=SUM($B3:$F3)<=100" note i locked the columns with $B and $F. this way it works 100% but you loose the decimal validation, which means...

glad i helped

check an example here... i believe its now working https://docs.google.com/leaf?id=0B5piylDHt3ybNWJjMzNkMjktZTI3NC00MWI0LTgxMjgtZjk1N2JlNWQ4MzAx&hl=en_US choose "download" to get sheet and check formulas and validation rules

hi yesterday when i thought of this solution i had my excel with manual calculation, which made this work, when it actually doesnt... with the auxiliary value being 100 (the max sum allowed) when every cell in the validated range is zero, if you put something like 60 in the first cell, it...

yes, i see the problem, im getting it too... the problem is excel is calculating the auxiliary formula value and only then forcing validation... thats major bug in my solution, ill try to check if theres any way i can turn this around.

hey there :D glad i could help. the logic behind it... well, basically, the main thing when working with dates and times in excel is realizing that the basic "unit" is the day, which means 1 is always 1 day... this means 1 is a day, 7 is a week, 1/24 is an hour, 1/(24*60) is a minute and...

use a cell as an auxiliary where you put the formula = 100 - sum(A1:F1) then, select your range you want to force validate (A1:F1) and go to Data Validation allow Decimals that are "less than or equal to" a "Maximum:" in the auxiliary formula (if it was, for instance, cell G1, put =$G$1 in...

= B3 + if(C3 = "AM" , 0 , 0.5)

using the .RefersTo property, you can extract a string with the Range inside you named ranges, but it extracts the complete address of the range, like "sheet!$A$1"... simple string management can get you rid of the "sheet!" part of the string, and the sky is now the limit! :D something like...

hi there... we can check it, yes: {=SUM(--NOT(ISERROR(FIND( KEYWORDLIST , A1))))} imagine your list of keyords to search for is {aaa,bbb,ccc,ddd,eee}. you define this list as KEYWORDLIST range in excel. in cell A1 there is something like "aaa ccc fff" therefore you want a formula that...

hi there! in the Base workbook, to where you want to copy the 39 files, create a sheet and in column A, starting on cell A1 and ending in cell A39, write the list of the files you want to combine into this base workbook, with FULL PATH AND FULL FILE NAME, something like this: A1 -&#62...

hi there... the only way i see it, in my example, the only way the aux3 would return an error (because of trying to add text) is because of the first row (and thats why, if you check my example, the first cell in aux3 column has a different formula (simple link to aux2 column, rather than a...

hi there! im nunes, from portugal. im a traffic consultant and i work several hours with excel. chandoo is, for what i care, the excel god and im here to learn new things and new ways to do old things, and of course, to help when i know and can...

the above formula is case sensitive, if you want to make it case unsensitive, you just have to use upper or lower in both arguments, key word list and cell to search, like this: {=SUM(--NOT(ISERROR(FIND( upper(KEYWORDLIST) , upper(A1)))))}

hi, from what i understood you want to count the number of keywords that can be found in a list of words in a cell, right??? assuming your base list of keywords is in a range of cells (each keyword in a cell), name that range something, like KEYWORDLIST. now if you want to count the number...

http://exertia.wordpress.com/2006/04/23/displaying-lakhs-and-crores-in-excel/