Using List Function With Hiding Irrelevant Outputs
Feb 12, 2009
I am in a fix on how to create a formula where the user can drive the outputs by selecting specific timeframe (placeholder Cell add: C3 on worksheet named Staff Number Calculation).
Based on this selection, the output should be calculated in address C9:H25, now where I am getting stuck is referencing as this output is based on values present in worksheet named "Volumes" and "Time Spent Per Unit".
However, as volumes vary by each month, the number od workers required should also be reflective of changing in volumes worked upon.
For example: ...
View 14 Replies
ADVERTISEMENT
Jul 7, 2009
I am setting up a summary sheet that contains =indirect() functions for workbooks that don't exist yet.
I would like some kind of function that returns the =indirect() function correctly if the workbook exists and just a 0 or blank if the workbook doesn't exist.
I would like to have the indirect function in all of cells that as soon as someone creates a workbook it will update the summary sheet.
View 4 Replies
View Related
Jan 8, 2013
Cells B2:B100 contain a list of sheet names, cells C2:C100 have entries TRUE or FALSE.
Is there away to setup a macro that goes through the list and if false, make the sheet very hidden, if true it should be visible?
View 1 Replies
View Related
Dec 13, 2012
I keep a client list and excel at random will essentially hide entire rows of entries. For example if I go to search a name from the name column that I know I entered nothing will return but if I specifically enter the cell number that is hidden the information will be show, does excel 2011 have an auto-grouping or hiding feature that I'm not aware of? If so, how do I get rid of it? I'm not sure if this makes a difference but all the numbers to the left most of the sheet are blue where in every other sheet they are gray.
View 2 Replies
View Related
May 27, 2014
I have seen a few threads here about this, but not for how I need it to work.
I will have a drop down list with 8 options, (Days of the week Wed to Wed, hence the 8 days lol) There will now be 8 columns, one for each day.
I want to be able to select the day from the list, and then have it hide all the columns except for the day selected. When a different day is picked, un hide that one and hide the rest.
I will attach a book in the reply...
View 14 Replies
View Related
Oct 14, 2008
If you choose entry number 1, Lines 10 to 14 are hidden. If you choose entry number 2, Lines 15 to 29 are hidden and lines 10 to 14 are unhidden. If you choose entry number 3, Lines 20 to 25 are hidden and lines 10 to 29 are unhidden
Until now i have the following:
[Private Sub Worksheet_Change(ByVal Target As Range)
Rows("10:25").Select
Selection.EntireRow.Hidden = False
If Range("A1") = "1" Then
Rows("10:14").Select
Selection.EntireRow.Hidden = True
ElseIf Range("A1") = "2" Then
Rows("15:19").Select
Selection.EntireRow.Hidden = True
ElseIf Range("A1") = "3" Then
Rows("20:24").Select
Selection.EntireRow.Hidden = True
End If
End Sub]
View 2 Replies
View Related
May 24, 2013
I used this method to hide previously selected values in a drop down menu. I thought by including that value multiple times in my "employee" list, I could use it multiple times before hiding it. Is there any way to skirt this? I need to use each value three times before it disappears.
View 2 Replies
View Related
Oct 1, 2006
I have a user form with 4 list boxes that I'd each like to populate with their own lists of cities according to time zone - one list box for each time zone (ET, CT, MT and PT).
I attached a worksheet that has some code from another project I got from another thread on this forum, but it needs to be modified (or whole new code written) for this application, including initializing the control buttons.
The code I'm borrowing has a slight glitch in it, where if you select the last item in the list box to hide, that item will be missing from the list the next time the user form is opened, and you'll then have to manually unhide that column.
View 9 Replies
View Related
Jul 25, 2014
I wanted to hide/remove the already selected options in a dependent drop down list so that the next time i am left with only the remaining options.
View 7 Replies
View Related
May 4, 2007
I currently have a list on a worksheet named "Options". It is a named range entitled "Type" with rows A2:A500. Another worksheet uses "Type" as a drop-down list. Currently there are only five entries in the list - cells A2:A6. Other items will periodically be added to the list. That is the reason for the long range - up to A500.
In the drop-down list, all of the unused cells in the named range show up as blanks. Is there any way to not have the blanks show up in the drop-down list?
Or...
Is there a way to define the named range as a variable range to enable users to add items without redefining the range and have the added items automatically show up in the drop-down list?
View 9 Replies
View Related
Jul 5, 2009
The issue: Theres about 300 rows of data arranged in multiple columns. One column has a dollar amount and another column has a code. Data gets entered into the sheet periodically so every new dollar amount needs to be transferred to another sheet by recognizing the right code.
What I want to do: I want excel to recognize the dollar amount and attribute it to the code in the same row. Then sum and transfer that amount to another sheet and if possible add it to an existing amount the in the correct cell.
The final outcome should be that any user of the document can just copy and paste data into one sheet and the document automatically generates the costs in another sheet. And if possible every dollar amount should be linked to a code so a user can just click on the amount and see where it came from and under what code it falls under.
View 5 Replies
View Related
Feb 14, 2007
I need a formula to create random letters down a column: A-F. Probability of A=3%, B=6%, C=8%, D=11%, E=14%, F=16%. I've got it worked out to where each letter is a header of a column ("A" is in A1, "B" is in B1, etc.). My question is: how do I consolidate that in one column, so that, "A" for example, reads ABFCBACFBFBECEABCEABCEAFBCEFBCAFEBCFAEBFC...? I'm using the random number generator for the 6 columns...thx
View 5 Replies
View Related
Apr 8, 2014
I need to multiply the result of the 3 if statements as shown below in one cell Each of these 3 if statements output an integer and I need to multiply the value of each of these 3 output integers to get the final output integer. syntax of multiplying if statements outputting an integer?
IF(ISNUMBER(SEARCH("Gold",B20)),C11,IF(ISNUMBER(SEARCH("Silver",B20)),B11,
IF(ISNUMBER(SEARCH("Platinum",B20)),D11,IF(ISNUMBER(SEARCH("Customized",B20)),E11))))
IF(ISNUMBER(SEARCH("Gold",B20)),C2,IF(ISNUMBER(SEARCH("Silver",B20)),B2,
IF(ISNUMBER(SEARCH("Platinum",B20)),D2,IF(ISNUMBER(SEARCH("Custom",B20)),E2))))
IF(ISNUMBER(SEARCH("Gold",B20)),C12,IF(ISNUMBER(SEARCH("Silver",B20)),B12,
IF(ISNUMBER(SEARCH("Platinum",B20)))),)),D12,IF(ISNUMBER(SEARCH("Customized",B20)),E12))))
View 4 Replies
View Related
Feb 4, 2014
On one of the reports which i am doing to identify the series of values for a single input data. In the attached spreadsheet there are 2 tables, table 1 has complete data with column a is the lookup value and column b is output value
I have to fill the table 2 in the same manner as shown in the attached template. there could be many different for a single lookup value but i am targeting to look for the 1st 9 matches and last matched value. eg. if i am looking for the value 9 which has 15 different values i want to display the 1st 9 matches (p,q,r,s,t,u,v,w,x and ad - being the 15th match)
Not sure whether this could be possible by using formulas?
View 5 Replies
View Related
Jun 2, 2008
I am looking to create an excel search with two input and three output values on Sheet 1 that will link to a table on Sheet 2. To help illustrate:
Sheet 1:
The two white cells indicate the two input values, and the three blue cells indicate the three desired output values.
Sheet 2:
I want to enter the two input values (Length and Width), press the "Search" button, and have the corresponding output values (DWG#, Item #, and Tooling #) from Sheet 2 appear in the three output cells on Sheet 1.
View 9 Replies
View Related
Sep 11, 2012
I'm trying to take 5 inputs that are all in text format and inputs have fixed options what i need is a macro that will take them and turn them in to the relevant outputs. there is a select amount of combinations and i've been asked to do each combination as a separate macro or sub macro.
What I have so far is;
If C6 = "Panel plate" And D6 = "24 by 21" And E6 = "<1.59" And F6 = "N/A " Then I6 = "120" And j6 = "4" And L6 = "120"
End Sub
A B C D E F G H I J K L
1
2
4
5
6 Panel plate 24 by 21 <1.59 N/A
7
8
9
10
(Doesn't seem to work)...
If those four cell match up then i need I6-L6 to line up with whats required.
If they don't equal that i need it to stay clear.
I also need to add the same macro with different options.
View 7 Replies
View Related
Nov 2, 2012
I have a userform with 5 data fields. The first two are listboxes and the last 3 are textboxes. I have recently added the multiselect option to ONLY THE FIRST listbox (MeetingSpaceBox). I can get the multiselect listbox to output correctly. The problem is, though, that the rest of the data will only fill in the first row and will not be added to any of the subsequent rows. The data from the other 4 fields should remain constant for each selection in the first ListBox or at least until either "ok" or "clear" have been selected on the UserForm.
Do I assign all those valuables to variables and fill them in after the Multiselect listbox data has been added? I assume this will mean a change (or deletion) of the emptyRow code.
See my code below
VB:
Private Sub CalcButton_Click()
If AllDataEntered Then
'define empty row
emptyRow1 = WorksheetFunction.CountA(Range("A:A")) + 1
[Code] .....
View 4 Replies
View Related
Feb 1, 2014
I have a small problem here with my worksheet. In my college i need to make a project about sales in a wooden floor shop.
I have done one part of work but actually i'm stuck at the specifications of my sheets.
What I need to do i added the screenshot with design Screenshot 2014-02-02 01.29.03.png
For my project i need to describe everything in my workbook as you can see in screenshot, i have made some notes but i'm note sure if its really right my teacher don't want or can't explain me well this part of work, i don't know why...
Now the question> i would ask some nice people if they can check my sheets and show me where i need to put inputs and outputs notes in the design sheet. Also in the screenshot I surrounded a part with red line there is a very strange thing no one know what is it 3 teacher telling different things.
View 1 Replies
View Related
Feb 12, 2009
I'm having a mental block with what should be some reasonably simple nested logic. Basically I've got 3 possible inputs in one field and 2 in another, and want to get 6 possible results out the other end. The sheet attached shows what i'm putting in (columns A and B) and what i'd like to get out the other end (C). The formula i need in column C is a bunch of AND's that i somehow keep getting wrong
View 5 Replies
View Related
Feb 12, 2013
I have a question around making a summary table from a model. I will try to explain this as plainly as possible.
I have a model where you can toggle 3 different inputs across two variables. The final output number of the model will vary accordingly.
I would like to create a 3X3 table with the final output for each combination of variables. Is there a way of doing this using formulas or would I need a macro?
View 2 Replies
View Related
Feb 14, 2014
How can excel find the least amount of inputs needed to produce a specified amount of outputs? To boil it down to the basics:
Need=
100 units of x
200 units of y
From a combination of=
item A that yields 25 units of x and 5 units of y
item B that yields 10 units of x and 15 units of y
Objective = minimize the number of items A & B used (i.e. minimum of (item A)*(item B))
View 7 Replies
View Related
Jun 23, 2014
I am working on a complicated macro. The macro is suppose to move certain data set to a specific worksheet if an if statement is "True."
As of now the macro, inserts the row into a new sheet but it is inserted into Row 1. My data set is in Sheet A is up to Column U and I want it to be inserted in Column B on Sheet B. The code below inserts the entire row into Sheet B starting with column a.
[Code] ........
This variation of the code moves the entire data set from Sheet A to Sheet B and inserts it in Column B as wanted, but it does NOT follow the Criteria of "true", it brings the entire data set even those with "False".
[Code] ..........
View 1 Replies
View Related
May 7, 2007
I have a textbox on a userform.
The textbox is populated with a number which is generated by a formula which is
the result of a "Select Case" module.
On some occasions, for instance when dividing 1000 by 3, I get a result with recurring decimals in this case 33.333333333.
Is it possible to make the "result" round to the nearest whole number?
The textbox is named "inDorW".
View 8 Replies
View Related
Dec 21, 2007
My goal is to match two outputs from a database so that the same person's record can be found on one row. At present output 2 does not marry up to output 1 due to staff leaving and new appointments being made. The data in output 1 should remain static. The data in output 2 must be manipulated to match the order of output 1.
Output 1 is a 5 column record containing (in A6:E6)
Forename, Surname, Branch, Roll Number, and a % score
Output 2 is again a 5 column record containing (in G6:K6)
Forename, Surname, Branch, Roll Number, and a % score
Each employee can be identified by a unique Roll Number Code contained in the fourth column of each record.
If an ideal match has been found I'd like the letter Y to be inserted in the L column of each record to signify a positive comparison. All non matches should have the letter N in the L column & these non matches should be placed at the bottom of the dataset.
View 2 Replies
View Related
Jul 10, 2014
Assuming I have three worksheets:
1) input: col A has a series of product code (string)
2) calculation: where I want to automatically copy and paste each product code from "input" worksheet into "calculation" cell B4, and through many steps of calculations, return the outputs in cell B8 and B9 (parameters are numeric)
3) output: how to automatically record B8 and B9 from "calculation" sheet to this sheet Row 5 and 6 from each loop? (format of output doesn't really matters)
View 4 Replies
View Related
Feb 10, 2007
The weather data I receive from a web query pulls data in oddly. For example, some days it pulls the weather for today into cell A1, and the forecasts for the following 3 days are in A4, A9, then A19. The cells in between are blank, and the problem is that it pulls it in randomly otherwise I could just have the cells reference it (ex. some days it pulls it into A2, A8, etc) What I need to do is arrange it, in order, without the blank rows, but I want to do this without deleting the rows.
View 4 Replies
View Related
Jul 14, 2008
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
View 9 Replies
View Related
Jul 19, 2006
I´m writting a macro. It works find until a certain point. When I want to change some outputs of the macro without changing the syntax, it display an error mesage while runing the macro. It says Else without If. Which is quite disturbing because the Else was not creating any problem before. Here is my macro before I changed the conditions (this one work nicely)
Sub Copy_Sheet_Beta()
Set wba = ActiveWorkbook
On Error Resume Next
If IsWorkbookOpened("Projekt.xls", "C:Documents and SettingsfrederikSkrivebordRedd Barna") Then
Workbooks("Projekt.xls").Activate 'In case open, just activate "Projekt"
Else
Workbooks.Open Filename:="C:Documents and SettingsfrederikSkrivebordRedd Barnaprojekt.xls"
End If
Set wb = Workbooks("Projekt.xls")
wb.Activate
If Not SheetExists(wba.ActiveSheet. Range("C1").Value) Then
MsgBox "overall doesn't exist!"
Else........................................
View 2 Replies
View Related
Aug 9, 2006
Attached is a sample of a ledger I am making for my small business. Under the ' books' column, having the output display nothing for a row with no entry was simple, but I can't figure out how to have three different displays for the 'bank' column. The idea of this ledger is that I can continuously balance my books by entering the check mark as transactions are posted. The thing is, I don't want to have the balance display all the way down the sheet when there are no entries in those rows.
View 9 Replies
View Related
Feb 8, 2010
I have created a list for Cells A2:A10 which has the various roles of person. Gave it a name Designation. I have created a drop down list using this list by going to validation. =Designation
I want a function that would check the value entered and search this list would return TRUE or FALSE
View 14 Replies
View Related
