long formula

I make use of the If statement a lot and sometimes the formula returns a 0. When the main formula returns a zero I want it to show the dash character so I usually make a formula like this: =If(*Formula* = 0,"-",*Formula*) My main question is if there is a way to not have to repeat the formula...

Hi All ive created this formula and it works perfect, but as your see in a sec its rather big. Can anyone think of a smarter way to get the same result. The below checks to see if there is a date in the BOLDED cell and if there is it moved onto the next till it cant find a date, when it cant...

I am using this code to run a macro everytime a cell in column H changes. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("H:H")) Is Nothing Then Call DefMacro End If End Sub The macro I am trying to use is supposed to make cells in column 'I' change in...

Hello I'm new here. I finished a formula that uses nested IF statements along with SUMPRODUCT, --ISNUMBER, and SEARCH, in order to put a string in a cell if found match in cell $F5 (and so on). The problem is when I attempt to create MACRO. Before starting MACRO, I copy formula then paste...

HI ALL, THIS IS MY FIRSTPOST SO HOPEFULLY I'M IN LINE W FORUM POLICIES. I HAVE SET UP A MACRO TO PULL DATA THROUGH FROM A SECOND OPEN WORKBOOK TO A SHEET. IN THIS SHEET I WANT TO USE A FEW OF THE DATA POINTS TO CALCULATE A VALUE. I HAVE THE FOLLOWING FORMULA THAT NEEDS TO BE APPLIED TO THE...

Hy all! I have 25 dynamic lists and i need to make a cell with data validation that shows me a list according to the first cell value. I used Name Manager to create the lists (A-Y to shorten the length... C & R was not allowed so i used CC & RR), but the formula in Data Validation Source is to...

Hi There, I am applying a formula in excel and it is working fine. I am recording the macro with the formula and I am getting Syntax error. The formula is "=IF(RIGHT(TRIM(LEFT(F2,IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(FIND(".",F2,1),FIND("...

Hi, I have a very long formula that gets the sales value from one of three sheets depending on some conditions. See below formula: =IFERROR(IF([@[12 months alive(Yes/No)]]="No",IFERROR(INDEX('BUDGET TY'!$Q$1:$Q$5000,MATCH([@[Store '#]],'BUDGET TY'!$B$1:$B$5000,0)),INDEX('BUDGET...

Greetings all, I've been a frequent visitor here over the past few months, so I'd like to start by saying thanks to all who answer these types of questions. Ya'll have been quite helpful, even to those who didn't ask the question :D The official question: Would there be some trigger that could...

I am building a Project Budget sheet and I am trying to shorten the formulas that provide sub-totals. I would be grateful if anyone could help. Staff names are represented in the first column followed by their day rate cost in the next. The subsequent columns represent weeks of the year. Each...

Hi, I need a way to get around this error because I need this formula to work in the next few days. The formula is as follows: =CONCATENATE(LEFT(B11,FIND(",",B11,1)-1),"...

I have Several Countifs and want to make it into one saying if this area was assigned by one of these people count the job, I have tried many different ways and can not seem to make it work. unless I have it search for each assigning Person one by one. Is there a way to make this Formula much...

I have a highly complex spreadsheet that I have to long hand the formula. Once I create the spreadsheet and create the formulas I cannot modify the sheet, only cell values. If I insert a column, the end formula will not count the new column contents (but I can delete columns before the formula...

Hello I have a few times got this message: "One or more formulas in this workbook are longer than the allowed limit of 8192 characters. To avoid this limitation, save the workbook in the Binary Workbook format." I have no long formulas in my excel. I even made a macro to check all formulas...

Could someone please let me know if it is possible to shrink this countif statement? It becomes too much to handle since I have to copy it for multiple different rows. The A1 stuff is to know if the sheet is "active" (if there is a different way to do this I am all ears) it would need to stay...

Hi there VBA Pros, I am getting the following error when try the run this code (Application-defined or object error 1004) Sub Filter_PayRoll_Report_PivotTables() Dim MonthYear_Name As Range Set MonthYear_Name = Sheet3.Range("E2") With Application .EnableEvents = False .DisplayAlerts...

I often find myself faced with fairly long formula in a cell. This by itself isn't a problem, but usually these formulas have many nested "IF" statements, and are logically & arithmatically complex. I have gone to the Microsoft site, and used Excel's help to find a better way to analyze, edit...

Hi Everyone, I have a formula that does exactly what i want, but my friend cant use it on his computer (he doesn't have Excel 2007) I'm trying to create a Custom Function/ UDF to do this in excel VBA but ive hit a bit of a road bump...