Excel 2007 :: Select Team Leaders Name And All Agents Reporting
May 17, 2013
I am looking for a formula or VBA code that will allow me to Select a Team leaders name and all the agents reporting to that team leaders would populate. I have the first part of that problem solved but its getting the agents names to generate uniquely that I find problematic.
Below is a step by step example of how i intend it to work
Step 1
I select the Teamleaders name
Shelaine
Once that has populated the Team Names automatically appear
Shelaine
Score
Andrew
20%
John
30%
Mark
40%
Sarah
50%
June
60%
The scores will populate on its own as I will include a Vlookup formula to generate the scores, Its just the Agents names. I am using Excel 2007
View 2 Replies
ADVERTISEMENT
Oct 24, 2008
I have Microsoft Office Excel 2007 Power programming with VBA in which this website was listed as a good reference site. I am working on a spreadsheet where I want to select the last non-empty cell in a column and the column always has some cells that are empty, though it is NOT the last non-empty cell in the column. This spreadsheet is automatically generated and I want to change this particular column to all one format and all one number, 0042. This spreadsheet is then loaded into another system via tab-delimited txt file, so this number is meant to serve as an indicator. Sounds like I gave too much information, just wanted to be clear.
View 9 Replies
View Related
Oct 29, 2012
Is there a way to select completely empty rows in Excel 2007 (Win 7)? I have found directions for highlighting empty cells (f5, Special, Blanks...), but sometimes the cells selected will be in rows with other filled cells in them so that doesn't really do what I need.
View 7 Replies
View Related
Jun 2, 2014
Essentially all I want to do is create a procedure/function in Excel such as =SQLdata(3), where 3 could be customer ID and then the function would make a connection to SQL and perform the SELECT etc procedure and return the CustomerName in the cell with that formula.
I have excel 2007 and MS SQL where my data is stored. I understand that I need to make the connection to my SQL database, but I really do not know how to do this.
View 4 Replies
View Related
Oct 27, 2012
I have linked Combo Box to Pivot Reporting Filter through VBA. When I Select Combo Box Value, same will be filtered in Pivot table. Combo Box values are constant where as Pivot report filter may not contain all the values which are in Combo Box. If Combo Box value is not available in Reporting filter of Pivot it should throw up msgbox and should select first value of Combo box.
View 3 Replies
View Related
Jan 7, 2014
I have spread sheet and values in some columns have highlighted in color.What I have to do is I need to quickly separate the highlighted data and put in the other spreadsheet.
View 5 Replies
View Related
May 3, 2012
I would like to know if it is possible with VBA in Excel 2007 to select objects based on size (or above a certain size).
I would like to select all the picture objects but not the small checkboxes on my worksheet.
I know that I can select the items one by one with home -> find/select -> select object but how I could make this "find/select" conditional.
View 7 Replies
View Related
Sep 26, 2013
My excel version is 2007 and i am on WIN7 64bit
I have a workbook with VBA which was working fine earlier. Lately i have been getting
"Runtime error 1004" Select method of button class failed.
I am adding buttons dynamically in a loop in a sheet and what surprise me is , i go to debug mode when error pops up and it points to below line
MySheet.Buttons.Add(ActiveCell.Left - 5, 25, 20).Select
but my code is running in a loop and i can see that atleast 20 buttons were added and it fails to select when it try to add this iteration. It is completely confusing me.
My entire below code is in a loop
Code:
maWrk.Cells(xIt1, yIt2).Select
mySheet.Buttons.Add(ActiveCell.Left - 5, 25, 20).Select
Selection.OnAction = "callMe"
[Code]....
I also notice that when it fails, on select method .. the button was actually added to the sheet with caption/name as "Button 65536" but then fails to select it. What makes the select to fail after adding the button? Could there be anything special with Button 65536?
Is there any better way to code the adding button and setting action and name for excel 2007?
note before entering the loop i am deleting all the shapes with myShape.Delete which name matches "btRun"
I also want to highlight that i don't have 65536 rows in my sheet. I just have 200 rows. and when this error happens it is on row 150 so firstly i wonder why it is naming the button as Button 65536. I would assume it will start with 1 and go on increments.
What makes excel to fail to select an added button in VBA?
View 6 Replies
View Related
Nov 6, 2012
I am a Microsoft Excel 2010 user and am trying to create a usage report for a website that I work on. I have been compiling the usage reports into one spreadsheet. The columns I use are Month, Device, Language, Title 1, and Title 2. I can filter each of these rows and it shows me the number of records found. Each row on this spread sheet refers to a time that someone selected something on the site. For example the row may say;
Month (filtered)
Device (Filtered)
Lan (filtered)
[Code].....
How do I automatically populate a table on a separate sheet with the number of records found for a certain combination of filters applied. So if I'm looking at monthly trending I want to know how many times the stress test was started in May, June, or July, but automatically using info from the multiple filters.
What is a formula I can use to compile the filtered data from above.
Jan
Feb
Mar
Aprl
May
Start
=formula?
View 4 Replies
View Related
Apr 8, 2014
Problem: Have a squad of 8 players in my darts side. We have 12 games in our season and each game requires 4 players so each player will play 6 games. 6 of these games are at home and 6 are away.
I want to create an excel sheet whereby a team will be picked for each match. Each player will play at Home and Away 3 times each. And the same group of players will not play with the other players each time.
eg. Week 1- Team Players, 1, 2, 3, 4
Week 2 - Team Players 5,6,7,8
Week 3 - 1,3,5,7
Week 4 - 2, 4, 6, 8 etc.
Where to start. There are multiple variables.
View 2 Replies
View Related
Mar 7, 2009
I'm Seeking in InRegards To Working Out How To Display Multiple Leaders Within The Same Cell Based Apon There Score In A Game Of Golf, Or If This Cant Be Done Perhaps A Message Saying "Multiple Leaders" So That It Is Known That Its Not Just The One Persons Name That Is Been Displayed. I Have Attatched The Document I Have Been Playing With While Trying To Work It Out Before Making A Big Sheet.
As You Will See "Daniel" And "Paul" Both Have The Same Score, And Below In The Summary It IS Currently Only Showing "Daniel" As The Scratch Leader, Is There A Way Of Getting It To Show Them Both As A Leader, EG: "Daniel, Paul", Or As Said efore Just Saying "Multiple Leaders" Thus Having To Manual Go Through Them ( The Fist Option Would Be Better As There Will Be Approx 85 Names In The Final Sheet)
View 5 Replies
View Related
Jun 1, 2011
I have a 5000 line table I am filtering by a few columns, and I'd like to calculate an exponential trendline value.
=INDEX(LINEST(LN(R1059:R1167),W1059:W1167),1)
But I actually don't want all the values from R1059 to R1167 - I want to select only the displayed values (R1059, R1068, R1077, etc). Is there a way to select only display values to use in a formula? The problem is it would be a lot of manual work to select them all - there are 50 or so instances I would have to select 13 manual values.
I am using Excel 2007 on XP.
View 8 Replies
View Related
Dec 24, 2009
Using Excel 2007 Calendar Control:
I want to select a date of choice from a calendar in a field within an Excel spreadsheet.
I do not know how to do userforms and so that is why I wonder if I can add this in just a spreadsheet.
This is how far I have gotten so far: After doing the following, I have a static calendar setting in my spreadsheet like a text box showing the current date.
Developer
Insert
More Controls
Calendar Control 12.0
View 9 Replies
View Related
Jan 13, 2010
attached file which contains about (150) agents sheets,i need a way which will move me to the desired sheet by pressing its name from the list in the main one , i need this because i shall hide all the sheets.
View 14 Replies
View Related
Mar 24, 2009
I have a variety of products that i sell out to different agents at various levels of commissions. These range from a possible earning for the agents from 12%-30%. Is there a set formula that will take my initial buying price ie £100 and i wish to earn £5 regardless of whether the agent earns 12% or 30%. so an agent on 12% would buy from me at £119.32 and an agent on 30% would buy from me at £150
View 3 Replies
View Related
Apr 21, 2014
Is there a way to accurately compute a conversion report that has agents with multiple roles? Right now it is done this way:
Agents are skilled to two skills, sales & service. They will make most of the bookings on sales (few bookings on service). I want a report that will calculate the agents who are on sales/service bookings vs calls while persons on service/sales bookings vs calls.
Right now, because the service/sales agents get way less sales calls, you will find that their overal conversion is way higher than an agent who is a sales/service agent. What this does is drive the overall conversion rate so high that it always favour the agents on service/sales, a sales/service agent is always disadvantaged.
Is there a way in excel where i can more accurately calculate this while maintaining some degree of parity?
See report attached
I have highlighted some of the SE/SA agents that are way high
Note: SE/SA = Service/Sales SA/SE = Sales/Service
View 2 Replies
View Related
Jan 8, 2004
I am trying to build a spreadsheet to calculate referral fees paid to our agents. These are sliding scale fees and I need the formula to consider both the high and low range of the project value. For example we pay a certain percentages for sales in the following ranges:
Up to $5,000,000 (I got this one to work)
$5,000,001 to $7,500,000
$7,500,001 to $10,000,000
$10,000,000+
I assume the best way to do this is with an IF command but I only know how to use it for a single value, not a range. Also, if the statement is not true, then return $0.
View 9 Replies
View Related
Feb 2, 2009
I have been trying to create a report that involves three conditions, but so far I have had no luck using SUM and IF conditions to do this.
I have attached a file with an example of what I would need. Basically, I would need the "Resolved" and "In-Progress" quantities filled in below the "Country Report" for each respective country.
View 3 Replies
View Related
Jan 17, 2014
So I've got a workbook with three main sheets: Pipe, Fittings, Report. In the pipe sheet I've got 8 charts that are all the same but they're for pricing different types of pipe. I want to assign each line in all charts a category and then in the reports tab have a chart that will add all the prices from all charts in each category. I've tried using the VLOOKUP function but I can't seem to get it to work. I can attach the spreadsheet here if that would make things easier.
View 5 Replies
View Related
May 15, 2008
I have a 2-column employee file. Column A has the employee id and column B has the manager's employee ID. The reporting layers can get pretty deep. In this example, there are 6 layers of reporting. For example, Employee id 1200 (which is not in column A) has 2 employees. One of them (1712) has several employees, one of whom (1680) has employees reporting to them and so on for several levels.
Is there a way in Excel (2003 or 2007) to summarize the reporting structure? For instance, I need to know all of the employees that report up through emp id 1712 - not just that report directly to 1712, but all those under 1712's tree.
View 9 Replies
View Related
Oct 1, 2005
I'm trying to figure out how to get Excel 2003 to report a value from a
separate column where the value reported by a MIN or MAX function
occurs. For example, say this is my setup:
Column A: time
Column B: value 1
Column C: value 2
I want to create a function using the MIN and MAX functions for cell
ranges in either column B or column C plus I want the value that
corresponds to the MIN or MAX value from column A. Basically, I have
data in columns B and C that occurs at times listed in column A. I not
only need to know the MIN and MAX values for certain ranges in columns B
and C but also the times at which these values occur (across hundreds
and hundreds of values with multiple MINs and MAXs, so this is not
something I want to do manually across multiple workbooks).......
View 11 Replies
View Related
Mar 4, 2013
I have a 2- column data as shown below
column A
Column B
aaaa
3
aaaa
8]
I have a data validation list in a different cell that reports the texts in Column A without repetition. The task for me is to write a formular in another column, which will report all the numbers that correponds to the text i select from my data validation list. For instance if i select bbbb i want the following numbers to appeear in a given column:
9
25
34
4
21
View 2 Replies
View Related
Aug 19, 2008
I run a store, and currently we handwrite all of our reciepts - and then implement them into a "Sales Sheet" on Microsoft Excel. From there we can monitor monthly sales and targets - coincide what we are selling, and figure sellthroughs etc etc.
To make life easier I am looking at typing out reciepts in Excel - within the same workbook as the "Sales Sheet". I would save each reciept as a separate sheet, and would like to know if it is possible to somehow copy data from the "Reciept" worksheet to the "Sales Sheet" worksheet??
View 2 Replies
View Related
Dec 31, 2009
I have a 2 page excel book, 2003, that runs a vlookup off a list from the 2nd page of the workbook. It is a long listing of information. It returns successful info in most of the cases, but in some instances it returns #n/a in one instance where it returned the correct info in others as in:
12345 = dog
12346 = cat
12345 = #n/a
Some instances don't report the corerct info at all while others only report the correct info some of the time like above where 12345 = dog and in some cases it doesn't turn out dog as the anser to the vlookup.
View 9 Replies
View Related
Feb 22, 2010
Assuming the below performance report:
Day 1
Actual 1
Target 1
Achieve 1/1 = 100%
Day 2
Actual 1
Target 1
Achieve 1/1 = 100%
Day 3
Actual 9
Target 10
Achieve 9/10 = 90%
Which method do you use to report the Achievement for the 3 days?
1. Do you take the average of 100%,100%,90% to give 97%
OR
2. Do you sum Actual (Day1+Day2+Day3 = 11) / Target (Day1+Day2+Day3 =12) to give 92%
View 4 Replies
View Related
Jun 19, 2007
I operate a small music management company and I run my accounts using relatively complex Excel Workbooks with different sheets for purchase and sales ledgers, cash in and cash out etc etc. Each financial year has its own workbook. My problem is creating financial reports. For example, if I need to create a report for outgoings and incomings relating to a particular project (a concert tour for example), and if that project was split over several financial years, I find myself spending hours creating pivot tables, and then pivot tables based on other pivot tables etc etc, to just get a clean report on that particular project. What I would love to have is a single report generating system, where I can just enter the name of the project, and get a financial report at the click of a button, based on all the raw data I have painstakingly entered in the individual workbooks and sheets. I reckon I'm too busy to sit down and tackle VBA programming myself, so I wondered if anyone knew of any third party software that might help me realise my dream simply and easily. Someone has already mentioned Crystal Reports, but I don't know whether this will be overkill.
View 3 Replies
View Related
Sep 11, 2007
I have a list of all my products in the "Price Listing" sheet organised by a 3 level tree structure (small range of products shown in excel file attached).
I am trying to create a way of getting this list into an organised way (via macro) so that I can print this off and give to my customers. Sheet 1 shows an example of how it might look after the macro is run using the tree structure. I have tried to use pivot tables with no success.I am currently creating this manually each month and updating all new products, then running the prices for each customer but it is taking far to lonng to create the master sheet.
Different customers have different prices and I want to create a price list so I can get someone in the office to output the file to excel, run this macro and create the file without me having to put alot of work into each one, or even be in the office while they are creating this.
Is it possible to create a table of contents for the completed result using either seperare sheets, text formatting
View 9 Replies
View Related
Nov 17, 2009
I have a pretty big worksheet (230 columns x over a thousand rows). Any given column might have 3-4 nonblank cells; the rest are blank. What I want to do for each column is obtain the address of each nonblank cell, then grab the contents of the corresponding cell in the B column and concatenate the results. In the attached example, I want to put the function in d2, and the expected result would be ar 001, fc 001, hw 003.
View 4 Replies
View Related
May 18, 2012
Users copy and paste source data from a report into worksheet 1 each month. Data from last month is deleted and data for the current month copied into worksheet 1.
I am trying to write a formula within worksheet 1 to check that data for the current reporting period only is in worksheet 1. For example all data from last month's reporting period has been removed and the only data in worksheet 1 is the current reporting period.
Reporting period is shown in two columns Year and Period number (1 to 12).
View 4 Replies
View Related
Apr 14, 2007
see attached image. Chart is recognizing the 20 dates but not using the real dates listed in the column. Changed settings multiple ways to try to get it to use the actuals - no luck. I realize 'month's is checked in the image - but I tried 'days' and still no luck. What needs to be changed to make it report actuals listed in the 2 columns?
View 3 Replies
View Related
