Displaying Multiple Values Without Using Macros Or ROWS Function
Mar 13, 2009
is to display a set of data based on filtered information.
My Data base:
Company Department Name
A X John
A Y Joe
A X Jane
B Y Bob
C Z Kate
A X Kerri
Based on user selection of Company and Department, I want to be able to display the relavent names.
If user chose Company A, and Department X, I want to be able to display
John
Jane
Kerri
I've used the ROWS, Index, Small combination that works perfectly (Please see sample below). However, since the this software doesn't support the ROWS function, and doesn't support Macros.
View 7 Replies
ADVERTISEMENT
Sep 10, 2012
In my userform I have a list of check box's that can be selected. Currently if more than one is selected, they will appear in the spread sheet in the same line one after another with a space between them. How do I make it so they either appear with a comma appearing after each, so the next value appears on the next line below or most perferably the cell turning to a drop down list with the values?
Current code
VB:
If CheckBox1.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox1.Caption
If CheckBox2.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox2.Caption
If CheckBox3.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox3.Caption
If CheckBox4.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox4.Caption
If CheckBox5.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox5.Caption
If CheckBox6.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox6.Caption
Spreadsheet current cell appearence if all 6 are selected ' Chinnook EH101 Lynx Puma Sea King Fixed Wing'
Required appearence:
Chinnook
EH101
Lynx
Puma
Sea King
Fixed Wing
View 1 Replies
View Related
Mar 30, 2009
I have some values in the column and i want those values to be displayed in rows now. Example:
In the Excel sheet the data is in the form,
Column1 Column2 Column 3
TASK_TYPE NE_TYPE ADC_PARAM_ORL
I want in the format,
Column1
TASK_TYPE
NE_TYPE
ADC_PARAM_ORL
View 2 Replies
View Related
May 11, 2006
I am copying web data into Excel and need a creative way to run a couple formatting macros on the data. I’m looking for the best way to initiate the macros. I cannot use command buttons because they’ll get deleting as a result of the line “DrawingObjects.Delete”.
I’d like to use a Useform with a couple buttons but am not sure how to have it automatically display when needed and hidden when not needed. I also prefer not using toolbars button unless they will only be displayed in that workbook and not any others.
View 9 Replies
View Related
May 19, 2009
This report spreadsheet is exported from SAP. Since SAP has a limit number of displaying the columns, there are 40 headings (= 40 columns) in the report has been broken down to 4 rows of headings.
How to use macros to move 3 those extra rows to one row and the number still matches each column? I also attatch the file for reference.
View 9 Replies
View Related
Apr 21, 2014
I'm new to Macros and below is my requirement.I need to split my data into multiple rows based on count and the first row should have the value but the other rows should have a value as zero.
Input
Count Value
1 400
2 101
3 300
4 450
Output
Count Value
1 400
2 101
20
3 300
3 0
3 0
4 450
4 0
4 0
4 0
View 4 Replies
View Related
Aug 21, 2013
I am looking for function which allows me to merge and sum values in rows with same ID.
Example:
Sheet1
Sheet2
ID
Value
ID
Value
1.1
1
1.1
4
[Code] ........
Ordinarily I would use a Pivot Table and GetPivotData function, which can easily do this calculation, but in this case file I work with is shared, so Pivot Tables do not work. Unfortunately I am not skilled enough to type my own VBA function. New rows are added to the data source (sheet1) every day...
View 4 Replies
View Related
Jan 23, 2010
I have been trying to get this formula to work, and it appears it only works when it wants to and I cannot figure out why. What I'm trying to do: I have a spreadsheet with several columns of data, but I want the formula to read only two entire columns for a specific criteria for each column, then display the most recent date based off the criteria. I do not need to total up the criteria, just display the last date. This is what I have so far: =IF((AND(Sheet2!A:A=B2,Sheet2!B:B="Game: Counter Strike - Source")),MAX(Sheet2!C:C)).
I have this in a table, header as "last date" which is (C2), the B2 in my table would be where criteria would be typed in. So basically, my table should pull the last date from based off the criteria from raw data in sheet2, that is entered in to B2 on sheet1 and is "Game: Counter Strike - Source".
View 5 Replies
View Related
Mar 26, 2014
I have some code which i would like to search a range of cells to see if a value is already used in this range. if it finds the value it will return a message box saying 'value found' and exit the sub, but when it doesn't find the value it will run extra code using "else" function. The code does what i want it to do, but it still returns the msgbox even if the the code in the "else" section runs.
View 2 Replies
View Related
Aug 10, 2009
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.
I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.
I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.
View 2 Replies
View Related
Jan 18, 2014
I am having trouble with some formulas displaying the results of the logical test as words.. For example.
=IF(E4>F4,100, IF(F4>E4, 200))
That works perfectly but I want "100" to be "Valid" and "200" to be "Invalid"
However...
=IF(E4>F4,Valid, IF(F4>E4, Invalid))
only results in the #NAME? error for me?
View 3 Replies
View Related
Mar 4, 2013
if there is a way to split multiple values in a cell to multiple rows. example check the attached sheet, macro/function with in excel etc.
View 2 Replies
View Related
May 16, 2014
I am building an attendance spreadsheet and trying to copy and paste all of the names from Sheet 1 to Sheet 6 for a different purpose. Thing is I need the names to match so I'm not having to copy and paste every time my data changes.
My problem was that when I insert a row into sheet 1, sheet 6 accommodates and then I'm missing data and have to insert a new line manually.
At first I tried using absolutes ($) to fix the problem, but that's a different ballgame.
I've discovered INDIRECT and so now use this formula: =INDIRECT("'DIRECTORY 2014'!B5"). It works.
But I have almost 300 entries. Is there a way to expedite the process without having to change each entry? I don't want to have to retype the function on every cell.
View 9 Replies
View Related
Dec 25, 2013
I am using the VLOOKUP function to get the exact corresponding value in one particular column. But I need to have two different value in two different columns. Here is an example.
I have attached the excel sheet as well.
Actually I do send the data to my different colleagues and they do send me back with the responses in two different column of D and E corresponding to the "ID" specified in column "B". Which I do need to MAP individually. But I want Excel to map the value of that "ID" (column B) to the corresponding responses in Column D and E with formula.
VLOOKUP generally gives corresponding value to either column D or E but i want both at a time to save time.
UPLOAD_DATA.xlsx‎
View 5 Replies
View Related
Oct 14, 2009
I've never quite mastered the idea of passing arguments between subs and functions. I have a project right now where I am writing this same bit of code over and over, for different parts of the macro:
View 4 Replies
View Related
Sep 14, 2008
I would like to look at an array and find the largest number. Once that is found, return the text value that is in column B from that row. For example:
Column....A.......B.........C.....D
....................Apple......5.....1
...................Banana.....2.....3
...................Orange.....2.....7
In this example, I would be looking at the array C1:D3. Because the highest value is 7, I want the text "Orange" to be displayed in a new cell such as A5.
To find the largest number, I am using =Large(C1:D3,1). How do I display its' corresponding text value from column B in a new cell?
View 9 Replies
View Related
Apr 14, 2014
I have multiple rows of data per each column. I'm trying to count rows that match a value that ISN'T any of the expected values in column A as well as a certain value in column G. This is in case future data is introduced that doesn't match the existing codes.
I'm trying to count all rows that DON'T have a value in column A beginning with PUA or P9V, so far I am trying this:
=COUNTIFS(A:A,"<>P9V*""<>PUA*",G:G,"P")
But it only seems to work on the "<>P9V" part. When I put P's in the column G of which the column A matches PUA*, it still counts the cell. I've tried a couple of AND variations but none are working.
View 2 Replies
View Related
Oct 11, 2011
I'm currently working on a test matrix that has high level and mid level test cases. The High level results cell is tracking all mid level test cases below it. If all mid level test cases are displayed as "Pass" then the High level test cases displays "PASS". If any of the mid level test cases is not displayed as "PASS" then the High level test case result is displayed as "FAIL" However, "PASS" and "FAIL" do not accurately represent the status of the test cases in the instance of them not being filled out. Mid Level test cases start off in the "Select Result" state which basically says it that test has not been performed. I would like to have a third value displayed as "NOT COMPLETED" or "IN PROGRESS". Below is the current formula that I am using.
=IF(AND(C50="Pass",C51="Pass", C52="Pass", C53="Pass", C54="Pass", C55="Pass", C56="Pass"),"PASS","FAIL")
On thing to keep in mind that may or may not make a difference is that one of the High level test cases has 30 mid level test cases and from my understanding, IF / AND function only allows up to 30 logic inputs (not sure if that is the correct terminology or correct at all).
View 8 Replies
View Related
Dec 18, 2013
I am working with the attached sheet and it is made up of groups of rows that alternate with a 6 digit NAICS code (industry code) then an aggregation of those codes into a more general 3 digit NAICS. I need to have the row with the 3 digit NAICS code calculate a number that multiplies a column called "% of industry" by several columns of of numbers and then sums the result into a cell in that 3 digit NAICS row. Different groupings have different numbers of rows. I would like to know if there is a 1 cell formula that can achieve this and be easily copied down the sheet to the other 3 digit NAICS rows.
You can see in row 37 this formula being executed for a simple 2 row aggregation. But that would take forever to replicate down the sheet, especially for the larger aggregations.
An example for row 32 that achieves the desired result and could be easily copied down the sheet.
3 digit Table.xlsx
View 5 Replies
View Related
Oct 4, 2012
At a high level, I have several thousand rows of data. For the sake of simplicity, assume there are two columns: 1) Name; and 2)A comment (optional, could be blank). See below for example..
Sheet 1
Row 1: Mike │ "Great to work with"
Row 2: Mike │ "Bad manager"
Row 3: Tom │ "Great guy"
Row 4: Mike │ (blank)
Row 5: John │ "Cool"
Row 6: Mike │ "Best boss"
On a separate sheet, I want to be able to somehow use a lookup function to combine all the comments for each name in a nicely formatted package (notice how I skipped the blank space so it wouldn't take up a line.
Sheet 2
="Comments for "&[ref to cell containing "Mike" in another sheet]
- Great to work with
- Bad manager
- Best boss
View 6 Replies
View Related
Aug 21, 2014
I am trying to replicate a payslip from a list of data on a worksheet.
The list of data contains the employee name, location they worked, and number of hours.
Each employee will work at multiple locations throughout the month, perhaps 10 or so.
The payslip must contain each location worked along with the relevant data, in a list so to speak.
What function can I use to pull this in? Of course if it were one location I would use vlookups to pull in data. As this only returns the top match I would then need a different formula to pull in the second location in the cell underneath?
View 6 Replies
View Related
May 23, 2007
I have attached a sample spreadsheet which describes my problem.
In C2 and C3 under the first business coalitions column, I have written in text what I would like to be displayed automatically with a formula. The trouble is that, as you can see, Abbott Laboratories is a member of 3 business coalitions. Therefore, a simple vlookup doesn't work because that would only return 1 result.
View 5 Replies
View Related
Jun 4, 2014
I have a data set where the row headings repeat a lot. I have 5 headers repeated probably 30-45 times each. Eg. Truck, Car, Van, Tank, House, Car
At the moment I am using the formula...
=INDEX($B$2:$M$45,MATCH($O$2,$A$2:$A$45,0),MATCH(O4,$B$1:$M$1,0))
which will only return the result of the first occurrence of the cell it is looking for (for eg. I am looking for the values in the cells in columns labelled 'Car', is it possible for it to look past the first occurrence in the data set and find all the values?
View 12 Replies
View Related
Feb 26, 2013
I have a spreadsheet with 27 Columns and 439 rows of data. I need to copy each row of data that has a certain criteria and paste the same data 141 times below it and then manipulate the data. In the same spreadsheet I need to copy a row of data that has other criteria in it and past it 30 times below it, and then manipulate the data.
I have been using the copy and insert copied cells function, but I have to scroll down 141 or 30 rows each time to ensure I add in the correct amount of rows. Is there a more productive way to do this? I have about 10 workbooks with approximately 47 tabs/worksheets each that I will need to update in a similar fashion.
View 4 Replies
View Related
May 31, 2012
I have two columns in my spreadsheet, and both are dates. Using a calculation (one date minus the other) I get the duration (in days) between the two.
I also apply color coding to say if the duration is > X, then color code as red, etc.
All the color coding is working fine for values that are either > or < 0, but anything where the two dates are the same (date 1 - date 2 = 0), the value shows as blank.
When I click on the cell, the value "0" is actually IN the cell, but it's now showing, as if the font was colored white or something, but it hasn't.
I don't have anything in the code telling the worksheet not to display "0" values, so I don't know where to go...
View 5 Replies
View Related
Aug 25, 2006
I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?
View 2 Replies
View Related
Apr 25, 2014
I am trying to write a macro in VBA excel 2010 that compares 2 sheets.
The macro should be something along the lines of if column 7 on sheet 1 = column 1 on sheet 2
AND
on that same row if column 6 on sheet 1 = column 3 on sheet 2
highlight green
** also on sheet1 there can be the same batch ID so if it is the same batch ID it needs to calculate the sum and look at that amount...
Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID
475-T
03/03/2014
Connect
$562.95
$19.95
$543.00
6G000001450835
[Code] .......
Results >
Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID
475-T
03/03/2014
Connect
$562.95
$19.95
$543.00
6G000001450835
[Code] ..........
View 9 Replies
View Related
Feb 4, 2008
I'm trying to make my spreadsheet display over 100% while calculating a long column of entries. Each entry is showing percentage cost per hour of a benchmark of $65.00 per hour. When an entry for example is $51.10 the percentage displays 0.79%, but if the entry is for example $73.89 then the displayed value is 1.14%.
How do I make this display the percentage over the benchmark of 65 as 110, 115 or whatever it calculates out?
View 9 Replies
View Related
Feb 4, 2009
Need help with this file, i did most of the code but cant figure out some bugs i am having.
CTRL-SHIFT-F runs the macro,
the problem i am running into is ,
1), i have the macro looking up the legend in row 3 in the factory install tab, and having it find this legend in the T1 Verbiage file. Once found i want all of the data in the column of that legend pasted in row 12.
I currectly have the macro pasting the legend verbiage according to the legend in row 3 correctly but it leaves out some rows. Can someone look at the code and find out why its leaving out some rows of verbiage?
Next i have certain requirements of where Capital E's should go, where routine maintanence should go ect. I have that at the end of the code and is correct.
The problem here is with this part. ( starts at row 12, counds all of n which is the verbiage rows, then adds 8. So in other words this formula needs to be 8 rows after the verbiage ( which is dynamic depending on the legend ) ....
View 7 Replies
View Related
May 19, 2009
I am trying to find a way to copy and paste multiple non concurrent rows of data from one spreadsheet to another.
Ex: I have a large worksheet with approx 20,000 rows of data. I need to copy
and paste every 100th row to a new worksheet.
I think this might be possible by setting up a formula and linking worksheets,
but I'm not exactly sure how to do it.
View 12 Replies
View Related
