I am trying to create a form with dropdowns (see attached file), so that when a particular company is selected in the first drop down, the second and third only display the relevant business units and departments respectively.
The INDIRECT function has confused me and I am not sure how to use it.
I have a drop down in IE in which four values are there
I will need to select each one at at time to make some change and move to next dropdown
the dropdown in IE should ideally have 4 dropdowns 01,02,03 and 04
However due to vendor errors we may have any of the above missing from dropdown or extra orderpoints in the dropdown like 05
IE.document.getElementById("vendororderpoint").value ="01" is the code to select order point 01
I need an alert in excel if any of the 4 dropdowns is missing.
I am trying to wrap my brain around dependant dropdowns and have hit a brick wall.
Using the Contextures web site [url="http://www.contextures.com/xlDataVal02.html#TwoWord"] I can get part of the problem to work but when I try to add a third dropdown i get lost. Attached is a sample of the workbook.
IS it poss to sort an entire sheet by one column then another, but to exclude certain rows that include certain data.
This is what i mean, I have a sheet where data is entered on a daily basis. this data has tobe kept with in its dates. Each day starts with a start time and ends with an end time, and on occasion a break is included within the data. then between dates there is a blank line.
IS it poss to sort the data and exclude the blank lines and the start, end and breaks within the data, but keep the data within date, so all data for the 12/4/09 stay within this block...?
I have a inventory report that has multiple stores. I am trying to add the total of all stores in two columns named "AE" and "ASC". Anytime the store name says "AE" i want to add the totals of that store in the "AE" column. Anytime the store name says "anything except AE" I want the totals to add in the "ASC" column. Hope this makes sense. How do I do this? Do I use conditional formating?
View 9 Replies View RelatedI have
Userform
Textbox1
Textbox2
textbox1 containing the province
textbox2 containing the city
If didn't click any value of province the city textbox2 don't have any value and if i click the one of the province the city will now have a value . Is that possible
Here is the picture : [URL] .....
I have turned off show zeros on a sheet. This is good in general, but for certain cells I would like excel to show zeros if the formula output for that cell is zero.
Background information: I am working with a very large workbook, and the final sheet is my output file. This sheet is then exported to a CSV file and imported to another program in my workflow process. I need most blank cells to remain blank, so that they are interpreted as blank in the output program. Some of my cells are actually zeros which I intentionally inputted as zeros...those should be interpreted as zero and not blank.
I'm not a novice but this might be something simple I didn't realize. I am creating a quoting sheet for a steel fab company. I have created dopdowns for the different materials and sizes but the dropdown is way too big. I need to have a column that helps me filter the dropdown more so I don't have to scroll through litterally hundreds of steels to find the one I need. The good news is that most steels have prefixes that make them easier to filter.
For example:
L-3x3x1/2
L-2x2-1/2x1/4
C6@8.2#
C8@11.5#
W12@16.0#
W21@44.0#
What I'm looking for is a way to have a dropdown that has all of the prefixes (L, C, MC, W, PL, etc) in it and when I select one of them the next column (the actual description of the steel as shown above) will only give me the specific steels for that steel shape (L,C,W,etc)
Currently I'm putting one row of say the MC shapes and one row of the C shapes and one row of the L shapes then copying them as needed. It's a lot of work and you can easily make a mistake.
I have two dropdowns, both use a different source lists and both use the same reference cell to deliver the result, My problem is I need the contents of the cell to be deleted and the new result inserted. At the moment if the cell is already filled the new result is put in the next column.
What I would like to happen is to have two dropdowns one above the other (B2 and B4) and the result in a cell below them in B6, which ever dropdown is used the result will fill the cell B6 this cell is then used for vlookup.
I have a cheque list with Column A for Cheque number, Column B for Date, Column C for Payee Name and Column D for Amount
Cheque number ranges from sequence 1200 to 3600
Is it possible to set up a formula to sum the amount of cheques in column D based on the cheque sequence number of column A . In other words if based on criteria cheque sequence number is
I have this code:
Code:
Sub comments1()
With ActiveSheet
With .Rows("180:186")
.Hidden = Not .Hidden
End With
[code].....
Now, when I run this code, sometimes (but oddly not every time) the dropdowns do not reappear where they were originally placed.
i have a VLOOKUP formula
=VLOOKUP(W3,CAT_A,3,1)
where CAT_A is a dynamic named range on the CAT sheet, I also have CAT_B thru CAT_F
I would like to make the VLOOKUP formula neater by using INDIRECT and the initial letter from column I as follows
=VLOOKUP(W3,INDIRECT("CAT_" & I3),3,1)
However I keep getting #REF, where have I slipped up
Biz develop an xls tab that will have dropdowns.
Essentially, one dropdown will filter another dropdown... etc...
Would also want a DISTINCT list for all dropdowns...
I've used the DropDown's from the CONTROL toolbar and although it seems easy enough to supply a datasource, not sure how filter/distinct might work.
I have a project that has a variable number of worksheets, dependant on the number of models in the job. These worksheets usually only use a single page, but may use two pages if the model is large (not often the case though). I have a "Template1" worksheet that is copied as a whole, to equal the number of models.
To minimize the file size that is generated when copying out the "Template1" worksheet, I have put the template of the second page on a separate worksheet "Template2". Now the initial copy is only of a single page, and the second page is only copied into the new worksheet if needed.
Sub AddPg2()
Dim CrntPg As String
CrntPg = ThisWorkbook.ActiveSheet. Name
Application. ScreenUpdating = False
ThisWorkbook.Worksheets("Template2").Visible = True
Worksheets("Template2").Activate
ActiveSheet.Range("A47:T96").Select
Selection.Copy
Worksheets(CrntPg).Activate
ActiveSheet.Range("A47").Select
ActiveSheet.Paste
ActiveSheet.Range("D58").Select
ThisWorkbook.Worksheets("Template2").Visible = False
Application.ScreenUpdating = True
End Sub
If I do this process manually, I toggle DesignMode "on", and it works, but I if I record the process, toggling DesignMode does not show in the code. how to include the comboboxes and checkboxes in the copy/paste process.
I have started to create a sales order. I have three columns that I will be working with. I have three lists in a separate worksheet (worksheet 2) in the same workbook. Column A has part numbers. Column B has descriptions of the part numbers in Column A. Column C has prices for the parts in Column A.
I have created a drop down list in Worksheet 1 from the list in Column A in worksheet 2. What I want to do is somehow link column B and C to Column A so that when an item is chosen from the drop down list, the information will pull through.
I need to setup a dropdown list (I already have the dropdown created) so that once a value in the list is used, it disappears from the list. Now for the monkey wrench...It need to completely re-populate once the list that is being generated from it is cleared.
I also need to copy a value from one sheet to another sheet, however, the location needs to be matched with a corresponding value from the first sheet. Hopefully it will be easier to understand within the workbook (I feel like I'm not explaining it well).
I have attached a sample copy of the workbook and what I am trying to accomplish.
Mens_Dorm_Sample.xlsm
I have a workbook with several columns, some of which are for internal company use only.
Sometimes we need to send an updated list to business partners, but they don't need all columns.
Is it possible to make another workbook which takes only specific columns, and excludes the stray info at the bottom of the original workbook? See examples.
Alternatively: Would it be simpler to make an extra sheet in the original workbook, and somehow choose "Safe only this sheet to a new document" whenever someone requests the list?
i have a column that has some values in it. Most values are #N/A but some are numbers. I am trying to create a function of some other snipet of code that would substract one value from the previous value and have the result next to the first value.
I am trying to wite a macro that will allow me to pull up specific files. All files will be located in the same folder. For example, I want to type the workbook name "sawn" into a cell and hit the macro button. The macro will then look in a "pool" folder for the workbook named "sawn" and open it. Is this even possible?
View 14 Replies View RelatedDependent Dropdowns from a Sorted List:
i have one file here..
how did they created validation here.
is there any macros?
i tried..but i didnt able creat like this.
i got this file in this link.
http://www.contextures.com/xlDataVal13.html
i saw this problem in this post..
http://www.excelforum.com/excel-prog...hierarchy.html
I have a spreadsheet with many many columns...the top of each column has a string (it's a bird name). Below in the same column are various blank spaces or letters (defining species abundance, if there), example:....
View 2 Replies View RelatedI recorded the following macro which deletes specific buttons and drop downs from a sheet. I would like a macro that can delete all Buttons and Drop Downs without them being specified.
View 3 Replies View RelatedThis is a formula in an existing spreadsheet that I need to amend.
{=SUM(IF(WeekNo=2,IF(Workgroup="REASSIGN - CAP",1,0)))}
‘WeekNo2’ and ‘Workgroup’ are names defined and are offsets that define two separate columns.
I have created a new name ‘realclosetime’ and it is an offset to another column of data.
I have tried to change the existing formula so that it is not actioned if the ‘realclosetime’ is empty. It’s a date and time field and if it is not completed I don’t want to be including that row in the SUM calculation otherwise I end up with N/A’s.
I am trying to copy selective data from one worksheet to another. My intent is to copy data in column F below to another worksheet in Column H. I only need to copy numeric data in Column F. Below is the screen shot of my data set. There are 244 Batches and each batch contain 4 members. In another words, I want to copy data for 4 rows (F2 to F5) then skip three rows (F6 to F8), Then again copy 4 rows (F9 to F12) the skip 3 rows and so on.
Here is the sample data -
ABC DEFGHIJ
1
2JohnAccounts841.5
3JackReceivable842.5
4JimPayable842.7
5JustinClaims843.9
6Next lines of Data contain expenditure for batch 2
[Code] ..........
Is there a way to set up drop downs so that you can type in the drop down box and it searches for the closest match?
View 1 Replies View RelatedBased on selecting 3 parameters of respectively 2, 8 and 70 variable ("size", "from" and "to") I would like to have excel return a line of 12 columns.
All data can be listed in 1120 lines x 15 columns (whereof the data in 3 columns are selectable)
Text-based data (container sized and port names, if you're curious) and the data (transport prices and conditions) are both numeric and text-based.
I'm familiar with VLOOKUP, but this stuff looks to be a bit above me...
Option Explicit
Sub copypaste()
Dim lastrow As Long, i As Long, j As Long, sheetlist As Long
Dim ws As Long
[Code]....
i just wanna execute this coding from particular tab to particular tab (in detail to say- if am giving from and to tab as A & D, then coding should be executed to A, B, C and D alone, and remaining should remains as is).
I have a spreadsheet which contains many rows (>3000) and a few columns.
I would like to make a macro that will copy specific cells depending on the number from another cell (from the first column) and then transpose the corresponding values. My current problem is that the values I'd like to copy (countries) are on a row (horizontal) and I need it on a column (below "Countries") next to the car models (in black: the current sheet, in red: my target):
Number of Countries
Cars
Countries
A
FJ
A
UK
[Code] .......
Is it be possible to create a macro that transposes automatically the countries from the right into the third column (like 90 degrees against the clockwise direction)?
I am trying to get excel to pick a cell from a hiden matrix and showing it on my worksheet. I want to pick the cell using 2 drop down boxes as bellow - 1 drop down picks option on X axis and the second picks using the Y axis and then the coresponding cell in the matrix is displayedMatrix example [URL] Drop downs example [URL]
View 4 Replies View RelatedWe want to transfer row data based on criteria in that row to another sheet. We only want to transfer certain cells in the row. We have a column that is named status that we want to trigger and identify the row for transfer and then its location in the new sheet. We tried vlookup but it would only take the first instance of a specific status in the column and not take subsequent instances. We then tried the index function but could not get it to work correctly. I hope this makes sense. There will be a high volume of data being inputted continuously so I don't think that copy and pasting is a viable solution....
View 9 Replies View Related
