Excel 2010 :: Calculating Value And Posting Total To 1 Of 4 Fields Dependent On Content Of Another
Jan 9, 2014
Excel ( 2010 ). I am creating a Sales Leads spreadsheet. Within the spreadsheet I have a the following relevant fields.
proposed sales value field ( F2 ), Estimated Close - which will be Q1,Q2,Q3 or Q4 ( G2 ), % Probability ( H2 ) & Q1 ( I2 ), Q2 ( J2 ), Q3 ( K2 ) & Q4 ( L2 ).
F2 = 150
G2 = Q2
H2 = 10%
I want to calculate the actual value of sales based on %prob and put the total into the correct field ( I,J,K or L ) based on what field G2 says. So in this case 15 into field J2. I have attached an example.
View 4 Replies
ADVERTISEMENT
Aug 23, 2013
Looking to create a calendar in excel and auto-populate the content with specific fields I enter. I know how to create an excel gantt chart but I'm really looking for a calendar view instead of a series of columns with dates. I reviewed the original post on Auto-Populating Excel Calendar but was confused by the instructions. (1) create a calendar in excel and (2) show me how to populate the calendar with fields like (dept and project name) into the dates on the calendar? I have two dates that are important (a due date for the project and when that project will be published). Is there a way to have both dates show info otherwise the most important date will be the publish date?
View 1 Replies
View Related
Jul 9, 2012
Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).
I am using excel for windows 2010. The macro that i have so far is below.
Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value
[Code] ........
View 1 Replies
View Related
Aug 7, 2014
I have two columns of data; one is in inches, one is in metres. If someone is inputting data from an original source that has the data in metres I want excel to automatically calculate and input the data into the inches column. Additionally, if they input the data from the source in inches, I want excel to convert it to metres and input into the other column automatically. The conversion factor from inches to metres is * by 0.0254. I am no expert but after looking for this on the internet I am sure this requires some sort of VBA event code (although I don't really know the first thing about VBA). The excel columns will be in the same worksheet. I don't have a circular reference like I would do using normal Excel formula? I am using Excel 2010.
View 2 Replies
View Related
Oct 29, 2013
Creating a spreadsheet in Excel 2010 and am creating the dropdowns from a separate sheet in the workbook. There are no spaces and I don't know why I keep getting the "The Source currently evaluates to an error."
I am entering the Data Validation to reference the first cell I need "=INDIRECT(H3)"
I have put the spreadsheet on dropbox. When you select the "Sequencing Platform" drop down, it need to give just the dropdown for the platform selected.
[Code] ..........
View 2 Replies
View Related
Feb 10, 2014
I have an Excel spreadsheet that where the headings start in row 5. Data starts in row 6.
The spreadsheet is updated frequently. The headings are always the same but the number of lines of data changes.
D5 = Units1 (field contains numbers or is blank)
E5 = Units2 (Field contains numbers or is blank)
I would like to insert a column to the right of Column E and add D + E in the inserted cell. I would like F5 to be called NewUnits.
Some cells in Columns D and E are blank. No cell in Column A is blank. (If cell A6 is the activecell and you press CTRL + Down Arrow Key -- you will get to the last cell with data in Col A. That is not the case if the activecell was E6 as there are blanks.)
I am using Excel 2010.
View 6 Replies
View Related
Aug 15, 2012
I have attached a work book example of what i am trying to do.
Column D is what i originally did in terms of the formula and now i have to have a column display text dependant on what is in column A to C.
I was trying this formula =IF(b2>a2,"Start Target Missed",IF(c2="","Failed","Tracking")) However if this isnt working.
Is there anything i can do to change this?
View 3 Replies
View Related
Jan 31, 2013
Exel 2010 cells formatted to Time
need to look at h1 and h2 if no data ignore
other wise add data together if total exceeds 08:45
then deduct 45min from h1
if combined the are less than 8:45 then h3 must equal 8:00 by altering h1 only
Example
h1= 5:00
h2= 2:00
h3= 8:00
h1=
h2=
h3=
h1= 8:00
h2= 2:00
h3= 7:15
View 3 Replies
View Related
Dec 12, 2013
I want to edit phone numbers in format 1112224444 to 111-222-4444.
My best shot has been...
Excel 2010 (Mac)
Sub EditPhone1()
ActiveCell.Offset(0, 0).Select
With ActiveCell
.Value = .Characters(1, 3) & "-" & .Characters(4, 3) & "-" & .Characters(7, 4)
End With
End Sub
I've tried many variations, no luck.
View 2 Replies
View Related
Jan 21, 2014
This is in Excel 2010
I have, what I believe a fairly complex request.
I have 3 fields that will be entered manually per row
Frequency in weeks = FQ Example FQ = 4 weeks
Duration in hours = DUR Example DUR = 3 hours
Start Date in Date Format = SD Example SD = 01/15/14
I have a series of columns starting at December 30, 2013. This represents the week of December 30th 2013.
It continues on until I hit the last week in December of 2015 which is 12/28/15
There are multiple rows with the 3 entries listed above (FQ, DUR & SD).
I need a function or macro that takes the Start Date (SD) for the specific row and finds it's location in the series of columns (for the example of 01/15/14, it would land on week 01/13/14). The program would enter the Duration (DUR) in that Column's Row (if that makes sense).
The program would then add the Frequency (FQ) to the Start Date (SD) and look for the next column (or Week) that it would fit in and once again enter the Duration (DUR) in that cell.
It would do this until it hits the 12/28/15 end date.
View 1 Replies
View Related
Apr 3, 2014
In our sql server table, we have a field of type DATETIME. This field is populated by a vendor product with the elapsed time of an event.
We created a SQL Server view of this column and other columns of info joined together from several tables.
Then we create in Excel 2010 a new spreadsheet with a data connection to the SQL Server, instance, and view that we created.
Excel 2010 displays each of the columns in our view.
The elapsed time column appears as "dd hh:mm:ss". However, when we click on a cell to look at its formatting, Excel lists it as general.
We want to calculate the average time in this column.
We go down below the data, click on a cell, and insert =Average(C2:C17215) and we get an error saying dividing by zero.
We have 17,000+ rows of data, so there is no divide by zero math going on here. Each row in this column has data, so there are no blank cells.
We have spent some time web searching. We then tried various methods of calculating this. Nothing has worked. In most cases, we get the divide by zero. We tried some solutions that mention using an array forumula, but that just changed the error to an invalid value error.
View 1 Replies
View Related
May 16, 2014
I pulled a set of financial statements from a database into Excel. All of the values are hard coded but I would like to make them dynamic, while maintaining the original content. For example, If cell C3 is a revenue figure for 2012 ($1,000) I would like to link it to cell C5, which will be a currency conversion rate (2.0). So I would like cell C3 to read =1000*C5, which would equal 2000, and for cell D3 to equal 2013 sales*D5, etc. I would also like this to go across a specified range so that I can also change operating expenses, taxes, etc over a number of columns (dates) that I define, all linking to the conversion rate for the given year. Having another tab where you link and anchor is an option but I would prefer something in VBA where I can qickly include this functionality with new workbooks that I download.
I am using Excel 2010.
View 5 Replies
View Related
Apr 10, 2014
I Need to Understand Running Total Sum Formulas. I'm using Excel 2010. and I'm typing in the basic correct formula to arrive at a running total for each ajacent cell to the left of it.
I put in the correct formula in Cell (B1) of Sum=(A1)
I then put in the correct formula in Cell (B2) Sum=(A1:A2) I then highlight the A1 part of the formula with an F4 Key to lock it in.
I then drag the B2 Cell all the way down the excel page to capture all of my running total coming from the (A) Column just to the left of my formula.
Why even though I'm getting the answers I want in the running total does it put an error message in each of the correct answers in each of the cells in the (B) column where I put my formulas.
I tryed to delete the error message spot but I don't know how to delete all of the error spots without going into each cell one by one.
How can I get rid of all the error message out of each cell without having to click on each cell one by one to do it when I might have over 500 or more cells to click on.
View 9 Replies
View Related
Jul 23, 2014
I am trying to take scheduled start time and scheduled duration in h:mm and auto populate the scheduled minutes in the corresponding time blocks.
Example:
Schedule ID
Start Time
Duration
8AM
9AM
10AM
11AM
12PM
1PM
2PM
3PM
4PM
[Code] ....
The first 3 columns are derived from a data table and I am trying to auto populate the minutes scheduled in the time blocks using conditional formatting or some other mechanism.
I'm using Excel 2010
View 4 Replies
View Related
Apr 15, 2014
As you can see in attached file (hopefuly you can see it) pivot table Summed sales in 2014 and 2013 in column GRAND TOTAL. Insted of summing this two years, I would like to show difference between them, but keep this layout!
PROBLEM.jpg
View 4 Replies
View Related
Mar 17, 2014
I'm an Excel newbie using 2010, but have been given a rather daunting task. I have a lot of data organised in a table, something such as:
Column A: Name, Column B: City, Column C: Date Entered
I can obviously filter this table to show the values I need, but I need it to automatically tell me the total amount of people entered per week, per city on a separate worksheet.
I understand I could use a pivot table for some of this, but I want to further automatically manipulate the data once I have the totals (for example, adding a financial value for each, etc) which I'm not certain how to do with a pivot.
So, ideally what I would like is a formula that says total entries in Table 1 that are marked Sheffield in Column B and have a value including or between 01/03/2014 and 07/03/2014 in Column C.
View 3 Replies
View Related
Sep 7, 2012
I need a method or function that can count the TOTAL number of pages to that will be printed. I don't need anything with HPageBreaks or Get.document(50, ""sheet name"")") for these don't seem to work on my worksheet.
All they seem to do is give me a number lower then the number of pages that will be printed and they are dependent on where the active cell is.
Here is my code that does not work.
Code:
Agreement_Page_Count = ExecuteExcel4Macro("Get.document(50,""Signature Page"")")
and
Code:
Function HowManyPagesBreaks(sSheet As Worksheet)
Dim iHpBreaks As Integer, iVBreaks As Integer
Dim iTotPages As Integer
With sSheet
[Code] ........
Both codes return 2 when there are 3 pages that can be printed. Unless I pause the code select the cell at the bottom of my sheet (it ignores it if I use use a macro to select the bottom cell) and then continue my code.
I've tried it with and without the entirerow.hidden.
Is there a way of bypassing everything and using a marco to open up page preview and getting the total number of page to be printed? I'm using excel 2010
View 1 Replies
View Related
Oct 22, 2013
I have a large amount of data that is from an events file. When I enter it into excel, it almost maxes out the row count (excel 2010) The data basically lists Event, Date/Timestamp, Call ID, and a few other details. Im trying to get a total of specific events and if they were successful or not, so the other columns arent of concern. For instance, there is an event called searchbyphone, a later row in the data will include searchresult found or notfound. The only way to pair these up is by the ID number which is unique per call.
So in the example below, ID 123-45-789a was a search by phone and successful (found). The Date/Time field is down to milisecond. If this were just adding up all the various events and then totalling the searchresult found/notfound, it would be simpler. However, Im only interested in certain events and found/notfound is used for many other events.
Using the data below, what is the best way to pull out an event (say the total number of searchby phone) and then know how many of those were found and not found (when the information needed is on 2 seperate rows and only the ID would be the same?)
To through a wrench into it, there can be several events with the same ID becasue the ID is unique to a call - a caller can search by a few variables. There will be the same amount of found/notfounds though with that ID. (meaning as a caller, I can do a few searches and all of these will be in the event file with the same ID) In this case, the number of requests (events) will match the number of results (found/not found).
What Im trying to accomplish is to get a total of searchbyphone found and not found, So in this example I would have searchbyphone found = 1 and searchbyphone notfound = 0.
ID
Event
Date/Timestamp
123-45-789a
searchbyphone
10/7/2013 12:52:38
987-65-432b
searchbycity
10/7/2013 12:52:39
123-45-789a
searchresult.found
10/7/2013 12:52:41
874-35-123c
searchbyzip
10/7/2013 12:52:42
987-65-432b
searchresult.not found
10/7/2013 12:52:47
View 5 Replies
View Related
Apr 30, 2013
Using excel 2010: I am calculating a dollar weighting with one isolation factor, the state where the sale took place. My current formula is below. Each dollar amount in AG3:AG2000 has a corresponding weighting in AB3:AB2000. The results are isolated by the corresponding state in the range E3:E2000.
I want to add in an additional isolation, the month when the sale took place. The months are located in H3:H2000. Where in my current formula would I insert the isolation for the month?
=SUMPRODUCT(--(Sales!$E$3:$E$2000='2013 NB'!S8),
Sales!$AG$3:$AG$2000,Sales!$AB$3:$AB$2000)/
SUMIF(Sales!$E$3:$E$2000,'2013 NB'!S8,Sales!$AG$3:$AG$2000)
View 9 Replies
View Related
Sep 19, 2013
Windows 8, Excel 2010
I have XYZ Coordinates for a continuous 3-D line that has numerous segments. I want to input a distance along that line, and have it create the XYZ coordinates at that point. See Image for reference.
Row 2 is my start point - I input the initial coordinates here - this point is the origin of the 3-D line
Column B is where I want the calculated Y value to go for each point
Column C is where I want the calculated X value to go
Column D is where I want the calculated Z Value to go
Column E is the how far along the 3-D line that the (to be calculated) point should be at. (MD1)
Columns G, H, & I are given to me, and I use this data to generate the coordinates in Columns J, K, & L
Column O is the cumulative length of the line at that coordinate. (MD2)
Basically, I had planned on writing a formula to:
Find the coordinates of the point who's MD2 (column O) is before the desired point's MD1 (column E)Find the coordinates of the point who's MD2 (column O) is after the desired point's MD1 (column E)Subtract MD's (column O) to get the length of the segmentFind the distance along that segment that MD1 (column E) fallsUse that distance to traverse along that line to the desired point.
View 1 Replies
View Related
Jul 31, 2014
I'm trying to create a form where you can select names from a list dependant on what depot has been selected
On Sheet 1 along the first row are names of depots, under each of these are a list of names (number of name varies)
On Sheet 2, C2 is a data validation list, with source being =Sheet1!A4:E4
Is it possible to make C3 show a list of the names that's dependant on what's selected in C2
View 2 Replies
View Related
Feb 26, 2014
I am trying to display data on a chart and dynamically change the items to display by manipulating the filters. What I cannot figured out is how to display the Pivot Table Grand Total column on the chart. This is the total that the pivot creates and there is no total field in the data. Hope this explanation I gave is clear.
View 1 Replies
View Related
Mar 27, 2014
formula to take a value from one sheet and add to it in Excel 2010? I'm naming the sheets but not necessarily numerically. I have done it by manually entering the sheet name but would like it to figure out the sheet name automatically based on where the sheet is located in the workbook. That way I can copy the current sheet, rename it and still have it update properly with a running total. Here is what I have now: =D7+'011514'!E7
View 2 Replies
View Related
Dec 19, 2013
I have a single work book with 8 sheets (I am using Excel 2010 BTW) and I am trying to find a total of times a word appears across all the sheets in column "C"
I found this formula on another thread. =SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!C1:C1000"),"="&H3)) with an example. I made the changes that I needed for my purposes
This worked but only after I renamed the sheets to Sheet1, Sheet2, etc.
Is there a way get the same results from the above formula if all the sheets are named after our reps? Example: sheet1 is named Dan, Sheet2 is Nick, etc?
View 2 Replies
View Related
Feb 9, 2014
How do I enter a formula in excel 2010 that will give the total amount of times each number is in this group. Example : how many times (total) the number 12 showed up , how many times the number 27 showed, and so on for each number that is in the entire group of numbers, from 1 to 80 .
Here is the page I will copy and paste into a workbook sheet from the internet that i want to evaluate the times each number was called.
Very new to all this , I am a bit aged and need not to learn excel A to Z, just need to know what correct statements /formulas have to be entered to do what i desire.
I am only interested in the total count of the small bold numbers 1 to 80, each single digit 1 to 9 and double digits 10 to 80 will be in their separate cells. The large bold three digit numbers and dates/times will have to be erased (manually) before the calculation takes place.
298
2/9/2014 12:28:02 PM
37
77
[Code]....
View 6 Replies
View Related
Jan 8, 2014
I'm trying to create a sheet in Excel 2007 that organises shipments, the file is normally pretty large so I want to create a macro which does a few things, so when printed it's easier to work with. First one is to make a gap when items change in Column B and Column F, I can use the below code to make the gaps for one column but not both without making a separate macro. Is there a way to make the gaps on changes in columns B & F on one macro? (I have an example nearer the end)
Dim Rng As Range
Dim x As Long
Set Rng = Range("F12:F" & Range("F65536").End(xlUp).Row)
For x = Rng.Rows.Count To 2 Step -1
If Rng.Cells(x, 1).Offset(-1, 0).Value Rng.Cells(x, 1).Value Then
Rng.Cells(x, 1).EntireRow.Insert Shift:=x1Down
Rng.Cells(x, 1).EntireRow.Insert Shift:=x1Down
End If
Next x
After the data is separated I wanted to insert totals of the weights when there is a spacing, and a counter which stops after each spacing and restarts when the next items start.
Here is an example of the data I have before the Macro with Columns.
Column B
Column C
Column F
Column I
Destination of order
booking ref
Size
weight of orders
[Code] ........
This is how I would like the date to look like after the macro.
Column B
Column C
Column F
Column I
Count
Destination of order
booking ref
Size
weight of orders
[Code] ..........
View 4 Replies
View Related
Aug 1, 2014
My boss has asked me to find a way to charge a mark-up on our project budgets based on a certain budget tier system ($0-1000, $1001-5000, $5001-10000, $10001+) He doesn't want a general percentage. Is there a way to do this is Excel?
He was talking about Boolean functions, but he was also used to using Logic. I do not know logic or if there is any such function within Excel.
View 2 Replies
View Related
Jan 12, 2012
I'm creating a restaurant rota spreadsheet and I need to calculate the hours for each waiter, which is easy enough. But I've got to include stand-by shifts and cover shifts into the rota for the staff and I'd like the formula to ignore the "standby" text, etc in its calculations. I know you can use the =IF(... function, but that only works with one value. As well, the standby shifts will change from shift to shift and week to week, so I need one formula that might encompass all these needs... if there is one!!
An example of my rota for you to see:
MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY SUNDAY
Total hours for week START FINISH START FINISH START FINISH START FINISH START FINISH START FINISH START FINISH
10:00 17:00
10:00 17:00 12:00 16:00
Lunch Hrs/Wk 18:00 #VALUE! 19:00 24:00
standby standby 19:00 24:00 standby standby o2 Cover o2 Cover
Dinner Hrs/Wk #VALUE!
View 5 Replies
View Related
Dec 23, 2013
excel 2010. This workbook has 4 worksheet(Process Engineer,OSBL,OSA,Lab Operator) I want to know what is the best excel formula/function to summary this 4 worksheet.
Example:I want a formula/function to summary all the statement from 4 worksheets and total number of answer "1" per statement from 4 worksheet.
Sample Statement below
"Demonstrate Interpersonal (People-to-People-) Skills" Question:What is the formula if above statement contains this statement in 4 worksheet?As i checked the total is 4 then What is the formula to get all total answered ICC on this statement from 4 worksheet?
View 2 Replies
View Related
Jan 13, 2009
I have 11 worksheets in one workbook, the last worksheet is a total page.
I'd like to run a macro that copies the information from each sheet and pastes it into the total sheet. My range on every sheet begins at A2, but the end of the range is unknown.
All I have so far is trying to loop to a new empty cell on the total page (coded in a module - is that right?):
View 8 Replies
View Related
