Complicated Sumproduct/VBA
Nov 27, 2008I am having some problems converting a working formula in excel into VBA code. The working formula is as follows;
=SUMPRODUCT(--(ReasonList=$B51),--(AgeList=0),--(CreatedList>=$D$47),--(CreatedList
ADVERTISEMENT
Making Complicated Sumproduct Into Function?
Jul 9, 2014So I have a rather complicated IF, Countif, Sumproduct combo in a formula right now. As you can imagine this makes my worksheet run rather slowly. I was curious if there is any way to make this formula into a User Defined Function. The formula looks like this:
[CODE]
=IF(A15="","",IF((AND(COUNTIFS(J:J,J15,AM:AM,ABS(AM15),AD:AD,AD15)>1,
(SUMPRODUCT(--($J$17:$J$10000=$J15),--($AD$17:$AD$10000=$AD15),--(ABS($BD$17:$BD$10000)
=ABS($BD15)),--($AX$17:$AX$10000=$AX15),$BD$17:$BD$10000))
[CODE]
I would not be surprised if this isn't possible but thought I would try. Another possible approach would be to make this into a macro and instead of the output being "Duplicate" or "Research" just have the "Duplicate" Cells Highlighted in Red and the "Research" Celss highlighted in yellow.
Complicated Div/0 Error
May 29, 2007I'm currently using the formula in cell I43:
=AVERAGE(IF(I10:I420,I10:I42,""))
If any of these cells are blank I get the #DIV/0! error. If any are filled in then I get an average which is what I want. The problem is that I am trying to average mutiple sheets of these cells (I43) and if any sheet has this has the #div! error it won't calculate.
Complicated VBA - 2 Sheets
Nov 20, 2002I have divided my data into 2 sheets. They are called 1) Sheet1 2)Sheet2
Sheet1 -> Sheet with all my Data
Sheet2 -> Sheet with calculations (formula's)
Know what I need to do is the following
1) As soon as someone starts enters a value in Sheet1 (eg. as soon as data is entered in Column A in Sheet1) VBA captures that line # and then in Sheet2 it copies the formula from the ROW ABOVE itself and then inserts a new ROW (corresponding to same row where value was entered in Sheet1) and copies that formula into that row (FORMULA are from COLUMNS B to J.
I need is Sheet2 to update itself and perform calculations when new line of data is started in Sheet1 and to INSERT a new Row so ALL other calculations BELOW it are updated as well. (Eg. Averages, Additional Calculations)
I have a lot of calculations in Sheet2 that correspond to Sheet1. The new ROW in Sheet2 has to look at where the data in Sheet1 was entered because I have a bunch of Averages at the bottom of Sheet2. I need all that to automatically update. So VBA has to insert a NEW row and then copy the previous row's formula in order for that to work.
Complicated IF Statement With Many Variables
Mar 12, 2014This is a complicated thing I'm trying to accomplish and I only have a rudimentary grasp of Excel formulas and logical statements.
I need to check BE2 if it's blank. If it's not blank, leave it alone. If it is blank, I need to check S2. If S2 contains the name of 1 of 11 counties (ex.: "Marion","Pinellas","Orange","Osceola", etc.), I need BE2 to say "TBD". If it does not say one of those 11 counties, it needs to say "N/A".
Complicated Calculation Using Tables
Feb 8, 2010I am creating a spreadsheet where I am able to track the events that our clients have been invited to AND in a separate column, I need to track who actually attended in order to get a total of how much we have allocated on each client, AND how much we actually spent, to date, on the client.
Through the help of some ExcelForum experts, I was told a pivot table would be my best bet.
Please see attached for an example of the columns on my current spreadsheet.
I need to give each event a value, so if “Jim” Says “Yes” to Golf, “yes” will equal “$400.00”. But if Jim says Yes to the Golf ($400.00), Spring Event ($100), and Executive retreat ($1400.00)” The “Yes” in those three columns will be calculated in a side column ($400+$100+$1400). Which means that the “Yes” must have a different value assigned to it, depending on which column it falls under.
The other important functions that my spreadsheet should have are as follows:
1) I need to be able to manually enter an amount, if need be, instead of a “yes’, and have the total be calculated all the same, in the total column.
2) As mentioned, I need to be able to have one column that keeps the total of how much we have allocated on that specific client (if we invite them to 4 events, and the total of the four =$2000.00, that would be our allocated amount)… but I need a separate column that holds the amount spent on the client to date (If we have “Yes” next to Golf, spa, and trip, meaning we have invited him for the three events and are prepared to spend $1000 on him, but he only accepted to the Golf, I need one column to keep the total $1000.00 and a side column to now read “250”, for example. And if in a few months he accepts to the Trip, I need to be able to track that he accepted, and the “Total Spent” should now be “750” while the “total Allocated amount” should still be $1000.00.
Is it possible to create a pivot table that meets my needs?
Getting Complicated Sheet To A Table
Aug 11, 2013I built a excel file to make some calculations for forex. I started with four prices for each day (open, high, low, close). I am calculating for four price ranges for the next day. That is ok. Another part of the sheet compares results to the predictions. From the four prices that come in everyday I calculate average over 8 weeks, over the last week, and individually for each day of the week. Here it is: TESTBook1.xlsx
I wanted to make a surface graph, but I have no table. I have no formula to use to make a table because the calculations are all over the sheet. I tried tracking through the cell where my "total" is put out, but it is a very long and involved trip. Even if I started a new sheet just to make a table, I can't get my head around the calculations.
I have two cells where I put in my adjustment numbers. In the first cell I put in a number from 0.00000 to at most 0.30000. The second cell I put in a number from 0.00000 to at most -0.30000. I then look at a third cell for the highest output. I got tired of typing in numbers, so I put sliders which works better.
So my table would have 0.00000 to 0.30000 on one axis and 0.00000 to -0.30000 on the other. The formula = ????
Complicated Search Macro
Aug 20, 2007I'm in the process of decoding a mass amount of data that will be incoming shortly. The data will be formatted as shown below. What I would like to do is have a macro that between two full seconds (for example 10:11:48:0 to 10:11:49:0) would find the mode of the RSSI values, paste that with a time value that is in the general excel format. (I.E. 10:11:48 = 0.42484). When all is said and done, what I have access to should look like the second string of posted data. I can get the time cleaned up on my own, but I'm having trouble with the loop macro that will find which cells to use for the mode.
View 12 Replies View RelatedComplicated Lookup By Date
Feb 4, 2009I'm trying to create a macro that allows a date to be entered into a Dialog box that is activated by a short cut key.
The macro would use this date to look through all the information on tab Output, and if(as) it finds dates that match the information will then be filled out on tab Schedule.
For clarification Work Center would be the abbreviation (letter code) about the dates.
Complicated Minimum Value Search
Dec 17, 2007i have a spreadsheet that has 300+rows,and the details below
b2 is the description and date, b4 to b29 are the item name
c3 is total sales volume between c4 to c29,
then another section starts from row 30 till row 57, row 58 till row 85 etc till the end (27 rows for each section)
i need to search the minimum value in each section and highlight it as red.
i know i can search the minimum value by using MIN(b2:B29), but how can i repeat it for each section?
UDF To Replace Complicated Vlookup
Apr 22, 2009I am trying to do a vlookup that returns the last and last but one value in a row.
If it were simple my vlookup would be
=VLOOKUP(A11,Comments!A1:F22,XX,0)
=VLOOKUP(A11,Comments!A1:F22,XX-1,0)
Where XX is the last filled column on the row.
I have attempted the following but can't figure it out.
Option Explicit
Public Function lastcomment(cust As String)
Dim Loc As Range
Dim ComDate As String
Dim Comm As String
Set Loc = Sheets("comments").Range("a1:a10000").Find(cust)
ComDate = Loc.End(xlToRight).Offset(, -1).Value
Comm = Loc.End(xlToRight).Value
lastcomment = ComDate & " : " & Comm
End Function
What have I done wrong?
Complicated Shift Differential
Apr 22, 2009Currently I have developed a Time sheet for employees however I am having a lot of trouble with the shift differentials right now.
First, I used an If function to say If(B7="E",B6,0) E would be the evening shift and of course this works fine if everyone worked an perfect Evening shift within the time limits however, they dont.
I'm wonderin if it is possible to have one, or multiple formulas that can do the following:
First shift differential is from 14:00-23:00
Second shift differential is from 22:00-07:00, However lets say you start at 14:00 I don't want it calculating the shift differential of the first one for 22:00-23:00.
Third shift differential is a weekend one which I have figured out it's rather simple to just have a IF function for that.
Start time is B3
End time is B4
Breaks is B5 (However, Breaks is subtracted from B6)
Hours worked for the day is B6
Complicated Summing And Counting Same Time
Jun 4, 2014Find the attached excel sheet : Example Statistic.xlsx
I am trying to calculate the crew hours from a database I created .
I entered the sumifs formula but i entered many argument .
The block time should be calculated based on the month and to calculate only for specific name for each crew .
The formula should be involve in the colorful cells only ...
Complicated IF Function With Integrated VLOOKUP?
Feb 14, 2014 I am trying to automise a workbook entry that Allocates Unique Invoice numbers depending on the job type and so that each job is only allocated one Invoice Number. Column A contains the Unique identifier, the customer name; Column C contains a drop down box with the 3 options for invoice type; "Monthly", "One off" and "Job"; Column D contains the Invoice number.
Assuming data has been entered up to row 10 I am trying to write an IF formula in column D, with the 3 conditions:
IF C11="Monthly" then D11= Vlookup for Unique Identifier, the customer name in A11; table array is A1:A11; Col_index_num is an earlier Invoice Number in column D that belongs to the same customer name, and D11 is left blank
2nd condition
IF C11="Job" then D11= Vlookup the Unique Identifier, the customer name in A11; table array is A1:A11; Col_index_num is an earlier Invoice number in column D that belongs to the same customer Name, and D11 is left blank
3rd condition
IF C11="One off" then D11= one value above the maximum (D1:D10)
Example:
Name (unique ID)DateInvoice TypeInvoice Number (IN)Explanation
White 1/2/14One off100 Initial (IN)
Brown 2/2/14Monthly101 Highest IN + 1
Smith 3/2/14Job 102 Highest IN + 1
Smith 4/2/14Job Blank, as it is the same Job as on 3/2/14
Brown 5/2/14Monthly Blank, as only gets invoiced once a month, with one IN
Peters 6/2/14One off103 Highest IN +1
Brown 8/2/14Monthly Blank, as only gets invoiced once a month, with one IN
Results Table To Complicated To Read
Nov 5, 2008I have attached the dataset. I need the data table to show the total numbers per year with the % change year on year, A column in this table of a year end projection figure for 08/09 and the % change. And finally add a line graph to show each complete year and the 08/09 projection as a comparator.
View 5 Replies View RelatedComplicated Data Lookups / Ranks
May 3, 2013I am building a dashboard for a project, which is going to be on a separate worksheet for easy printing and providing only needed data from my database. I have attached a small example in the format I will actually be using as I think the formula is going to be specific to my dashboard.
Attached file, you should see a column "Rank" highlighted orange, with certain rows being numbered. My objective is to display certain data from that particular row, and display it (scroll to the right to see the dashboard display layout) on the dashboard. I think my main goal is to have the specified data displayed in ranked order 1-10 from the top down, and I don't know where to start with that....the other possiblity is to just have the specified data display in ascending order of the zip codes as long as something other than <blank> is in the "Rank" field.
Example.xlsx‎
Complicated Find Value Delete Row Macro?
Feb 15, 2014In column B I have a list of blank cells and cells that contain values. I want a macro to search for 'Jobs' and if it finds this word in column B then delete the entire row where it is find and also delete the row below it if column B is blank and also delete the row below again if column B is blank (up to 8 rows below where the word is find at most will be blank)
When it eventually finds a cell that is not blank, then exit sub.
Complicated If Then / V Lookup / Match Statement
Dec 6, 2005I need a formula that would be able to calculate the distance from the start
of a code to the end of a code. An example table is below. The start and
end of a code is indicated in the CONT. column. (Start of first code = S1,
end of first Code = F1, Start of second code = S2, end of second Code = F2,
etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start
& Finish Codes. Two conditions need to be met before the length (in DIST
column) is to be calculated, the Report numbers and the Codes must be the
same for both rows.
ABCDEF
1S1S2
2REPORTDIST.CONT.CODEF1F2
31005S1B
410010S2A
510012F1B
610014C
710016F2A
82002B
92004S1B
1020010F2B
In the table above:
- Code B in report 100 would go for a distance of 7 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).
Getting Complicated Formula To Calculate Only If NOT (ISBLANK)
Dec 28, 2011H4 is a date/time stamp I have saved as a macro. Returns 12/28/2011 10:47:00 AM.
I4 is the same macro and returns 12/28/2011 10:48:00 AM
J4 calculates the difference between the two (I4-J4), but only recognizes business hours and business days (Monday-Friday, 8:00 am to 5:00 pm)
I only want J4 to calculate if I4 is NOT BLANK.
These are in a table so J4 is trying to calculate when there is data in H4, but not I4, and returning a large number like 981583.22
When I try to apply IF(ISBLANK) logic to the formula in J4, I get an error that it exceeds 255 characters, even though it works fine if I am not trying to put the IF(ISBLANK) logic in.
Here is the formula in J4. I want it to automatically calculate if there is data in I4. Otherwise, I want it to return 0.
=IF(AND(INT(H4)=INT(I4),NOT(ISNA(MATCH(INT(H4),HolidayList,0)))),0,ABS(IF(INT(H4)=INT(I4),ROUND(24*(I4-H4),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(H4+1,EndDt-1,HolidayList),0)+
INT(24*(((EndDt-INT(I4))-
(H4-INT(H4)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(I4-INT(I4)))-24*DayStart)+
(24*DayEnd-(24*(H4-INT(H4)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
Complicated Array Formula - Where N Is Finite
May 29, 2012I want a formula to do this... (x1*z1+x2*z2+x3*z3+....+xn*zn) where n is finite.
x1=a1
x2=a1+a2
x3=a1+a2+a3
xn=a1+a2+a3+...+an
z1 = 1.05^1
z2 = 1.05^2
z3 = 1.05 ^3
zn = 1.05^n
is this possible?
Macro- Complicated Data Sorting
Jul 28, 2009here i have results from a race. the racers have numbers in the format of a20,c51 etc
then the name of the competitor and car make model.
then some times that they run. in the format of 11.80 secs the following times have either thousands or hundredthousths of a second which i only want to comma seperate the 2 decimals before and after so a 12.3456 will be ,12.34,ignoring the 56 then the next result and so on.
so i want to comma seperate the a20,andrewspeck,vauxhalltigra,11.27,13.36,12.87,12.91 etc etc
now the name and vehicle will cause huge sorting head ache so leave those together i can seperate them manually.
.
A7,Andrew Speck,Vauxhall Tigra,11.27,13.36116,12.87113,12.91124,12.14134,11.37129,11.27128
2A4Iain BurtVauxhall Astra11.8012.1011311.8811411.8211511.80120
A2Chris SuttonVauxhall Corsa11.9514.1911413.8811212.4612412.1511511.9511814.08112
A17Andy NichollsRover 200 Turbo12.0812.0811612.8211712.50119
1A4Iain BurtVauxhall Astra12.0812.0811212.5812312.4611612.5311712.2910512.18111
A4Iain BurtVauxhall Astra12.1313.5811512.6111712.1811012.4811512.1311112.29113
1A8Gareth BirchVauxhall Nova12.1412.1411612.769912.18119
A8Gareth BirchVauxhall Nova12.1912.9811412.3811012.4111513.009612.4611212.19114
1A7Andrew SpeckVauxhall Tigra12.2612.26131
A10Errol HuellHonda CRX Turbo12.3613.3211513.2912315.726912.3610822.3652
1A14Westan FrickerFiat Coupe Turbo12.7812.78110
A14Westan FrickerFiat Coupe Turbo12.8414.8411513.7411413.4011513.3011513.0511312.84109
Creating Complicated Formula With Letters And Number
Apr 8, 2014I have been trying to create a formula that will save me DAYS of messing around at work.
What I am trying to achieve is to have a sequence of numbers as follows:
BNA01A01 to BNA01A09 then have it change to BNA01B01 to BNA01B09.
This needs to be repeated for all letters to BNA01I09.
Then this sequence needs to be repeated to BNA12.
The last thing is for me to be able to change the formula in order to implement the same sequence on a separate sheet for BNB01A01 - BNB12I09 to BNL01A01 - BNL12I09
Complicated Conditional Formatting With Multiple Columns?
Apr 22, 2014I have what I think is a pretty complicated issue. At my job, we have a running and constantly changing list of jobs that we have to work on throughout the day. The job management system is really unwieldy and it’s often hard to tell what exactly we have to do today and what can wait until tomorrow. So a few times a day, I export the list to an excel doc, which I’m trying to set up with conditional formatting to highlight as green the stuff for today, ghost back what can wait, and leave white and blank the cells that don’t have any info in them. I but the attachment system wasn't working for me, so I can't provide the example I've been working on.
So far, I’ve managed to set up a formula in the conditional formatting that takes into account column C, but I need to take into account information in columns B, C, and D to determine if the job line should be highlighted or dimmed. Column B is the job's title and it is just text, column C is the Begin date, and column D is the due date. here’s what I need the conditional formatting to do.
If the begin date in column C is Today at 9:00am or earlier, and the due date in column D is today, I need to highlight the column. I managed to figure out the first part of this formula, but don’t know how to keep the line from hilighting if the due date is tomorrow: =$C2<=TODAY()+TIME(9,0,0)
The second part is that I need to hilight jobs that begin and are due today (have today’s date in columns C and D), regardless of what time they have if the task title in column B starts with “Rework Request from PS” or “Rework Request from QA"
Ideally, I’d like to change the text color to something nearly white to sort of hide the jobs that don’t need to be done today, and I figured that part out based on the formula I did manage: =$C2>=TODAY()+TIME(9,0,1)
apply this to the whole sheet so I can paste the report into it a few times a day without having to rewrite the formulas in a new sheet every time, and the number of items does change throughout the day. However when I do, the empty cells turn green as if they’re due today, and I can’t figure out how make them stay blank.
Copy / Paste Complicated Table Between Sheet
Apr 20, 2013I have a very complicated table that has a bunch of links to other cells on the sheet. I would like to duplicate this table onto another sheet in my workbook. However, when I try to do that, the links also change when I paste it on the new sheet. Is there a way I can keep all of the links to the original page after I have pasted the table on the new sheet?
View 4 Replies View RelatedComplicated Macro To Copy And Sort Data
Apr 23, 2008I got a complicated macro that I need some help on. I want to write a macro that does the following (for clarity, I have attached an excel file).
I want to write a macro that automatically copies the rows in worksheet 'boekhouding' to their corresponding worksheet. The name of the worksheet, to which the data of each row has to be copied, is specified in row I (Beware: if it is not in row I, it is in row H). The names of all destination worksheets are given in the blue cells in worksheet ‘rekeningen’.
For example, row 2 (of worksheet 'boekhouding') should be copied to worksheet 'ABD'. Row 3 (of worksheet 'boekhouding') sheet be copied to sheet 'bestuur'. Row 4 (of worksheet 'boekhouding') should be copied to sheet 'Commissiekosten gala'. Etc for every datapoint (row) in worksheet 'boekhouding'. The number of datapoints will exceed 300, so manually doing this will be too time-consuming and unreliable.
Setting Long Complicated Formula To Cell Via VBA?
Jun 12, 2014I want to set formula to cell S1 via vba.
This is the formula: =IFERROR(VLOOKUP(H3;'[VATCompanies.xlsx]1'!$A:$B;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$D:$E;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$G:$H;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$J:$K;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$M:$N;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$Q:$R;2;0);I7))))))
Complicated -different Combinations Of 2 And Summing Data For Each Combination Of 3
Jun 3, 2006I have one last question. is there a way to make this a little more complicated? for every two possible combinations of names, i have a value. is it possible to create a fourth colum, in which the sum from the three values is calculated?
for example-
12 different letter names (a-l)
after running the code derk sent, i now have a-l in cells A1:A12
I also have every combination of 3 using these 12 names in columns C D and E
take for instance the combination of three names (a, b, c). i have values for ab, ac, and bc in columns G, H and R respectively. can these values be summed together and averaged in the fourth column?
Poor Export Formatting Leads To Complicated Macro
Jun 12, 2008I'm going to attach a file I'm working with so you can see my issue direction but here is the background:
- file you see is an excel export from another software. This is as clean as it gets.
- what I am trying to accomplish is take the data for each person listed and movie it to it's own sheet via a macro. Creating the sheet name based on the name of the person. I've completed that task before with some help here, but never with data formatted like this.
- does anyone have any options they can think of? I've tried working with the two cells that make up the page number and basicly the move off that but can't seem to get that to work.
- would anyone suggest any formatting changes (while keeping all data integrity) before trying a move?
VBA Code To Find Dups, Highlight Them With Different Colors, Pretty Complicated
Aug 29, 2008I'm trying to find some vba code that will find dups on my worksheet and highlight them in a special way: first dup = bright yellow, second dup = bright green, third dup = bright red, and if there is a forth, fifth or sixth dup, then just increment color number to next one, than next, etc...
I would like for the code to run as soon as there is a change on the worksheet (when I finish entering data in a row). I think I already have the right code to do that:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E9:E10000")) Is Nothing Then
Call dupSearch
End If
End Sub
I tested it out, seems to work as it should.
My major problem is that I need the code to check column F9:F10000 and compare the cells all along that column. That column concatenates 3 other cells in that same row (lastname&firstname&employee#), so if I run my dupSearch on that column only, I should find only real dups, and not just 2 people with the same name.
I also need to make sure it doesn't find empty cells, thinking they are dups. I would like to highlight column A to E of the row that contains the dup, and not just the cell in column F.
I have tried many other codes that I have found on the forum, but none of them work right with what I'm trying to do, and I just don't know enough VBA to make the changes I need.
Not Responding: Added A Formula To A Spreadsheet With Som Complicated Formula
Jun 1, 2006I recently added a formula to a spreadsheet with som complicated formula. It worked ok and I saved the sheet. Now it takes 5 minutes to open the sheet and when I try to do anything,like delete the inserted column the program locks up giving a no responding message. I can do without this column if I have to.
View 4 Replies View Related