Value Appears Hide The Row It Appears In
Nov 12, 2007
Is there a way to get Excel to look down a column for a particular value and where that value appears hide the row it appears in?
We have a series of reports which all have the same template, however we don't want some customers to be able to see some of the products as these are own-label products for which aren't offered to everyone.
I'd like to add this as a macro to a combo-box so that the user can view a report for their customer and then send a copy to the customer with the knowledge they won't see anything we don't want them to...
View 9 Replies
ADVERTISEMENT
Jan 26, 2014
I would like to hide the content of a cell so it appears blank to user. I edit excel bike tour (route) cue sheets for our bicycle club and some info is needed for formulas in calculating total distance but doesn't need to be seen by the bicycle rider.
View 5 Replies
View Related
Sep 11, 2007
SEE ATTACHED FILE
All,
I have a spreadsheet that lists each employees hours for the last 4 pay periods...each is in it's own row I am trying to find out their average.
I am using the subtotal function to average their hours and that works fine. BUT...my boss doesn't want to see 4 rows for each person. He just wants to see one row for each person and when I collapse the rows, it only shows the employee's ID, not the name (because that's what I told the subtotal function to do...add subtotal after every change in employee ID).
name appears instead of the employee ID? That's useless to him because he hasn't memorized all the Employee's IDs.
View 13 Replies
View Related
Jun 20, 2008
I would like to have a list of names in column A1 Smith, A2 Jones, A3 Johnson etc. Everytime someones name appears in E1-E20 I would like the adjacent column next to that persons name to turn red. Example: I type Smith in E14 -> B1 turns red. If it's not too complicated it would be good if excel could recognize the name in any font,capitols etc.
Lastly if I could have a pop up appear if a persons name appears more than once in column E that would be great. Example type Smith in E1 and then when I type smith in E4 pop appears--Name already used once do you want to continue?
View 9 Replies
View Related
Oct 24, 2007
I have an Excel file that contains macros that I made. Sometimes when I open the file I do not get prompted to enable macros. I DO want to get prompted so I can enable the macros. Any idea why this happens? By the way, my macro security is greyed out so I cannot change it.
View 3 Replies
View Related
Jun 10, 2009
I used macro recorder, and this code was the result. Is there a way to shorten this code up? The more I use macro recorder, the more I'm beginning to realize that the code generated from macro recorder can be very sloppy.
View 8 Replies
View Related
Dec 22, 2011
I'm putting together some data for a pivot table.
In Column Q I have a list of products. In column E these products are listed a number of time (depends on the number of regions which may change). What I am looking to do is have column A show "1" the first time the item group is in the list, "2" the second time...up to 12. Basically I'm looking to have a month (#1-12) appear in column A next to the item. If there are 3 regions then then each item will appear in the column E list 3 times. I'd like each group of 3 items to have a "1" the first time they appear, "2" the second...etc.
Here's an example....
1 - Item#1
1 - Item#1
1 - Item#1
1 - Item#2
1 - Item#2
1 - Item#2
2 - Item#1
2 - Item#1
2 - Item#1
2 - Item#2
2 - Item#2
2 - Item#2
3 - Item#1
3 - Item#1
3 - Item#1
3 - Item#2
3 - Item#2
3 - Item#2
View 2 Replies
View Related
Feb 15, 2007
I am trying to use a MIN() function on a row of numbers; the row is not completely full (some days data was not recorded) and when I have a blank cell, the result in the MIN() frame is 0.0. This is unless of course I have a negative number (which I don't, but for the sake of argument)
This is my current function:
{=MIN(IF(AND(MOD(COLUMN($D10:$DL10),5)=1,CELL("CONTENTS",($D10:$DQ10))0),$D10:$DL10))}
It works like:
Take every 5th column starting at the second.
Try to ignore any cell that contains 0. (This I am trying to get to filter out the empty cells; I have tried ISBLANK(), ISNUMBER() and still get nothing)
Then return the cell to the MIN() function.
That is the long winded thing of it all, basically it can be summarized as this:
{=MIN(D10:DL10}
I have also tried some other code which I was told did the same thing essentially (which it does) but is shorter and nicer:
{=MIN(IF($B$5:$DQ$5="Q*",$B10:$DQ10))}
Which will check for the proper heading on each Column.
These functions work fine with AVERAGE/MAX as far as I can tell, just not with MIN().
As a side note, none of the cells leading up to this function contain "0".
View 9 Replies
View Related
Dec 23, 2008
I have a range ie C2:D400.
The word "ball" appears many times in this range. How do I determine the last row that "ball" appears in?
View 9 Replies
View Related
Sep 16, 2009
there is a strange character that appears in an excel spreadsheet. It looks like a square with a question mark in it.
what this means and am I able to remove them
View 9 Replies
View Related
Dec 9, 2006
I have imported a jpeg graphic and it appears in excel as a black box. What can i do to rectify this?
View 4 Replies
View Related
Dec 23, 2006
I tried using Conditional Formatting, but for some reason it didn't work. I want a cell to be highlighted and/or bolded when particular text is written. It can be in any cell. This can also include blank cells (if possible within a given area such as 20 cells by 20 cells)
View 3 Replies
View Related
Mar 20, 2014
I am in the process of developing a tool to generate Bar charts for a set of metrics data. PFA the screenshots for the bar charts. enough spacing is needed in the bars.
ActiveChart.ChartType = xlColumnClustered
i had also axes.group=2 secondary axis.
View 2 Replies
View Related
Apr 30, 2014
The spreadsheet that i use includes a table that some of the cells are displayed as blanks( include an IFERROR function ) I need to calculate the XIRR for a range of cells in a row that includes some of these "Blank" cells
View 2 Replies
View Related
Jun 9, 2014
I am trying to have my cell show a range of dates, like:
5/25/14 - 5-27/14
I'm trying to use a formula like:
=DATE(2012,5,9) & " - " & DATE(2012,5,9)+2
But when I do this it just appears as:
41038 - 41040
Is there any way to tell excel to format these as dates?
View 2 Replies
View Related
Dec 19, 2013
The attached spreadsheet is a sample of a master sheet I need to maintain. Keep in mind that the spreadsheet has hundreds of names on it and 10 sheets in the workbook. What name appears most in the "person who called" column, then I need to know out of all the people who called, what percentage of calls he made....
I have to do this on each sheet, so if it is possible to have it work for all the sheets.
View 6 Replies
View Related
Feb 5, 2014
I would like to use a formula that will pull values into a sum where the values are in a column and the category is in another column. The criteria in the text cells could be located anywhere in the cell.
Example:
Column A Column B
xyzyszt (Baseline) 7890
wrxyz4sdtr (Strategic) 1234567
zecgjioae (Baseline) Infrastructure 71235
qpielcmjj ae (Strategic) New Capability 278332
View 2 Replies
View Related
Jan 30, 2008
When I hide a column in this spreadsheet, text appears in some of the cells that shouldn't be there. When I highlight and try to delete, it won't delete and it doesn't even show up in the function bar. I was able to get it to delete when I do clear, all from the edit menu but as soon as I try formating the cell, it puts it right back. Same text, same formatting. I can't get rid of it.
View 10 Replies
View Related
May 25, 2008
I am linking several cells in Sheet1 to other sheets throughout the workbook. Sheets2-10 to be exact. The problem is when no data is in a given cell in Sheet1 a '0' shows up on the linked cell in Sheet2-10. The problem is 0 can be a correct response sometimes and if 0 is always to show up someone is going to misunderstand the task.
I just want to stop blank entries from being linked over as 0. I have a ton of cells being linked and don't want to do conditional formatting on each individual cell. Is their any easy way to stop this?
View 10 Replies
View Related
Apr 17, 2009
I have a list of numbers in Column A (labeled "Number"), with a list of dates in Column B (labeled "Date & Time"). Every date in Column B is unique, but some numbers in Column A appear more than once.
I am trying to create a simple formula that will automatically search for the LAST time a unique number appears in Column A (ignoring all previous instances of this number), and copy the date right next to it in Column B to another location on another worksheet.
I tried fussing with several of the built-in functions, but I really don't know what I'm doing, and the built-in help documents are filled with too much mathematical/programmer technical terminology for me to understand. All the tutorials I've looked at seem to think I want to add some numbers together, but I don't want to do any calculations!
View 4 Replies
View Related
Dec 14, 2012
At work I have been tasked with building a summary excel document that details people's names, grades etc. To be accurate, each person has their own positional code (a unique number), which I used in my lookup formula. After weeks of work, I have 1500 odd names down. However, this is 6 short of what I need the number to be. This is the cause of great stress,as this has to be ready on Monday, and I just don't have the time or sanity to sit there copying and pasting a number into find 1500 times to find the 6 numbers I have accidently ticked off as in the document. Therefore, it would be useful if there were some way to tell me how many times a number appears in the document. Each number should obviously appear twice (once in the data sheet, and once in the appropriate table)
View 9 Replies
View Related
Jul 9, 2014
I have worksheets with different employees on separate tabs that are paid based on a compensation system inherent to their position and certain inputs. What I want to do is have a userform box appear whenever a worksheet is copied and allow the user to input the information unique to that employee (inputs are unique things like ID number, email, compensation rate, etc.). I know how to create a userform but how do I get it to automatically appear whenever a worksheet is copied?
View 4 Replies
View Related
Mar 5, 2014
The formula I'm trying to use is
=IFERROR(INDEX(L31:L6621,MODE(MATCH(L31:L6621,L31:L6621,0)+{0,0})),"")
And it works as I need it to returning the value that appears the most from L31 to L6621. My problem is the number of rows will continue to change. So I tried something like this
Sub ARBM()
Dim lr
With ActiveSheet
[Code].....
But I get a compile error with expected end of statement. How else can I write this?
View 4 Replies
View Related
Oct 21, 2008
I have a range of data that is sorted (about 20,000 rows). In about 15,000 of these rows column D will contain a generic text string. How do I select the entire row where the generic text string appears in column D?
View 3 Replies
View Related
Apr 7, 2009
I was wondering if it is possible to delete rows if a value appears only 1 time in a column? To clarify since 123 only appears once in column A I want the entire row 4 deleted.
View 2 Replies
View Related
Jun 26, 2009
when opening Excel, a popup window appears with a question. you can then enter a value to answer the question in the popup window. then you can press enter, another window will popup with another question etc. the values you've entered will be inserted to the excel body appropriately. for example,
popup window with question: "What is your name?" your answer "John Doe"
popup window with question: "What is DOB?" your answer "1/1/2009"
the two values will be inserted as below: this is John Doe. My DOB is 1/1/2009.
View 2 Replies
View Related
Aug 14, 2009
I have a long spreadsheet and want to remove rows of information from it if cells contain a specific word. If the cell in column A contains the word "district" or "borough" anywhere in the string of text and irrespective of the case I need to delete the whole row.
View 4 Replies
View Related
Apr 26, 2006
I have 2 columns with data in them, basically representing a gaussian distribution. Column A has the "X-axis" values and so is uniformly ascending with no duplicates. Column B has the "Y-axis" values and increases up to a maximum and then decreases again (this is data from an instrument and so its not completely smooth but is close). An example is below.
0 4
1 8
2 16
3 27
4 50
5 27
6 16
7 8
8 4
What I would like to do is get the 2 Column A values where the corresponding column B value is half of the max (in the case above, 25 is not available so the closest is 27). I am trying to calculate the difference between these values, so in the example, I would have 5-3. Is there a way to do this?
View 11 Replies
View Related
Nov 26, 2006
I have a problem with LAN function. I have following formula to count how many times appears number 2 in a box: LEN(I5)-LEN(SUBSTITUTE(I5,"2",""))
in I5 I have 1,2,3,12,34,22,21 . Outcome is 5 which is not what I need. What I wanted to do is to get output of how many times appears number 2, not how many times appears expression 2 (it counts also 22, 21, 12, 2) . The output that I need should be 1 since number 2 appears just once in the box.
View 14 Replies
View Related
Nov 5, 2011
Time spent on each page appears in row 18. Need to sum up all of these pagetimes for each site, to get the total time on site. So in the example below,
D19 would reflect the total time spent on GreenElectricalSupply.com sum of cells D18-G18(1:23.0)
and H19 would display the total time spent on Rexel.com (00:45.0)
So, how to do a conditional sum across row 18, until a new site begins (indicated by the appearance of text in the next cell of row 8).
Data
CDEFGHI8
greenelectricsupply.com
rexel.comnextag.com9WW ONLY: Arrived at site via
[Code] .....
Excel 2007
View 4 Replies
View Related
