Populating "band" Based On Value In Another Column

May 11, 2007

I am trying to populate Column BB based on data in BA. Explanation: If BA2 is less than 1, BB2 should equal "00-01". This should run until BA is blank. Sample value in BA: 6.68.

I'll paste in my code which is causing a run time error 13 type mismatch error starting with the line in red below. I wonder if I am even close?

Dim m As Long
For m = 2 To Range("BA65536").End(xlUp).Row
Select Case Cells(m, 53).Value
Case Is < 1 'line where error starts
Cells(m, 54) = "00 - 01"
Case 1.1 To 2
Cells(m, 54) = "01 - 02"
Case 2.1 To 3
Cells(m, 54) = "02 - 03"
Case 3.1 To 4
Cells(m, 54) = "03 - 04"
Case 4.1 To 5
Cells(m, 54) = "04 - 05"
Case Else
Cells(m, 54) = ">5"
End Select
Next m

View 9 Replies


ADVERTISEMENT

Extract Time Band From Column

Feb 27, 2013

I am facing an issue right now to extract time band from a column. Below is the data from where I have to extract Time Band only. I tried different things but its diifcult for me to extract time slot.

New Rate - 19:30 - 19:59 -

Effective From 1st May - 00:00 - 00:59 -

- 07:00 - 07:59 - Branding

Morning - 00:00 - 01:00 -

Night - 21:00 - 22:00 -

LPT - 23:00 - 00:00 -

- 22:00 - 23:00 - PT

- 22:00 - 23:00 - BEFORE EP

The output should be like this
19:30 - 19:59
00:00 - 00:59
07:00 - 07:59
00:00 - 01:00
21:00 - 22:00
23:00 - 00:00
22:00 - 23:00
22:00 - 23:00

View 5 Replies View Related

Populating Cells Based On Row And Column Labels Matching

Oct 24, 2013

Having a bit of trouble trying to get excel to pick up text in one sheet (sheet 2) and populate cells in another (sheet 1) if the row (row 1) labels and columns (column a) in both sheets match. hope that makes sense? I've tried googling this to no avail, i've also tried index-match however i keep getting errors.

View 3 Replies View Related

Time Band

Apr 16, 2009

I have column A1:A100 which contains times which vehicles are off site and B1:B100 which contains times when they are back on site I need a formula to look in these colums and tell me for any time ie 21:30 how many vehicles I have off site.

View 9 Replies View Related

Calc Yrs Serv In Different Band Ranges

Jan 30, 2007

I am wanting to calc the yrs of serv in different age ranges for example

Start Age = 17
Yrs in Serv = 25
Age to date = 43

Band 1 (21 yrs) IF(start age is =41,age to date - 41,0) =2

Band 1 = worked 4yrs from an age of 17 to 21
Band 2 = worked 18yrs from an age of 22 to 40
Band 3 = worked 2yrs from an age of 41+

I am stuggling with the formula to calc Band 2.

View 9 Replies View Related

Auto-Populating Multiple Values Based On Two Criteria Based In Comboboxes

Aug 7, 2013

I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.

View 2 Replies View Related

Populating Picture Based On Number?

Jul 2, 2014

I have a large excel workbook that is full of data about certain parts. I am able to enter a number and populate the sub item using VLookup but I would like to populate a picture based on the sub item. The pictures are located in there own folder inside the folder where this excel file is located.

View 3 Replies View Related

Populating One Sheet Based On Values Of Another

Jun 22, 2012

I have a workbook with two sheets. Sheet 1 has raw data that contains contacts with multiple statuses. I need to pull out only the contacts that have a positive status and put them into Sheet 2.

I would like there to be no spaces between the contacts in Sheet 2. I'd also need all of the data pertaining to each positive contact (so not just their name, but all of the data in the row that relates to the contact).

View 3 Replies View Related

Populating Combobox Based On Selection Of Another

Dec 25, 2009

I have a problem involving two combo boxes in a user form.

I need to populate different “lists” to combo 2, based on a selection in combo 1.

I browsed through the forum but could not find any solution that made sense to me.

Here are the specifics:
Combo 1 (called Cbo_Act) is populated with a list based on a named range in one of the sheets. The range is named “activity” and holds 2 records (“Income” & “Expense”).

This list is loaded as part of the form initialization.

If the user select “Income”, I need combo 2 (called Cbo_Act_Type) to show a list of various income types (derived from a dynamic named range called “Income_type”) and if the user selects “Expense”, I need the same combo box (Cbo_Act_Type) to show a different list, specifically – a list of various expense types (derived from a dynamic named range called “Expense_type”).

I know that this should be with a Cbo_Act_Change () routine, but for the life of me, I can’t figure out how to do it.

View 9 Replies View Related

Auto-Populating Based On Dropdown Selection

Dec 4, 2013

I am trying to create a sheet for identifying work hazards based on the job assigned. So what I have set up is a drop down list to select the job type. Once this job type is selected I want the pre written hazards to autopopulate in the hazard box. I would also like the required PPE to appear in the PPE box for the job selected.

