How To Populate Table With Names Based On Cell Numbers
Mar 13, 2014
I have a column (A) with names, Column (B) with platoon numbers. On another sheet I have a table for each platoon and I want it to be populated with the names of each platoon indevidually. The names are alphabetically and the platoon numbers are mixed.
Column A Column B
Name Platoon
J Goodman 1
L Barns 7
H Law 1
B Aims 10
and so on....
I want all the names from platoon 1 to appear in the table for platoon 1. I have about 3000 members and 40 platoons.
View 3 Replies
ADVERTISEMENT
Jan 4, 2013
I have a worksheet with many tables that I use in formulas.
I like tables for a couple reasons, one being the ability to insert/delete rows without affecting the rest of that worksheet row.
Also, automatic copy of formulas/formatting is great.
But, what I really like about tables is the ability to use the naming conventions in formulas.
Problem is when I save this worksheet, after I close it and open it back up, all table references in my formulas have been converted to cell references.
Example:
Code:
=IF((SUMIF('Quote 1'!$M$28:$M$43,">"&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&tblOSSRV[Min Order Cost])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],""&tblOSSRV[Min Parts Per Line])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],"
View 4 Replies
View Related
Oct 7, 2009
I've been racking my puny brain for an hour trying to figure this out. Basically, my problem is with the last 2 invoices listed. If one invoice has "PO CLOSED" on it, I want all other invoices on that same PO to say the same thing. Surely I can do this with a formula or conditional formatting, but I cannot think of how.
PO #Invoice #DateInvoice TotalDept.ActivityElementPO AmountPO Amount RemainingPO Status0707955289651/12/09$812.9075105854035$2,960.28$59.380707955459806/15/09$667.2475105854035$2,960.28$59.380707956486827/7/09$812.9075105854035$2,960.28$59.380707955584669/29/09$607.8675105854035$2,960.28$59.380719750913336/18/09$947.7975105853305$5,000.00$3,117.210719750915917/17/09$935.0075105853305$5,000.00$3,117.21PO CLOSED
View 9 Replies
View Related
Dec 15, 2008
Using two columns of data:
Column A with a list of first names sorted by first name, Column B with a different integer value for every cell in Column A. How do I write a formula that sums the values contained in Column B for each unique first name in Column A? The formula needs to identify where first name changes in Column A as there are different numbers of entries in Column A for each first name. (Note: I do not wish to use sub-totals, as I want to copy/paste values back into the results column and use sort to generate a new lookup table)
Have attached an example of the two columns I would like to test. Ideally I would like to see the results in a third adjacent column with the sum displayed only where a new first name appears in first column (the cells would be blank in the third column when the first name is the same as the row above).
View 8 Replies
View Related
Mar 18, 2013
I've read a lot of tutorials on how to use pivot tables, but found them either too basic, or too advanced, and never finding any example of what I am looking to do.
I have a database, column A holds names, and column B their phone numbers. I'd like to generate a list of unique names and their associated telephone numbers. I figure I can use a pivot table to do this.
But I do not quite understand how to format the pivot table to put the name in one column and the phone number in the next. I will use this pivot table as a vlookup range. I assume I can do this.
View 7 Replies
View Related
May 23, 2013
I have a workbook with two worksheets,
sheet 1 contains student data, name dob, actual age, raw score and an empty column standardized score
sheet 2 contains a conversion table, using the actual age from sheet one you locate the age in the top row of table in sheet 2, and using the raw score from sheet 1 you locate the matching raw score from the first column of the table in sheet 2, going down and acrosss until you meet this gives you a standardised score
i want the standardized column in sheet one to fill by using a formula which looks at the table in sheet two locates the two values and returns the result.
View 5 Replies
View Related
Jan 27, 2013
I am a small business owner with little experience in Excel and I have been trying to set up a worksheet that will organize my products and finances. I organize sets of various products and sell them in bulk to customers. I was trying to recreate this in Excel so I can figure out the total price for each of these sets and easily edit them.
What I've been trying to do is to take a list of the items and assign a price to each of them. This is on a sheet titled "Prices." Here's an example:
prices.jpg
On a separate sheet titled "Sets," I created lists of items included in each particular set. To easily add or change the products I used drop down menus.
sets.jpg
I would like for the sum of the price of all the items listed under each set to add up automatically in the "Total" row. For example, in cell B7 it should display the number 10. Here is a list of the following formulas I've tried:
[Code] ........
View 2 Replies
View Related
Jul 7, 2014
I've been trying to get a table to populate based on a couple of criteria. However, I've not come-up with the solution yet.
I have my dropdown selections in cells C2 & C3. The objective is to populate the table below the dropdown with data from the sheet named (very unimaginatively) 'Data'. Currently you see the selection AA-11 & Mar-14 in the Contract ID & Month cells. If I change this, the table below should auto-populate.
I've attempted using Vlookup, Index-Match. But it does not give me the desired result.
Also, the number of Products can change each month (although the file shows 4 for each month & each Account).
View 4 Replies
View Related
Oct 22, 2007
I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.
There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.
NAME, PHONE, STATE
Bleh, 555-555-5555, =ChkState(B2)
I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field
Function ChkState(pVal As String) As Long
Dim AreaCode As String
Dim StateAbrv As String
AreaCode = Left(pVal, 3)
If AreaCode = "201" Then
StateAbrv = "Test201"
ElseIf AreaCode = "203" Then
StateAbrv = "Test203"
ElseIf AreaCode = "555" Then
StateAbrv = "Test555"
Else
StateAbrv = "0"
End If
MsgBox StateAbrv
End Function
I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.
View 9 Replies
View Related
Mar 28, 2008
I'm trying to create a macro which will run through every salesperson in a list, and copy and paste their product and client data (from separate sheets) to a template sheet. The template sheet will then by copied to the Salesgroup workbook, and renamed to the salesperson's name. Then the macro should go back to the original workbook and do it all again for the next salesperson, putting them into the same workbook.
The problem I'm having is that the references to the Template worksheet seem to be being messed up by copying the template sheet to the new workbook, and it then ends up creating all the worksheets, but it stops taking the data from the data sheets, and ends up copying the previous worksheet and pasting that into the new copied template, which means that I get the template multiple times across the same sheet.
Sub SalesSheets()
Dim Salesbook As Workbook
Dim NewBook As Workbook
Dim SalesDataSheet As Worksheet
Dim ClientDataSheet As Worksheet
Dim SalespersonListSheet As Worksheet
Dim Template As Worksheet
Dim rRange As Range
Dim rCell As Range
Dim strText As String
Dim SalesGroup As String
Application.DisplayAlerts = False
Application. ScreenUpdating = False
Set Salesbook = Application.Workbooks("2008 Salesperson TEST.xls") 'change filename here
Set Template = Salesbook.Worksheets("Salesperson Template")
Set SalesDataSheet = Salesbook.Worksheets("SP product YoY")
Set ClientDataSheet = Salesbook.Worksheets("Client YoY")
Set SalespersonListSheet = Salesbook.Worksheets("Salesperson List")..........................
View 3 Replies
View Related
Mar 3, 2013
I'm trying to extract a list of names from an Excel 2010 table based on two criteria, thus:
=IFERROR(INDEX(Database[FullName],AGGREGATE(15,6,ROWS(Database[Age]-ROW('Database'!$A$2))/((Database[Age]>1.8)*(Database[Age]
View 4 Replies
View Related
Jun 7, 2014
I am keeping a spreadsheet for an auction
We have 20 people and each person gets a total bid for 48 items.
if person1 = 55,person2 = 100,person3 = 76,person4 = 67,person5 = 49 etc.
The person's names will be in column C while values in D in cell G3 will hold the total bid for the items each week updating.
What I am trying to do is sort the table (names & values) from cell G3.
So if each week G3 total gets bigger then the table sorts to match.
If the overall total bid after 48 weeks (G3) was 52 then the table sorts with 55 on top (because it's closer) 49 next.
The closer the person's bid to the total bid the further up the table the person will be.
I have uploaded an example with before and after sample.BidExample.xlsx
View 8 Replies
View Related
Jan 7, 2010
I'm having a bit of a block with an index/match formula that I am trying to create for the attached spreadsheet. i.e. I need to populate cell J3 with the info the corresponding letter contained in the table to the far left. The numbers of reference to match are the 'zones' in H3 and J2.
View 2 Replies
View Related
Mar 12, 2013
This is what I need:
Columns B, C, D & E are all populated with 3 digit numbers.
I would like column F to automatically populate with any of the 3 digit numbers that share two numbers, i.e.
F2 might look like this (using 00 as the pair):
001, 040
F3 might look like this (using 01 as the pair):
701, 051, 110, 001, 120
F4 might look like this (using 12 as the pair):
123, 721, 281, 912, 112, 120
etc...
View 1 Replies
View Related
Apr 9, 2014
I am trying to populate a field in a Sheet 1 that is dependent on two other drop down fields that are chosen. Some of my data has the same names but different equipment.
Name
Equipment
Serial
Model
Jim Jones
snips
123
1AB
[Code] ....
Basically, I want to choose a name from say a drop down, then an equipment pc from drop down, and have the other field populate for say the Serial Number.
View 4 Replies
View Related
Apr 9, 2012
I would like to populate the names in the G column as shown below. the formula which can yield the result?
Sheet2 Â ABCDEFG1LocationJohnTrevorMikeBillDukeDesired
Result2AYes    John3B YesYes YesTrevor/Mike/Duke4CYes  Yes John/Bill5DYesYesYesYesYesJohn/Trevor/Mike/Bill/Duke
View 5 Replies
View Related
Oct 10, 2009
Is there a way to make a cell populate certain text based on conditions of other cells without putting the formula in the cell you want to populate. So that someone could type other text into the cell if the conditions were not met?
View 14 Replies
View Related
Mar 18, 2009
I have a macro that copies data from 6 different worksheets into one summary worksheet - based on certain criteria - I need a way to note on each row of the summary sheet which worksheet it was copied from. Each worksheet is specific to a salesperson, so ideally I would like that salespersons last name to populate in column B on the summary page for each row of theirs that is copied over....here is my current macro I use to copy the data: (this code is repeated for each sales persons sheet - "Blankenship", "Dew", etc...) I have attached a scaled down version of my file.
View 2 Replies
View Related
Aug 2, 2013
I have a list of teams, and beside them I need a calculation to be filled.
The criteria is based on a cell reference (a month which can be changed from a drop-down in BM2) and also the name of the team.
dummy 16.xls
View 5 Replies
View Related
Apr 6, 2013
I have a table in excel with some data to create labels, but these labels must be repeated according to the number of volumes. Example: I have a delivery for X and such delivery has 5 volumes, need to create 5 labels just changing the volume number: 1/5, 2/5, 3/5, 4​​/5 5/5.
I would enter a value in "D1" after running the code, in column "A" shall be filled as follows.
D1 = 7
A2 = 1/7
A2 = 2/7
A2 = 3/7
A2 = 4/7
A2 = 5/7
A2 = 6/7
A2 = 7/7
If "D1" equals 3 then
A2 = 1/3
A2 = 2/3
A2 = 3/3
View 4 Replies
View Related
Apr 30, 2014
I have some excel sheets that are formatted like the following:
COMPANY | TOTAL | R | G | B
company1 | 10 | 255 | 000 | 000
company2 | 20 | 000 | 255 | 000
company3 | 30 | 000 | 000 | 255
...
and so on...
My question is that I would like to have a macro that runs on this basic file and creates a bar graph with the data. Then it utilizes the RGB values in the columns to change the specific bar for that row. So setting the r, g, b as variables corresponding to the columns in the sheet. Also there isn't a preset number of rows in the files.
[URL]
View 13 Replies
View Related
Jul 7, 2014
I am currently trying to create a spreadsheet whereby if I enter certain text in a cell in Column A on worksheet 1 that correlates with text in a cell in Column A on Worksheet 2, then the description in Column B in Worksheet 2 is entered into Column B on worksheet 1.
For example, if worksheet 2 has the following:
Column A Column B
XXXX PRODUCT 1
YYYY PRODUCT 2
and I enter XXXX in column A on worksheet 1, I want Column B on worksheet 1 to automatically enter PRODUCT 1.
View 5 Replies
View Related
Feb 27, 2014
I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.
What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.
View 7 Replies
View Related
Jun 26, 2014
I have a userform, a textbox and a listbox.
I want to populate the listbox dependant on the worksheet names, skipping the first 4 worksheets The texbox should highlite a value in the listbox (if exists).
View 2 Replies
View Related
Aug 15, 2007
All I want to do is
put a combo box on a spreadsheet,
have it populated, and sorted - ideally, alphabetically with range names already created.
And
I'd like to be able to click on the Range Name and GoTo the location.
View 9 Replies
View Related
Sep 11, 2008
I have a complicated one here (at least for me)...
I have a very simple sample spreadsheet showing exactly how I'd like it to work but couldn't find out where to upload it? I can upload or send to you privately, if need be? May make it much easier to understand the desired end result.
I want to populate as follows:
Column A: "IN TIME"
Column B: "NAME"
Column C: "POSITION"
I have a date in cell D1 - today().
I need to search for that date in a column between range AA2 and BN2. When it finds the date (let's say it finds a MATCHing date in AA2), it will then search that entire column cells AA30:AA1920 and look for "server". When it finds "server" it will then grab the in time which will always be 4 cells directly above where it found the position ("server"), and then the name of the person, which will always be in column X, 6 cells above the position.
There will be multiple instances of "server", and I want to populate the columns A, B, C with all the servers it finds first for that day with their name, in time and position. then a blank row.
Then, I want it to find "Bar" positions using the same formula and method, and populate that right below the server data, then find "line", etc.
Ultimately, I'm trying to make a daily staffing plan, where all the people that are working on that date, it will show their in time, name, and position in columns, A, B, C as far down as necessary.
However, I'd like them auto grouped together by position, so I'd like the formula to somehow populate all the servers first, then all the bartenders, then hosts, then line, etc.
View 9 Replies
View Related
Mar 4, 2008
I want to be able populate a combobox with all defined ranges in the workbook?
View 4 Replies
View Related
May 20, 2008
I am having trouble with populating a combo box on a user form. I wish for the combo box to be populated using the names of the sheets of the workbook. I am using sheet1 as the control sheet where a user form will open up from this sheet, this will allow me to select the sheet that I require from a combo box that is placed on the form however I do not seam to be able to code the VBA to populate the combo box with the sheet names.
View 3 Replies
View Related
Apr 8, 2014
When I enter a value for 'Number of Steps' from a drop down a table is generated with the x values changing accordingly.
For example:
Number of Steps 5
x x^2
------------
0 0
1 1
2 4
3 9
4 16
Number of Steps 9
x x^2
------------
0 0
0.5 0.25
1 1
1.5 2.25
2 4
2.5 6.25
3 9
3.5 12.25
4 16
You will notice that the number of steps is the number of x values that take us up to 4. The 4 is fixed. I would be able to work out the maths myself as I am a maths teacher but I was wondering how I could autopopulate the table based on a drop down box selection. If it involves going into VBA then don't worry my son needs to get to grips with what I have done.
Edit. The formatting does not seem to work above. On the left of each table is supposed to be the x value and on the right is x^2.
View 7 Replies
View Related
Mar 5, 2009
I need to modify the code below:
PHP
Sub Macro1()
Macro1 Macro
Dim strbody As String
For Each cell In Range("B2:B640")
Sheets("MASTER").Select
Cells.Select
Selection.Copy
Sheets("Final Merged").Select
Sheets.Add
ActiveSheet.Paste
Next
End Sub
The code above is creating a new sheet for each different value in column B. I would also like to take that value and place on the newly created sheet in cell A17.
Also, how would I get the sheet to be ranamed to match the value in cell A17 of the newly created sheet?
View 3 Replies
View Related
