I was just wondering if there was a way for a dropdown list to allow the user to input just the first letter or two of a selection and automatically bring that information into the list.
Type in ch
...the dropdown list will automatically filter to Cherrywood? Is this possible? I have been searching through excel but can't seem to find anything.
I have a list of model numbers that corresponds to part numbers i.e:
model number
part number
abc
123
def
456
[code]....
I can type in A2 = ab and it autofills the model number abc, and have B2 automatically have the part number which is 123 filled in as well.
how best to do this (bare with me I'm not quite a pro at excel yet!). I'll attach my doc (JMP Excel Forum) so you know what I'm taking about. I've set up the data to work in a similar way to this, as you can see I've a table set up (Appliance Sheet) which feeds information to the Main Sheet which contains the drop down lists.
What I need it to do is autofill the wattage column (Main!F8 onwards) with the wattage for the device as described in Appliance!N3 or N4 for example. For the wattage column to equal 28 (equivalent to Appliance!N3) then the following would show in the dropdown selections Type>Computers SubCategory>Monitors Device>BENQ G2222HDL Status>On
Is there a way to do this with the set up I have?
I did have a previous version of the whole worksheet that each Device had its own table and I looked up the wattage using and If formula to lookup a info on the device and knowing where to look it up based on the subcategory
=IF(ISERROR(VLOOKUP(D7,IF('Main UI'!C7="Heating_Cooling",Appliances!$A$7:$B$14, IF('Main UI'!C7="Kitchen_Bathroom",Appliances!$A$16:$B$27,IF('Main UI'!C7="Monitors",Appliances!$A$52:$B$62,IF(C7="Computers",Appliances!$A$64:$B$68,........
And I got it to work but it was getting to cumbersome to add in new devices and update the formulas. If attached that doc (OLD) so you know what I'm talking about. The last two sheets are the same sheets that make up the new document (JMP Excel Forum)OLD.xlsm
I really prefer the new way I've got it set up. Its easier to add new information or options to the dropdown list
I am currently trying to make a digital time card for my place of employment. I has an odd set up and odd date ranges. The pay period starts on the 21st of each month and ends on the 20th of the next month.
The time cards have 5 columns for each week starting on Mondays going to Sunday (also weird its not Saturday to Sunday) What I was hoping to accomplish was being able to select the starting month of your choice for pay period and have multiple cells update the week date range.
Date
Date
Date
Date
Date
**Here would be the drop down month select.
Sept 21 - Sept 22
Sept 23 - Sept 29
Sept 30 - Oct 6
Oct 7 - Oct 13
Oct 14 -Oct 20
I am trying to pull data from more than one drop down but don't the same data to show if already use, example as follow:
First drop down Contains:- Pants
Shirts
Hats
Shoes
Dresses
if I chose Hats it should not show up in the second drop down
second drop down Contains:- Pants
Shirts
Shoes
Dresses
Is this done in data validation or combo Box? if so how?
I am trying to simplify my data entry and I have this idea that I do not know how to tackle, but it would be nice to have it worked out.
I am looking for an autofill macro that will fill column B2 to Bxx with values from 1 to max value specified in cell B1. Lets say I know I have 5 trees I need to enter. I want to enter "5" in a cell as a reference, and everything else will be filled in automatically with press of the button.
To make things even more productive I would like to use value from cell A2 and replicate it through the range specified above in column A2:Axx.
Result: ....
I have this ActiveCell.Offset(0, -1).Range("A1").Select. Selection.AutoFill Destination:=ActiveCell.Range("A1:A6"). However I need to copy the value down, so it is identical to the Range("A1"). At the moment it adds one year to each value when copied down. e.g. A1 = 12.01.09. and it copies it down so I get 12.01.10, 12.01.11 etc... I want them all to be 12.01.09
View 3 Replies View RelatedI am looking for a way for my macro to select cells C4:T4 and then auto fill the formulas down to the last row with data. I have found suggestions when working with one cell to auto fill, but cant seem to find a way to do it with a range of cells
View 2 Replies View RelatedI have a Macro and I need to code it to Autofill a formula in column B with data from A and I need to set it to AutoFill to the last row. Probably basic but I've tried a couple posts previously and I am just not getting it. This is what I currently have. Sorry, I recorded it and have some additional steps in here I don't need.
View 6 Replies View RelatedI have a piece of code that includes the following line:
Selection.AutoFill Destination:=Range("B1:B9414")
I want it to always autofill to the last row that has content in it. I have been changing the value to a large number that I know is always more than the content of the worksheet which changes periodically, but I would rather it always only go to the last row that is not empty so I don't have to go back and delete those extra rows.
I have a spreadsheet with data where the number of rows change daily, in column D.
I have formula in column E which I autofill manually by double clicking on the cross thingy of the first cell.
However I'm trying to do it using VBA, but it always stops at a certain row and not where the column D data stops.
Below is the macro which stops autofilling at row E7762
Selection.AutoFill Destination:=Range("E3:E7762")
Is there a way to amend the VBA to autofill based where Column D data stops?
I have a piece of code that includes the following line:
Selection.AutoFill Destination:=Range("B1:B9414")
I want it to always autofill to the last row that has content in it. I have been changing the value to a large number that I know is always more than the content of the worksheet which changes periodically, but I would rather it always only go to the last row that is not empty so I don't have to go back and delete those extra rows.
I need to write a code that will autofill down column A until the next value is reached and will continue to fill down using the new value. This will continue until the last row in column B. I have attached a small sample file for clarification.
View 3 Replies View RelatedI have a macro that copies 3 cells and a range of cells from various sheets to a new sheet (See attached). I would like the rows in the first 3 columns to populate with the same values as in the first row. I only need to copy as far down as the last row that has been copied over. Problem is amount of data copied over each time will vary, and so will the start point on the spreadsheet as more data is copied across.
View 9 Replies View RelatedI have data in range A2:A20 (column) and I like to see all range data also in row B1:U1. Like B1 cell "=A2" in C1 cell "=A3",... (I like to set some kind of matrix with the same data in first raw and first column.) Is there any autofill solution to this problem. Copy and paste is not usefull, because range A2:A20 is editable and I need direct link to range B1:U1. Probabely in future I will need to set range like A1:A700 and autofill will be common solution to this setup.
View 2 Replies View RelatedI fill out this form everyday and sometimes need to type "S" and every time it fills in "Sampling Rate #1" because that's what it says in the same column higher up. Is it possible to turn this off somehow?
View 3 Replies View RelatedI want to change the following code so that it fills a row instead of a column
[Code] ......
This fills column "A" top down but I now would like to fill row "1" left to right.
I have code to fill down to the last row with data for 1 column Can I adapt the code to fill down more than 1 column. current code for 1 column
View 2 Replies View RelatedI am working on automating our truck payment system. When the macro copies the data over to the output printout sheet, it will only copy the information once, even though there are 15 lines that it should be copied to. When I originally recorded the macro, I used the auto fill option to fill the information for all 15 lines. However, when I run the macro the auto fill doesn't work for me.
View 8 Replies View Relatedthe code as shown below so that in column A of worksheet: Data to Text I need an AutoFill from cell A1 to the last row. The value in cell A1 needs to be: 1
If there are more than 1 records then I need an AutoFill from cell A1 to the last row. However, the value in cell A1 needs to be 1, value in cell A2 needs to be 2, value in cell A3 needs to be 3 etc...
I have the following formula that I would like to be able to autofill.
=UniqueItem('Divide Outs'!$A$2:$A$999,1)
The 1 after the comma needs to count up...anyway to autofill a formula like this?
This is a function that i use to display unique listings.
I was wondering if it was possible to have some cells where when I start to type it suggests me from a list from another sheet?
View 1 Replies View RelatedI have multiple worksheets each with varying amounts of data. I have a number in cell A1 that I need to autofill down to the bottom of each worksheet. All of which have varying amounts of data.
RA-0151
0
765804
3308345
168
0
5.74E-07
8.8E-09
1.371696
0
0
[Code] ........
When I record a macro it sets the autofill range as the bottom of what ever worksheet I recorded it on.
How can you write a macro that goes to the last row of each worksheet.
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A122"), Type:=xlFillCopy
Range("A1:A122").Select
Range("B1").Select
End Sub
I have this code present, however the autofill part needs to work by looking at the cells to the right & i dont know how to do this
Sub Merge()
Application.ScreenUpdating = False
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("A10").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[1])=TRUE,"""",RC[1]&RC[2])"
Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, -1).End(xlDown).Offset(0, 1))
Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Range("B2").Select
ActiveCell.FormulaR1C1 = "=LEFT(C2,3)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B235")
Range("B2:B235").Select
I have this code here, it basically writes the formula in the cell, then sends it down to the bottom of the list i have. The problem is: It will not always end at Cell B235. It could be more or less. How can i make it so it will auto-fill to an undetermined end of range?
I have the following code...
Sub updateDB()
Cells(1, Range("D1").End(xlToRight).Column).EntireColumn.Select
Selection.AutoFill Destination:=Offset(1, 0)
End Sub
Its erroring out on the Autofill line... All i want it to do to finish the code is autofill one column to the right after it selects the column after finding the first blank cell in row A?
I have this bit of code;
Selection.AutoFill Destination:=Range("j2:j20000")
But I want it to only autofill as far as the data goes in column I rather than down to row 20000
I am making a macro that takes the cell I have selected, extends the selection exactly 3 columns over, and then autofill extends the formulas 3 rows down.
So far I have:
ActiveCell.Offset(0, 3).Select
Selection.autofill Destination:=Range(Offset(0, 3)), Type:=xlFillDefault
The range(offset(0,3)) in the destination doesn't work. What am I doing incorrectly?
I currently have a worksheet that, using a Form Button, generates a new worksheet that formats column widths, titles and also creates a file list of all files on my hard-drive along with date created and the full file path.
Each file has all of its information in one row.
From the original button that initiates the whole process I need the code to create a delete button for each file in the list using, I presume, the kill function.
The delete button would need to sit in column 'L' starting in cell '5' and autofill down. The kill function would pull the file path of the file to kill from the full file path displayed in column 'K', again starting in cell '5'.
I already have the code to genereate the file information (path, date created etc). I literally just need the code for the creating the multiple form buttons and assigning the macro to each button.
code?
View 9 Replies View Related