I have 6 different choices in the drop down list. For each choice I have the hazards and PPE in a separate sheets in the worksheet.

Is there a way to get this information to autopopulate?

View 1 Replies View Related

Auto Populating Based On Specific Data

Jan 7, 2014

I have a unique task i am trying to achieve (when i say unique i mean i am out of my depth). I am trying to take specific data from a couple of different sheets to populate other sheets whether it be copying the text of fill cell (color)

Excel Automation Test.pdf

I have Attached a PDF detailing.

View 2 Replies View Related

Populating A List Based On Four Toher Lists

Oct 3, 2008

I have Columns A:D with a heading at the top. the user can then type organisation names under the correct column. There is a maximum of 30 entries (rows 2:32).

Problem One:
Column E is a hidden column and I need cells E2 and downwards to be a list which gets populated by ALL the organisation names inserted into $A$2:$D$32. I don't want there to be any blank cells until the last entry in column E (if that makes sense). how to populate this list?

Problem Two:
At another part of the worksheet, I have five cell where the user has to select from a list. The five cells correspond to the lists in columns A:E. Is there any way to avoid having blank options in a list using data validation (say if the user has not entered 30 organisations into one of the columns)?

View 5 Replies View Related

Populating A List Without Blanks Based On If Statement

Apr 17, 2009

I have created a checklist using "Marlett" checkboxes. I have the names of choices in column B the Checkboxes in column C. In another sheet I want to populate only the names of the choices chosen without any of the spaces that a traditional if statement would populate if it was copied down a column.

View 3 Replies View Related

Populating A Data Validation List Based On...

Apr 18, 2009

I want to populate a Data Validation based on values in another cell on another worksheet--but, I want to populate it with the values stored in the cell right next to the cell. http://i32.photobucket.com/albums/d3...n/untitled.jpg. would be an example....

What I want my Data Validation List to do is to look at column B and wherever it sees a certain color--for example, red, I want it to put the corresponding value in column A in the list.

View 5 Replies View Related

Populating Data From One Sheet To Another Based On Selection

Jan 14, 2010

I need help to create a formula that would grab data from one sheet and populate another sheet based on the employee that is selected from a drop down list.

Attached is a draft of what I am looking to do.

Basically this would allow us to enter employee variances from several employees on one sheet and get a detailed break down of their history on another sheet.

View 8 Replies View Related

Populating Drop-Down Based On Selection Of Another Dropdown

Sep 12, 2006

There's a table with all the raw data in it

camp name
session name

to put in the correct format I am thinking of usinf drop down boxes so that the user can selct the camp name from drop down and based on that the session name can be populated and alos the rest of infomration of that accput in to the specified fields

View 6 Replies View Related

Populating A Cell With A Column Header

May 13, 2013

I have a spreadsheet with a number of columns from a database export, where values in the column are either "TRUE" or "FALSE," Depending on whether or not the box was checked in the database. I am wondering if there is a way to populate a new cell with the text from the column if the value is "TRUE."

For example:

BOY
GIRL
John Doe
TRUE
FALSE

Is there a way that I could have another cell populated (I guess via function or macro) with "BOY," since the option for boy is true?

View 8 Replies View Related

Populating Worksheet Column From Combobox

Sep 3, 2009

I have a userform with comboboxes and listboxes etc. The comboboxes have drop down lists which are populated from data in columns from a worksheet.

1.) During the execution of the userform, a value is selected from the combobox, which is place in a column on another worksheet. So far so good. If I select the same value again, nothing shows up in the row below the first selection. If I choose a different value it shows up. I have tried using different properties of the combobox but have not been able to get around this.

2.) I would like, when the excel file is opened that the userform is ready to go, ie enter data. I have tried putting the
Private Sub Workbook_Open()
UserForm1.Show
End Sub

in "ThisWorkbook" module but have had no success with it.

View 12 Replies View Related

Populating Column In Excel From MS Word?

Nov 21, 2011

I am an intermediate MS Excel user and I have a question for those of you who are more savy. I am trying to figure out if it is possible to populate a field in Excel by reading the footer of a MS Word document.

View 7 Replies View Related

Populating A Combo Box With Column Headings

Mar 23, 2007

I am trying to write an advanced sorting macro that sorts on more than 3 columns. I need to poulate a combo box with the column headings, so that I can choose which column to sort on first, second etc. Now I am a complete beginner at this sort of thing, but I believe that ranges are required. I have tried using rowsource properties, but the combo box only ever displays one column heading.

View 2 Replies View Related

Populating A Table From A Separate Sheet Based On Ranking

Mar 21, 2014

I'm looking to populate tables for specific tasks that my site performs and compare their performance against the other top sites in the company. I need to pull the site # and their performance based on the task, ranking them from first to last.

View 5 Replies View Related

Excel 2007 :: Populating Data Based On Two Criteria?

Mar 28, 2014

