Automatically Populate Cells If Value Exists Within Range
Mar 22, 2013
I need to summarize some information from a different worksheet. Worksheet A(WS-A) is the summary and worksheet B (WS-B) is the source of information. On WS-B I have a range of values under the title "Materials" which I use to put together an estimate. This range has about 25 rows and not all of them are populated depending on the needs of the particular job.
If it matters, the Materials cells are populated by a drop down "list" from yet another worksheet. On the summary (WS-A) I want to show only the cells from WS-B that have a value in them so that if, within the Materials worksheet, there are only 6 rows that have data in them...only those 6 rows are shown on the summary worksheet.
View 9 Replies
ADVERTISEMENT
Mar 12, 2009
I would like to know how to automatically populate a combobox with the data in a certain range. Attached is a simple workbook with the example. I know how to do this with the additem. But I dont know how to create the array or userform initialize to populate the combobox with the range.
View 3 Replies
View Related
Oct 11, 2006
I have a worksheet where I need to match the work items from Region A to Region B. If the work items match , then I need the formula to automatically populate the corresponding data in the next 3 columns as similar to Region A. I have tried searching the forum and but I could not work out the formula. I am not sure if the formula is Vlookup or INDEX/Match which both I am not well verse. I have attached a sample sheet.
View 4 Replies
View Related
Jan 27, 2012
I am writing a macro for a my team. I will distribute the .bas file then have them run it. I don't know what their individual sheets in their workbook are called so I need to ask them. Grab various columns from that sheet, check if they have a sheet called data import, then put in the columns in order in data import.
I have:
Code:
Private Sub checkForSheet()
Dim sh
On Error Resume Next
sh = Worksheets("Data Import").Name
If Not Err.Number = 0 Then Sheets.Add.Name = "Data Import"
On Error GoTo 0
End Sub
to check if Data import is made
Then
Code:
Sub prepareData()
Dim SCMsheet As String, TSE As String
Dim DISh As Worksheets
SCMsheet = InputBox("Enter your SCM Sheet name in entirety.")
If SCMsheet vbNullString Then
[code]....
But it doesnt stop scmsheet is not in the workbook, and the values arent put in.
View 4 Replies
View Related
May 23, 2014
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
View 2 Replies
View Related
Jun 2, 2014
I have a workbook that links to four files. I would like to automatically update the links when the file is opened and if the linked files are not available, don't display a message.
I got the first part done using the Options menu in Excel but can't figure out how to suppress the message when the linked files are not available
View 3 Replies
View Related
Jan 14, 2007
I have some code that works almost perfectly. I am trying to automatically delete rows in which no data exists in cells in a certain column.
the problem that I am having is that is seems to go into an infinite loop.
For example, if I delete row 5 and then iterate to the next row, row 5 becomes what used to be row 6 and now my code is on row 6...essentially skipping a row.
Here is an example data set (note that the set is for 20 rows):
1
3
6
15
36
37
here is the
Sub Macro2()
r = 20
Min = 1
Do While r >= Min
If Cells(r, 1) = "" Then
Cells(r, 1).EntireRow.Delete
Else
r = r - 1
End If
Loop
End Sub
View 9 Replies
View Related
Aug 10, 2006
I have a range of cells that I want to use as column headings. Say the range is A1:A100. I need them to be able to be transposed across the first row of the sheet. Now I know that I can use the transpose function in a cell but this array is embedded in a macro.
View 3 Replies
View Related
Oct 2, 2008
I want to run a macro when a cell is changed and populate Cell A12 With
the value in a named range.
The Name of that range is in cell A3
so i assumed i could do the following:
View 7 Replies
View Related
Sep 25, 2011
I have a sheet containing 2 lists of categories for income and expenses. These lists are named inc and exp, respectively, and are contained in a sheet called "Lists." They are dynamic ranges, meaning every time a value is added, the named range adjusts itself to include the new value(s). [=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)]
I have 2 tables summarizing 12 months of data. The tables use sumif to find all occurrences of each category and sum them. There's 1 table for income, 1 for expenses. Each is a 2 column table, with all the categories for income in column A and their total for 12 months in column B. Same for Expenses in D & E.
To populate the categories in column A & D, I am currently using the simple =Lists!A1 =Lists!A2 (inc range), =Lists!B1 =Lists!B2 (exp range) and so on... the problem is when a value is added to either list, while the named range adjusts, I have to manually drag the formulas in Column A & B down 1 more cell to include the new value.
How can I use VBA to look for the inc and exp ranges (which will change in size), then populate each table with the most recent categories?
View 9 Replies
View Related
Nov 2, 2013
I have a combobox on a spreadsheet it is populated on a sheet called teams :
The Format control has an input range of: Teams!$B:$B
Now this has a few thousand empty cells in the range but I would need it to only populate with the cells that are not empty.
View 3 Replies
View Related
Jul 25, 2014
I have a column of values (E4:E87). I'm adding 1 value to this column manually each day...so tomorrow (July 24th) I will have E4:E88...and so forth. Each day is a new row.
In column Z (i.e. cell Z87) I have the formula =(SUM(O53:O87))/(SUM(E53:E87)). Z88 will have
=(SUM(somevalue:O88))/(SUM(somevalue:E88)). This gives me a resulting percentage for a rolling period of time.
I need an automated way to populate "O53" and "E53" ("somevalue") in the formula so that the SUM of the current row's cell (i.e. E88) through [whatever] is 50 or greater. (The sum of the values should be as small as possible, but at least 50, and should not include any older values than absolutely necessary for it to equal at least 50.)
An image of my spreadsheet can be viewed here: [URL]
View 3 Replies
View Related
Mar 10, 2014
Is it possible to be able to select a range of dates, and a time interval, and then to automatically fill in cells in a column? to be more specific, I want to be able to select, say 1 Jan 2013 to 31 dec 2013 in one box, then specify a time interval, for example "hourly" and then a column is populated with each interval, i.e. 1 jan 2013 00:00, and then the next one is 1 jan 2013 01:00 etc. I have tired the normal automatic fill tool but it never seems to get it right. It works for the first few days then it all goes funny.
View 3 Replies
View Related
Sep 22, 2009
I need to populated a specific range of cells based on a value of any kind in a header cell. Attached is a sample of the end results. Please remember each header cells needs to determine what happens the range of cells below the header cells.
View 3 Replies
View Related
Mar 28, 2009
I've asked this similar question on another message board (the Microsoft help community), but failed to follow-up quickly enough for the support I've come here to ask for. My situation involves a spreadsheet that is used to track days worked over the course of a week. I have the days of the week listed in one row (Sun - Sat) and in the row above, a place to put the day (i.e. - 3/28). I asked for help with a macro that would allow the user to place the date into any of the cells above the days of the week, check to ensure that the corresponding day of the week is correct, and if so, populate the other remaining empty dates automatically. Below is the macro that was kindly provided. It works perfectly as long as my data is in the referenced ranges (A1:G1, etc); however, my actual data is in range L6:R6 for the dates and L7:R7 for the day of the week. (example below). I've been able to pick apart the code to understand how to change most of the macro to accept input from the different range; however, I'm unable to comprehend what I'm missing to complete the modification.
View 6 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
Jul 29, 2008
I'm new to Excel 2007... whilst I'm managing to find most things I'm used to using in 2003, I have an issue in one workbook that I can't figure out.
When I click in some single cells (not all), that cell and several below it automatically highlight as a range... left-clicking initially highlights the cell, but as you release the mouse button, the others below it also highlight.
View 14 Replies
View Related
May 16, 2009
The following code works perfect but the "change" event is only triggered when working directly on intersect range. Tried using the "calculation" event but could not figure it out. This is what I want:
1) To replace the code provided below using the calculation event
2) To only trigger the event for the row(s) where the new value was generated, not for the whole "For Each" statement
3) To use one single code for all worksheets, instead of copying the code in every working worksheet on the workbook, if feasible
4) And I would like a "second alternative", where the user of the workbook can click on a button and trigger the event on every row on the workbook that has a non empty cell within the intersect range, assuming that the intersect range column is the same for all worksheets
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
Set d = Intersect(Range("I:I"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
Select Case UCase(c)
Case "C"
fc = 1: fb = True: bc = 4
Case "O"
fc = 2: fb = True: bc = 3
Case "D"
fc = 2: fb = True: bc = 46
Case "G"
fc = 2: fb = True: bc = 5..................
View 4 Replies
View Related
Sep 10, 2009
I have a form, when I fill in the order no, I want it to look if the value exists in a range and then notify me with a msgbox. Here is my code - it doesn't work no matter what I insert into the textbox it tells me that it exist.
View 11 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 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
Oct 15, 2009
Can we use the inbuilt thesaurus that MS excel has through macro.
i.e. if I select a word and play the macro, excel will automatically populate the below rows with the synonyms.
View 2 Replies
View Related
Jun 28, 2007
I have a userform that pops up upon opening a file. The user form has
5 text boxes names Zero, Two, Four, Seven and Nine. I would like each of these text boxes to automatically populate with the values last used. The values last used are stored in Worksheet "Index Settings" in Cells "C3:C7".
View 9 Replies
View Related
Jun 18, 2014
Basically, if a cell value exists in a list on another worksheet then move to next row. If the cell value does NOT exist then look at columns J, K, L and M and see if a specific value exists in each. If any of these cells contain this criteria then copy the row and add it to the bottom of a growing list on another sheet. Here is what I have so far.
Sub OrdersHeld()
Application.ScreenUpdating = False
Dim x As Double
Dim ws As Worksheet
Dim numberofrows As Double
[Code] ......
View 11 Replies
View Related
Jan 13, 2009
Hi all, this might sound really easy but I have a Named Range in excel 2003.
Now I'd like to write a VBA code to check if this range exists before carrying on futher computations.
View 4 Replies
View Related
Jun 4, 2012
How I can confirm whether a particular value exists in a range. i.e.
ABCD11Yes/No22
Does the Number 4 exist in the range
A1:A8?334455667788
View 3 Replies
View Related
Jul 1, 2014
I'd like to automatically populate a 9 box grid using data from Excel. The number of names for each rating can be anything from 1 to 100 so the size of the grid would need to be adjustable. I would like one table for each function and grade. Attached is what I'm trying to do. In the 'example tables' sheet, I've shown how I'd like the tables to look, but with my live data the tables would be bigger so I'd like to do one table per worksheet (or possibly one function per worksheet - so 3 tables).
View 3 Replies
View Related
Oct 11, 2009
I want to automatically populate C2 with the most current date from B9:B14.
I can't seem to figure out the formula.
View 3 Replies
View Related
Dec 31, 2012
I have a spreadsheet that contains many lines of information that will constantly be added to and updated. One of the columns contains a drop down with 4 possible options. I am trying to get the data to copy to a sheet based on 1 of the 4 options selected. So for example, if Education is selected, then that row of data should also appear in the Education sheet. Same goes if one of the other options is selected.
I have seen several examples on this forum, but they are mostly macros (or formulas that don't quite apply). I am trying to use formulas so that the information is updated live so macros don't really seem to be a viable option.
I am including a sample of the spreadsheet with data on the main sheet and how I want it to look when it is transferred over to the other sheets. I had to substitute a lot of the information (with numbers or filler words) due to it being confidential.
View 6 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 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
View Related
