Reference Function: Pickup Groups And Members Of Each Group From A Phone System
May 6, 2009
I have the following worksheet, which lists the pickup groups and members of each group from a phone system. Another worksheet lists all the exts and I want to have some sort of VLOOKUP, MATCH function that will look at the ext number and come back with the pickup group number.
I've attached a copy of the spreadsheet to show you what I'm talking about. So looking at the spreadsheet if I have an ext number of 8001 I want to return the value of 2 as ext 8001 is a member of pickup group 2. Also of an ext is not a member of any group, then I want to return nothing.
View 2 Replies
ADVERTISEMENT
Apr 27, 2014
I have a data that looks like:
1
a
data_1a
1
b
data_1b
1
c
data_1c
[code].....
Is there a way to automatically make all possible combinations of deduction between members of group 1 only then of group 2 only etc? So it would look like:
data_1a-data_1b
data_1a-data_1c
data_1b-data_1c
data_2a-data_2b
data_2a-data_2c
data_2b-data_2c
Combinations between letter indicators but not between number indicators. BTW groups are different in size starting from 2 till 8 members.
View 6 Replies
View Related
Apr 27, 2007
I have created a UDF which determines what the name of the day is.
Public Function Dagnaam(daydate As Date, Optional Language As String = "Dutch")
weeknl = Array("Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag", "Zondag")
weeken = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
dayno = Application.WorksheetFunction.Weekday(daydate, 2) - 1
Select Case Language
Case "Dutch"
Dagnaam = weeknl(dayno)
Case "English"
Dagnaam = weeken(dayno)
End Select
End Function
What i would like is when the user uses this function in the worksheet the field Language holds a dropdown list with predefined languages.
View 6 Replies
View Related
Oct 7, 2013
I want to group N elements which is a factor of 7 into groups of 3 randomly. For example, I have 21 elements:
ZSW
PMH
NYV
JTX
DEF
CRK
AOI
I want to group them into 6 more sets. Now, all in all 7 sets. Group them into other sets without the element repeating in the row it has occupied before. For example, for letter P. "P" has occupied row 2 in set 1, it cannot occupy row 2 anymore in other sets. Therefore, each element must have occupied row 1-7 in all the sets.
See attached excel file for the example output : grouping7-final.xlsx
View 14 Replies
View Related
Jan 13, 2010
way to transpose groups of data where each group is a different size. To clarify, my data looks like this (all one column):
data 1
data 2
data 3
data 4
data 5
data 6
data 7
data 8
data 9
data 10
and I would like to get it to look like
data 1 data 2 data 3
data 4 data 5
data 6
data 7 data 8 data 9 data 10
That is, to transpose each group of rows before a space.
View 9 Replies
View Related
Oct 24, 2007
How can I use the RAND function to generate a list of simulated phone numbers.
View 9 Replies
View Related
Aug 6, 2009
Example:................
The key point is that I have over 400 columns grouped in three, offset by one row down for each (this is due to a different formula I have working). Each column is 60 numbers long (not including empty cells above data). I want to copy all of these columns into a single set of three columns on another sheet (Sheet2). (These will be the fifth, sixth, and seventh columns, E, F, and G. If it would be simpler, I could simply copy the third of each set of columns on Sheet1 (C, F, I) to column G of Sheet2. Then there would be a total of 144 columns being copied (60 numbers per column).) All of this copying has to be values-only as the original columns consist of formulas referencing outside cells. Sheet2 should look like this:.............
View 7 Replies
View Related
Aug 16, 2014
Need formula to pic required data from Validation,
In the excel sheet Orange color (Data Validation) , If i select the data validation(Like, Jan-Orange- consumption) and corresponding data should display, so i required formula for that.
View 6 Replies
View Related
Apr 18, 2012
I am trying to project a dispatch date from my DC against the received customer orders.
The following are some important points for consideration of the projected dispatch date:-
1)Customer order is received 24X7(365 days via internet).
2)DC works between Monday-Saturday.
3)Courier company picks-up material from DC between Monday-Saturday.
4)Customer order processing CUT-OFF-TIME is 16PM(on any working day).
5)There are holidays for both DC & courier company on and above SUNDAY.
I am looking for a formula which can forecast the proposed pick-up date from the DC based on the following conditions:-
1)CUT-OFF-TIME:-If the customer order is received on any working day(except Sunday & Holiday) on or before the CUT-OFF-TIME of 16PM then the order will be dispatched on the same day or the dispatch will be made on the next working day.
2)SUNDAY/HOLOIDAY:-If the customer order is received on SUNDAY/designated HOLIDAYs(irrespective of any time) then the order will be dispatched on the next working day.
I am providing few probable scenarios with the desired result. Formula which can populate the desired result across D2:D11.
Sheet3 ABCDEFGH1Customer Order DateCustomer Order TimeOrder Processing CUT-OFF-TIME
Proposed Dispatch Date(Desired Result)Explanation For The Proposed Dispatch Date(Desired Result)
List Of Weekly Offs(Both DC & Courier)DC Holidays Courier Holidays
24-Apr14:2016:004-Apr4th April is a working day & order time is before the Cut-Off Time of 16PM and hence the dispatch will be done on 4th April.
[Code] ..........
View 1 Replies
View Related
Mar 21, 2014
I cant seem to multiply two groups of cells at the same time. Its a hard one to explain so this is what I have tried to use and it doesn't work
=sum(a1:a5*5),(b1:b5*10)
so I tried...
=sum((a1:a5*5)(b1:b5*10)) and that didn't work either
what I want to do is add a1-a5 then * 5 and add it to the total of b1-b5*10.
View 8 Replies
View Related
Feb 19, 2014
I need a VBA function to extract number sequences from a string and separate them with hyphens In the example below cell A1 has the value 'xx2 yyy34 zz515' The code must produce the value '2-34-515' from the above example I have the following function that extracts the numbers but need a way to separate the groups with a hyphen
Code:
Function parseNum(strSearch As String) As String
Dim i As Integer, tempVal As String
For i = 1 To Len(strSearch)
[Code]....
View 9 Replies
View Related
Dec 1, 2007
I need to create a function that numbers rows with respect to data groups in a column (column labeled "Type" in this example). The result would be that shown in column A in this table.
How do I write the function?
#TypeName Date
1CarsJohn1/12/2008
2CarsJane11/10/2007
3CarsMary11/2/2004
4CarsBob12/7/2003
1TrucksMike12/12/2007
2TrucksSandy1/3/2007
3TrucksDale12/14/2006
4TrucksVince4/8/2005
*
View 10 Replies
View Related
Mar 20, 2009
Another interesting dilemma to solve. Using this formula:
View 2 Replies
View Related
Feb 9, 2012
(I've actually gotten what I needed through some very convoluted formulas, but I thought there MUST be an easier way!!)
I have three columns of data:
foldername1TEST0001TEST0006
foldername1TEST0007TEST0008
foldername1TEST0009TEST0018
foldername1TEST0019TEST0021
foldername1TEST0022TEST0022
foldername1TEST0023TEST0028
another folderTEST0029TEST0031
another folderTEST0032TEST0039
another folderTEST0040TEST0056
yetanotherTEST0057TEST0058
yetanotherTEST0059TEST0101
yetanotherTEST0102TEST0104
yetanotherTEST0105TEST0106
yetanotherTEST0107TEST0154
yetanotherTEST0155TEST0190
foldername1TEST0191TEST0197
foldername1TEST0198TEST0267
foldername1TEST0268TEST0275
I am trying to get a "range" for each folder using the first instance in columnB and the last instance in columnC. For example, what I need to end up with is:
foldername1TEST0001TEST0028
another folderTEST0029TEST0056
yetanotherTEST0057TEST0190
foldername1TEST0191TEST0275
Again, I was able to finally accomplish this, but it took some way outside the box steps. Tried it in Access with simple query for Min and Max, but the caveat is that there are identical folder names for different ranges (see "foldername1) that need to be captured and I was getting results like "foldername1 TEST0001 TEST0275", which is incorrect.
View 2 Replies
View Related
Jul 15, 2014
I have certain cells in my spreadsheet that contain formulas linking them with other sheets within my workbook. I have grouped sets of rows in my first 2 sheets (to show just headings and to expand to subheadings & details). When I try protect the cells with formulas on the sheet then it blocks me from expanding or contracting the row groups.
Is there an easier way to stop someone editing or deleting the data in my cells? There are about 2 dozen cells with formulas in my first sheet that need protecting - unfortunately not in any particular order...
View 8 Replies
View Related
Apr 11, 2014
I have a worksheet with Names in Column A. Date when the person joined in Column B.
On a separate worksheet I want to sort members by the months and year they joined by having all the months in Columns.
View 9 Replies
View Related
Jul 3, 2009
I would like to assign listbox members to a public variable (quite the opposite of what you normally do). I receive the error message "type mismatch".
Public MemberLB as Variant
Public Sub ListBoxTest()
n = ListBox_target.ListCount
For iCnt = 1 To n
MemberLB(iCnt) = ListBox_target.List(iCnt - 1)
Next iCnt
End Sub
View 9 Replies
View Related
Apr 10, 2014
I have a sumproduct formula, I am trying to divide the output by the number of members used in the calculation:
This is my data set
4/10/2014102.6214/10/201499.5144/8/2014106.144/8/2014105.4
4/9/2014102.3284/9/201499.3864/2/2014105.8734/7/2014103.6
4/8/2014102.0884/8/201498.7953/17/2014105.5864/3/2014103.95
my sumproduct function is adding up all items that fall on that date which is fine, but i would love to calculate an average so divide by the number of items that were used in the calculation, is that possible?
=SUMPRODUCT(--(A1:G9=A11),(B1:H9))
View 3 Replies
View Related
Apr 29, 2009
I am trying to make a workbook to track crew members daily time.
I would like to be able to track time by Tag # and by W/O # for daily hour totals.
View 15 Replies
View Related
Mar 13, 2009
How do I activate the little box in VBA that appears after e.g. '.' (which enables me more quickly to see if I'm writing the correct code)?
View 3 Replies
View Related
Jan 5, 2014
I am using an excel where I want to send mail to my team members for their trainings pendings,
Few trainings are once in a year and few traings are monthly.
View 1 Replies
View Related
Feb 22, 2010
I have roster that has 12 teams on it, each team has their own column with the supervisors name in the first row. I want to look up the supervisor and count the number of team members listed under that supervisor.
View 4 Replies
View Related
Mar 17, 2014
I am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.
View 11 Replies
View Related
Nov 12, 2009
I have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
View 9 Replies
View Related
Nov 4, 2012
I use Excel to query a SSAS OLAP Cube, the return of which I then model into various outputs. I do not have access to amend the cube.
I am hoping it is possible to be able to add custom calculated members to some dimensions in excel rather than on the server, but I'm struggling to find any resources that explains how it is done or if it is even possible. For example, the cube will return the dimension 'Source Country' with members 'England', 'Northern Ireland', 'Scotland', 'Wales', I would like to add a calculated member 'United Kingdom' to consolidate all those members.
Is this possible or should I just look to calculate this consolidation by formulas in the model.
Excel 2007
Win7 Pro
View 1 Replies
View Related
Feb 10, 2014
I have several groups of data in the same sheet. Each group has two blank rows above the first row of data. Each group has column "B" in common (e.g., "Phone" in the example attached). I'd like to add a title/label of "Phone" to the first column in the row immediately preceding the first row of data for that group.
View 1 Replies
View Related
Jan 17, 2013
how to use the group box to group a number of option buttons together.
Question is, lets say, i have a total of 4 group box. if i want it to work in such a way where by once 2 option buttons are being selected (1 option selected from each group box), the rest of the option buttons in the 4 group box will be greyed out. is that possible?
View 2 Replies
View Related
Aug 9, 2006
My store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.
Is there a function to do this automatically?
View 9 Replies
View Related
Nov 6, 2008
I have a the following function: =INDIRECT(ADDRESS(SUMPRODUCT((F35:G45=K40)*ROW(F35:G45))-21,SUMPRODUCT((F35:G45=K40)*COLUMN(F35:G45))))
I'm trying to put the get the value of it from a different Sheet.
say this function is in Sheet1 and I wish to place the equation in Sheet3 - what should I change? I tried INDIRECT(ADDRESS(SUMPRODUCT((Sheet1!F35:G45=K40)...
nothing works.
what the function does? there are 2 tables. table 1 has values and table 2 has values as well. K40 is the user input --it reflects the number in table2 and the return value is the 'reference' in table1. for example, if table2 in F41 has the number 54 and the reference for it in table1 is $344, when the user types 54 in K40 the returned result in the function will be $344
View 4 Replies
View Related
Jul 28, 2009
I need a function where the sheet-reference can be linked to a cell.
By other words can the content of cell A1 be sheet1, sheet2, sheet3 etc. The cell in B2 shall always point to cell b3 in the choosen sheet, but the sheetreference is dependent on the sheet refererred to in A1
View 2 Replies
View Related