I'm trying to populate a table based on two different criteria in a another table. On is based on a date and the other the number of a unit. I'm trying to get the data in sheets Week 1 and Week 2 into the format in sheet Example. Is there a way to create a series of functions, filters, etc that I could use to create a macro to do multiple sheets or at least a whole sheet at once? I've been trying to think through it with my limited knowledge of filters and functions

Here is the file with an example and I'm using Excel 2007: Example.xls

View 2 Replies View Related

Auto Populating Columns In One Sheet From Another Based On Criteria

Jul 28, 2009

I've been trying for some time now to figure out how to have one sheet automatically fill in another based on a larger data sheet. I'm trying to find a way to take a monthly calendar which contains a row for each employee and column for each day and use that to create smaller lists on another sheet. Basically if someone puts in for a day off, or has a medical appointment etc, that person has a coded single letter for that day for tracking and planning purposes. I want to have a second sheet that references those codes and makes individual lists (the following people are on vacation, these people have a medical appointment etc.)

View 3 Replies View Related

Auto-populating A Table Based On Ranked Values?

Jan 21, 2014

I am looking to create a table with ranked projects from a different tab that is able to pull data from the same tab and change based on value updates. It is difficult to explain, so I have attached the file below.

What I am looking to do is pull the ranked projects from tab #7 - Scoring to tab #5 - Final list. Each project has a unique ID#, but a lot of the columns I need populated are not unique. I tried using a combination of arrays, but it always returned a #value into the cells.

P.s: In tab #5 - Data sheet, the formulas in I5 and P5 work, but are pretty ridiculous, and I'm pretty sure there must be an easier way. I was looking up values from the 8-budget tab to try and determine which group column H values were from. The only complication I was having was that a project can be allocated to either a functional unit, or group so I need to have the "group" total as independent, not just as a sum of the functional unit totals. Like I said, these formulas work,

View 4 Replies View Related

Populating Combobox Based On Selection From EXCEL Worksheet

Jul 25, 2014

I'm trying to do some userform development with multiple dependent comboboxes, but I am having trouble populating the third and last combobox. First, I populate the 1st combobox on the userform initialization. I can get the first 2 comboboxes to populate correctly, but I can't seem to get the 3rd combobox to populate correctly.

The 3rd combobox takes the selection from the 2nd combobox and searches column "A" in the worksheet "Chassis Specs" and populates the 3rd combobox with data from column "B", there will be repeat items in column "A", but all those occurances should populate the 3rd combobox with the data from "B".

I am attaching my whole workbook.

Chassis Specs Info_Build_REV B.xlsm

View 14 Replies View Related

Userforms - Populating Textbox Based On Combobox Selection

Oct 26, 2003

I am creating a Userform and one of the elements on it is a combobox where the user has to select their name. On the same Userform, I want to have a textbox (or listbox or another combo box if necessary) which will show the users default cost centre based on the initial combobox selection. I've tried doing this various ways, but I can't get the textbox to update when the combobox selection changes.

View 7 Replies View Related

Macro For Populating Cells Using Variables From Another Column

Jun 20, 2006

I need to create a macro, where the contents of a particular cell are dependent upon summing values based on a word desription from another column. For instance, I would like cell E10 to include a numerical value from cell D5, but ONLY if cell C5 is populated with the word "trust" as opposed to "equity". Conversely, I would also have a macro in, say, cell E11 that would do the same for the word "equity" in column C. I would like the macro to hold for the entirety of different words in column C.

View 6 Replies View Related

Select Button And Starts Populating Column

May 4, 2007

I have this macro for a button that needs to start populating a column
with Xs. I want to populate cell F2 with data from a different sheet with Xs.

ub Create_button_macro()
Dim sButton

Set sButton = ActiveSheet.Buttons.Add(156.75, 36.75, 73.5, 22.5)
With sButton
.Characters.Text = "Program 1"
With .Font
.Name = "Arial"
End With
.OnAction = "Button1_Click"
End With

End Sub

Sub Button1_Click()

End Sub

View 9 Replies View Related

Populating Two Listboxes With Sheets Based On Cell Value; Printing Their Selection

Jul 29, 2009

I would like to do is have 2 listboxes. In the first listbox I would like the name of all the worksheets which contain the words "elective class: " in cell C7.

In the second listbox I would like the name of all the worksheets which do not contain the words "elective class: " in C7. I need this only to source from worksheet 7 onwards however.

The listboxes are called ListBox1 and ListBox2 respectively.

Also I am using this to print and I have a button which currently selects all the data in the first listbox and another button which prints all the selected data. The code being used for this is:

View 2 Replies View Related

Excel 2013 :: Populating Column Without Duplicate Values

Jul 14, 2014

I've got a column of client company names and because it is based from accounts, the companies names appear many times. I just need to populate a new column with unique names, so I have found a formula to do this:

=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

However when I do this, I CtrlShiftEnter and drag to copy that down, and it's just a copy of the original list - client names are appearing on my new list multiple times. I don't see how this doesn't work... I'm using 2013.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved