How do I write a reference from a drop down list to another sheet and have
the formula do the if function so that it can do several arguments and return
"direct" if true and "indirect" if false.
I have a photography business and I'm building a spreadsheet to help me better lay out my cost/price/profits of my print orders. I have created a spreadsheet with all my vendor prices for print sizes and print finishing services. I have created a pull down list for my print quantity, Print size, it then references the cost and markup % to give me a total cost and price. The trouble I'm Having is that i want to also have a pull down for the finishing services and have it reference the qty, print size, and then know what the cost is for that finishing service and then give me the cost and price with markup.
I'm certain that if an expert had a look at the sheet it would be a simple "oh all you need is this formula" and presto I'm back in action finishing the spreadsheet. I have attached the worksheet.
The sheet shown, Complaints, lists on each row information on a single complaint. This information extends from Col A to Col Y (though not that way here, the data actually begins in row 3). I want to highlight a Customer cell, say H227 for Customer A, then activate a macro that (1) recognizes the active customer (here A), searches the list, identifies each row where A is the customer and then writes each row into sheet "Event" beginning in row 4. I should then see the same A to Y cells of information, beginning in row 4, filled in sheet Event for the six customer A complaints. Note, the actual data in sheet Complaints involves 300 rows of data, and over 100 customers can be on the list, some up to 15 times. I know that I could do this by filtering the data, copying and pasting, but a simple macro would be quicker for the group of individuals involved. Note, I then use this information to populate a chart.
Excel 2010ABCDEFGHIJKLMNOPQRSTUVWXY226#INITIATION MonthINITIATION DATECRM Transaction IDSales OrderSalesSHIP PlantCUSTOMERCITYST/PROVPRODUCTProductLbs InvolvedPROBLEM Complaint
[Code]....
I have an excel workbook with two sheets. Sheet 1 has a list of people names, rank, address, and phone numbers. Sheet 2 has boxes where a user can select a last name from all last names in sheet1. What I want to happen after the user selects the last name in sheet 2 is for it to automatically fill in the rest of the data (ie Rank, First Name, Address, city, Home phone number, Cell Phone number). What do I need to do in order for this to work?. Attached is a very small example of my much larger project.
View 3 Replies View RelatedI'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.
View 2 Replies View RelatedI need to have a drop down list which displays a different set of values depending upon the value selected by a previous drop down list. ie. (drop down box 1)= x, y, z. (drop down box 2)= either x1, x2, x3, or y1, y2, y3, or z1, z2, z3. I can produce a single drop down box thats not a problem but linking several drop down boxes is beyond me .
View 4 Replies View RelatedI have a number of macro's which correspond to shift numbers 1 - 23
I would like to have a drop down list on the front sheet so that the user can select the number at the end of the shift which activates the corresponding macro
I am trying to create a list, I know how to do this on the same sheet, I need to be able to have the list informaiton on a data sheet that contains other information that I need to have to populate other parts of the sheet. then I need to add this list data so that I can have drop down options so that users can pick from selected work descriptions
View 2 Replies View RelatedSplit off from
Dependent/Linked Drop Downs In Cells
Originally Posted by Dave Hawley
Try it now. You hadn't named the cells that the Validation lists are Dependant on. Not bad for a "moron", hey?
Things get pretty stressed around here, especially when your commander depends on you as much as mine, and just between the two of us, it is twice as hard when your commander is younger than you. Let me try it real quick. Auto Merged Post Until 24 Hrs Passes;Ok, that works great, now I have to go back and add all the real units and soldiers. Fun, fun, fun. BTW, you wouldn't happen to know how to add a first worksheet that would have buttons to take you to the different sections? I know how to do the hyperlinks, but I think I read something on here about comboboxes?? If I need to post this somewhere else, then just push me in the right direction. I am use to pushing in the Army, it's our life!!
I have A dropdown list (form control combo box) ...in that the values are jan,feb,mar,apr... property of cell link is given as C4..... now my doubt is suppose if i select mar from drop down, in sheet the cell C4 should Show Mar only.....
View 6 Replies View Relatedhow to get a 2nd drop down box to update the range (Not just use C6:C7)based on selection in 1st drop down box.
Example:
If Fat, Use range from C6:C7
If Skinny, Use range from D6:D7
Note: I tried putting an if statement in the input range for box2 but won't accept it.
how to add drop list box or combo box in this yearly time sheet so every employee has his own record in this time sheet so when ever i select name from drop list all info changed, i did include table in sheet 1 as an example.
View 4 Replies View RelatedI'm not including my spreadsheet because it has company information however, I will try to articulate my request as best I can. I currently have an Excel spreadsheet with about 20 worksheets, I would like to be able to click on the first worksheet, create a "Drop Down box" or "Data Validation" box or whatever I need to do to be able to view information in a range of cells on ANOTHER worksheet within the same workbook.
I've tried Data Validation, Drop Down Boxes, Define name ranges, all to NO avail. I'm missing a step somewhere, because I'm still not able to view the information on the first worksheet.
I'm need to add a droplist with periods 1,2,3,4 etc... that references a cell then populates that same reference in a cell raange. Example: cell E1 contains the droplist with periods 1-4 as choices to select from, when you select period 1 it populates the dates jan 15 in cells E4:E7, if you select period 2 it populates dates jan 31, etc. how to add the droplist, how to expand the list, show the formula and how to expand it as I will have more data than this short example.
View 3 Replies View RelatedI am trying to create individualized worksheets based off a master worksheet. I have different committee names that I want to appear on the individualized sheets, based on whether the person is apart of that committee or not. On my master sheet, I choose the persons name under the committee and I want that committee name to show on the individualized sheet. For example, I want A1 on Master to show on Name 1, B3 and A10 on Master to show on Name 1, C3. Can I write a formula to achieve this or or am I asking too much? In my actual file, I will have about 10 names and 5 committees with one person being on up to all 5 of the committees.
View 1 Replies View RelatedIn the following example the costs for each FGI SKU are on detail cost build sheets each named simply 600001, 600002, etc.
To add a summary I simply list all the SKU's on a summary worksheet and reference each detail sheet. The problem is with large numbers of SKU's it gets clumsy having to create a reference for each sheet.
Is there a way to have the formula in the cost column reference the detail sheet based on the value in the FGI SKU column?
FGI SKUCost60000111.34 60000211.34 60000311.34 60000411.34 60000511.34
So to reference the cost for the 600002 SKU instead of this...
=+'600002'!$E$37
I would like this...
=+'value in cell with 600002 in it'!$E$7
I am sure it is easy as I have done this before, but can't remember how or where I have put the code.I have a spreadsheet with multiple worksheets (The amount and names of sheets will keep changing). On a front sheet I have a dynamic validation list in cell "L9" that will always have the full list of spreadsheet names. The idea is that I will choose which spreadsheet to print from this list. Press a button and only that sheet will print.
View 4 Replies View RelatedI m using Data/Validation function in Excel2003? Is it possible to have reference list of drop down input from another sheet? I recorded the macro for this function and had the following result
Sub Macro1()
Range("A1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$C$2:$C$6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
How can I change reference List from the same sheet $C$2:$C$6 to "sheet2!$C$2:$C$6" or it is a limitation for this function?
I have a drop down box in column N, however I would like the options in that drop down box to change depending on what appears in column M.
If column M Says "Lapse" then I want one drop down box to appear in column N, If coumn M says "NTU" then I would like column N to show different drop down options.
When ever I update my external CSV file and hit refresh all data, I get #REF! Errors. The CSV file has a table that changes daily. Thus the "A" Column has a different number of rows depending on the day. The problem is that Monday the CSV file will contain 700 Rows, while on Friday it will have 200 Rows. I end up with a lot of #REF! Errors friday. How do I write the code so that (A561 for example) does not change regardless if it can reference the target cell or not? I even thought of making a macro that just copied the formula into all of the cells after each refresh, but there must be a better way.
Example code
=IF(A561>0,IF(Start!$H$2="Monday",'calculations-mon-sat'!O561,calculation!O561),"")
=IF(#REF!>0,IF(Start!$H$2="Monday",'calculations-mon-sat'!O567,calculation!O567),"")
I have 6 worksheets that are identical cosmetically. Each has the same drop down box list with the same name (dropdown1), however the cell link on each sheet is different. (ie. sheet1 dropdown1 cell link refers to a sheet ' lookup' cell $K$1, sheet2 dropdown1 cell link refers to sheet 'lookup' cell $K$2, etc.) I have the same command button in each sheet that performs the same task so I wanted to keep one module for all 6. Problem is I set a variable to the cell link in the code, and this has to be changed.
DropDownValue = Sheets("Lookup").Range("K1").Value
Is there any way I can set this variable using a property of the drop down list?
DropDownValue = dropdown1.celllink
I don't want to create 5 more duplicate modules just to change this one bit of code (I only use it this time in the code!). If there is no way to get the cell link value of the drop down, could I possibly put a worksheet change event on the lookup sheet to populate a certain static cell with the value of the last cell change?
I am trying to create a fairly simple spreadsheet with about 8 columns and about 400 rows. One of the columns features a drop-down list with about 8 or 9 different options. Dependant on which option is selected, i would like the entire row to change colour with that option.
For example:
FAILED - whole row changes red
SUCCESSFUL - row has no fill
Tested - row changes to orange
etc.
Is this possible within Excel 2003?
I am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list. Example: Cell A1 returns a value of 15
A drop-down list displays the values 1 through 15. Cell A1 changes to 20
Drop-down list displays the values 1 through 20. I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.
I'm doing the website for my company and things have to be entered into the site via spreadsheets. I have a list of numbers
e.g. 3652, 2845, 50925, 4809, 18392
that need to have the spaces removed in order for them to work.
e.g. 3652,2845,50925,4809,18392
There are over 500 cells with multiple numbers in each, so I don't want to do it one at a time, and rightly so, because Excel keeps changing the format and giving me something like
3.6*10^30 and loses all my commas.
Is there an easier way?
I am trying to figure out how to reference a number of cells from accessing one drop down menu. I have a list of products with pricing and logistics. I would like to have the drop down menu in another sheet that will access a specific product and the pricing that goes with it. I know how to do it if i wanted 100 seperate sheets, but I would really love an all-inclusive page that refreshes with the drop down menu. I have a copy of the file if that i hope will be helpful with colour coded cell references.
View 2 Replies View RelatedSee attached my workbook, I have a user form and when the OK button is pressed I need to get the data to write to the master sheet. Sheet1 behind.
Is there also a way I can re-populate all the data back to the userform using my search URN combo box?
Then the recorded can be altered and when update is pressed on the user form the record just updates on the master and not adds another record.
Waiting time sheet Basic.xlsm
I'm attempting another macro to copy the next years budgeted expenses from the LT Tracking tab to the Expense Tracking tab.
The result must be in the formula " ='LT Expenses'!D9 "
I have entered the following, however there is a syntax error on the last line: Sheet6.Cells(9, lcol).Resize(UBound(x, 1)) = x
Option Explicit
VB:
Private Sub Rectangle2_Click()
' NewExpenses Macro
' Transfers LT expenses to Expenses tracking sheet
[Code].....
why my drop down list defaults to the middle of the list when first clicking on the arrow that opens the list? I created an alphabetical list of all US airports but when I open the list, it starts at "D" instead of "A" which is the top of the list.
View 5 Replies View RelatedThe attached excel file arrivals page and departures page Serial number to compare current on the data up-to-date page, I want to copy.
up-to-date on the "F" column is copied to the page on which you need to print.
I making a template for multiple users to import data into for analysis. I want them to be able to delete and drag-and-drop data to get rid of any bogus values. However i need forumlas that reference those to NOT follow the values as there as moved. As a simple example say you have two columns, column A is a set of random numbers, column B is the number from coumn A times 2. If take the bottom half of column A and drag it up one cell, overwriting a cell, now the cell in column B next to the overwritten cell has a #REF err and ever cell below that now references a Cell in column A from the previous row. I want a formula that references a static address and will not follow the cell if it is moved.
If I use named ranges rather than specific cells references it works for the above example, however for my actual form it does not. Even though the ranges and formulas stay the same I start getting incorrect values. If there is no other way to this I will try to figure why the named ranges do not work.