Automatically Populate Cell Based On Worksheet Name
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 on the summary page for each line of theirs that is copied over....here is my current macro I use to copy the data: (this scrpit is repeated for each sales persons sheet - "Blankenship", "Dew", etc...)
View 5 Replies
ADVERTISEMENT
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
Jul 31, 2007
Is it possible to automatically populate a summary worksheet, based on matches in other worksheets within the workbook which match a particular date?
I have a workbook with 15 worksheets--each sheet is setup like a database with 4 columns (fields). One column is for the date.
I would like to populate a summary sheet that serves as an executive summary of all "records" which match a particular date.
View 10 Replies
View Related
Apr 16, 2008
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.AutoFilterMode = False
Range("d6:g6"). AutoFilter
Range("d6:g6").AutoFilter Field:=2, Criteria1:=Range("e1"), Operator:=xlAnd, _
Criteria2:=Range("e2")
I've attached the file also.
What i intend to do is that : (1) if i enter a value in E3, the filter should only apply using E3 value (currently its applying E3 value but if E4 is kept blank, it takes that as = " " ) . Unfortunately, i need to have the and condition, so i have to find a way in spite of this condition. Any way out ??
(2) If i enter values in A) E3 & F3 B) E3, F3, G3...then it ahould make multiple filtering possible. But when i try to apply such a condition, the same problem as in point (1) occurs, it takes the and empty criteria range as = " "
View 8 Replies
View Related
Dec 3, 2012
I have main worksheet (target a) that I am trying to populate data from target(worksheet) b. The data I am trying to get from target b changes every month,(declining balance) based on a new month. So how can I get financial data from different cell each month from "b" into same cell in "a"? (so "A" # would be overwritten in same cell based on new # from "B". I have tried VLookup but can't be doing something correct.
View 2 Replies
View Related
Mar 26, 2008
i am working on a spreadsheet that includes a large amount of statistical numbers / records. one part includes a win / loss column and the very next column i am entering the final score for each game. to max this easier to comprehend, there are only 3 columns. 1st column lists my opponent, the 2nd lists, with just a red L for loss, or a green W for win, and the 3rd column has the final score. (my score is always first, regardless of whether i won or lost the game.)
OPP F SCORE
--------------------------------------------------
John W 23-10
Paul L 17-25
Terri W 10-7
This is what i'm searching for: (and the easiest way possible b/c i'm not familiar with macros / visual basic, etc.) ... Once i enter the score: ##-##, I would like the "F" (final outcome) cell to automatically populate either a bold red L for a loss, or a bold green W for a win. My scores are almost never 3 digits, so they would almost always be ##-##. if u need me to provide more info,
View 12 Replies
View Related
Dec 18, 2006
I have a master list of people. (call it master) I have 1 other worksheet that should contain everyone aged 15 and up (call it must progress). One of the columns on the master show's their ages. I was hoping excel would automatically populate the sheet based on the age from the master list. So, for example. On the Master list, Joe Smith is 17. I would like Each cell in the Joe Smith Row to automatically copy itself to the must progress worksheet because his age is over 15. The formula would be based off the age on the master.
View 2 Replies
View Related
Apr 27, 2014
I have a spreadsheet where vehicle milage is entered at the end of each month. On another sheet I have the vehicle fleet numbers and the the months along the top. What I need to happen is; when the mileage cell is updated I need the cell in the other sheet to be automatically populated, when the mileage is updated the next month I need the next cell(February)to be populated with the new milage and so on. The aim is to calculate monthly milage for each vehicle without having to enter the date at the beginning and end of the month.
Everyone I work with is completely computer illiterate and only knows how to enter the data the same way they always have(just the once at the end of the month) changing it up will only cause issues.
View 1 Replies
View Related
Feb 8, 2012
I'm looking for a cell formula. here is an example:
In cell A2, I want it to populate automatically with a predefined value if cell A1 is a certain colour, as below.
A1 A2
559.00
I have conditional formatting in cell A1.
View 6 Replies
View Related
Mar 29, 2014
I have a spreadsheet with 6 hidden columns (B to G). Rows 1 to 14 are frozen and have data that comes from another work book and these rows will be protected. The user will enter data from H14 to U14 and then H15 to U15 for the next row and so on down the sheet. The number of rows of data over a week is variable, a minimum of 21 rows (3 per day) but no maximum.
When the user starts entering new data in the column range H to U I would like to automatically populate the 6 hidden cells on that row with information from particular cells in protected rows 1 to 14. For example B14 would populate with the value from I4, C14 would populate with the value from I5, D14 from I6, E14 from I7 and so on whenever someone enters a value anywhere from H14 through to U14.
I can understand that a worksheet change event would be useful to do this but I guess then it wouldn't want to run every time each cell is populated so I think if it had to activate on a single cell change then the cell in column L would be best. Also, as I understand it, there can be only one block of code per sheet that operates on a worksheet change event, have I got that right? If that is the case then I assume all the code to populate the hidden cells on each row will need to be in this block of code.
View 8 Replies
View Related
Mar 2, 2010
I am trying to get a cell to automatically populate commision earned.
Commision is worked out as a percentage to gross profit and works as follows:
£0-4000 = 0%
£4001-8000 = 1%
£8001-12000 = 2%
£12001-16000 = 3%
£16001+ = 5%
I have my spreadsheet that details their sales and profit. the profit is calculated in cell M45.
I have tried for ages to get an equation to work, and have ad no joy. It is actually driving me mad now. I need a better man/woman than me.
View 9 Replies
View Related
Feb 14, 2012
I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
View 3 Replies
View Related
May 16, 2008
I am working on a spreadsheet for a client. I am using Excel 2007 and she is using Excel 2000. I have created a emplate for her to enter survey data into. Using the following code, I have created combo boxes for the drop-down menus so the client can tab from one cell to the next while entering data:
Option Explicit
Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
'Hide combo box and move to next cell on Enter and Tab
Select Case KeyCode
Case 9
ActiveCell.Offset(0, 1).Activate
Case 13
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub.....................
View 6 Replies
View Related
Nov 21, 2012
I am new to VB macros, I am trying to figure out how to color a tab Green based on a selection from a drop down list.
View 9 Replies
View Related
Mar 24, 2014
I am working with a worksheet that has some cells merged e.g. A1 and A2 are merged together as 1 cell. I am using cell offset to reference values from the worksheet assign them to variables then populate another worksheet with their respective values. I noticed that cell.offset(row,column).value is not retrieving a value when merged cells are mixed with non-merged cells within a given row. Is there another method/function I can take advantage of here?
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
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
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
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
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
Dec 7, 2006
I think the best thing is to take a look at the attached, then read what I'm looking for, as it would make more sense...
That being said: what I am looking to do is change cell L3 based on new criteria in cell M3.
Right now if B3=Stationery World then L3=Stationery.
What I then want to happen is if E3=Toner then M3=67200 which then has L3=Equipment Consumables and NOT stationery.
View 9 Replies
View Related
Jan 25, 2008
I found many examples on this forum of how one can pull in data from closed workbooks, or copy it and have it stored in the active sheet. So far so good, but I need a solution which would do the opposite - I need the macro to take a range of data form the active sheet store it in a closed workbook and save any changes in the target workbook without opening it.
View 2 Replies
View Related
May 12, 2014
I've attached an example workbook.
Trip Plan example.xlsx
I'm trying to work out how to have cells R11 - Rxx generate the correct date, based on the rest of the calculations.
Please note that the attached is a crude cut down version of the larger spreadsheet, for the purpose of highlighting my problem.
SO, if E7 is changed, subsequently the values in K11:Rxx will be respectively updated.
D12 and G12 are entered manually. (although I'll probably make these drop downs or date choosers at some point...)
Depending on the value in E7 and time in D12, the required break period is listed in K11: Kxx.
The time that these periods of rest are required to occur before is then calculated and listed in O11:Oxx.
I need R11:Rxx to calculated the appropriate date from these calculations, relative to the date in G12.
For example, the rest required in K11, is required on the same day, as such it need to populate with the value in G12.
However the rest required in K13:Kxx, is required by a time (O13:Oxx) on the following day - G12+1
In some case, this time variance could run over 2 or more days.
I figure it's got something to do with the value in D12 also including the date of 0/1/1900.
I'm happy to add hidden cells for formulas/results to work around it, as the end product will print as an A4 document, where many cells will be locked.
Would prefer not go with any VBA unless it's my only option as I haven't done any VBA stuff as yet.
NB (the value in P5 is static only in this example)
View 11 Replies
View Related
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
View Related
Jan 19, 2009
Various Numbers Will Be in a drop-down List eg:
6718
0820
7141
0821
I need it to Be able to, When I Select '6718' from the drop down Menu
"375 CAN 2lB18 C/F D/COKE" will Appear in the Next Cell.
View 9 Replies
View Related
Mar 24, 2013
I have data in three columns
A B C
4.5 9.50 4.657
4.5 11.60 4.789
4.5 12.50 4.654
4.5 13.50 3.930
4.5 15.20 3.826
I need to be able to auto-populate the cell in column G based on values in columns A & B. If I type 4.5 in E2, and 13.50 in F2, G2 should auto-populate with the value from column C
E F G
4.5 13.50
View 2 Replies
View Related
Jul 3, 2009
How do i get cells to automaticly populate dates in sequence based off of one cell. I.E C1= 4/Jul/09 I want A2-A7 to populate the dates in sequence Jul 4 - Jul 11. Is it a formate issue?
View 4 Replies
View Related
May 14, 2008
I have three hidden columns (Text only) one column with Parts, then Description, then Cost. I have a pull down menu (i.e. Cell 3, A) listing all of the choices in the hidden Parts column. I want to automatically populate Cell 3, B with the corresponding Description when a Part is chosen from the pull down menu, and the same in another column for the Cost.
I am using Excel X for Mac, in OS 10.5.2
View 4 Replies
View Related