Is there a way to make "reversed" hidden cells? For an example:
Cell A1 has a basic description of what is needed. Cell A2 is blank, but when highlighted, will display more intricate text in the formula bar. I'm trying to adjust a corporate worksheet that's filled out nightly, but add in "helper" cells, so that if someone new doesn't know what number goes where, they can look at the "blank" helper cell.
Also, if this is possible, will the second cell "overwrite" the first, if the first cell's text overlaps? Basic example:
Cell A1 is 26 wide. It's text is 18 pt. bolded, Times New Roman "Brand National Accounts", which overlaps into cell B1. If any text or other information is entered into cell B1, it shortens the visible text in cell A1 to "Brand National Ac". If the "helper" cell is B1, would it make A1's text the shortened version?
I am trying to format all cells on all sheets (hidden or otherwise) as "Locked" so when the sheets are protected the user can't see the formulas. This macro individually selects every sheet in the book and applys the formatting. Is there a way to modify this code to accomplish the same thing without having it actually select every sheet? The only reason it is an issue is that after running the macro you end up on the last sheet in the book.
I have two sets of columns in this chart, and two lines on a secondary Y axis. But the first set of columns have their colors reversed. How do I fix this?
What I am trying to do is rank my employees based on certain metrics. They are weighted in three different catagories. Talk time is 40%, Quality 50%, Attendance is 10%. I can weight the Talk Time and Quality, as those are basically the higher the score the better the weight. Attendance is the opposite, the lower the number, the higher the weight. Meaning, if employee has 1 absence that would better than someone who is at 4 absences. Is there a way to do this. I am somewhat educated on VBA, I understand if,then,else statements and/or Select/Case statements, so if I needed to do it in VBA I can. I just need direction to get it going.
I am trying to make a function that will only show the value of visible cells (so I can have excel calculate a slope/offset of a filtered table. I made the function below, and it seems to work pretty well.
Option Explicit
Function Visible(x) As Variant Application.Volatile If x.Rows.Hidden Then Visible = "" ElseIf x.Columns.Hidden Then Visible = "" Else: Visible = x.Value End If End Function
However, when I try to use it in an array formula instead of with an individual cell (example, {=SUM(Visible(A1:A10))} ), it only evaluates the hidden property on the first cell. So if the first cell is hidden, all cells will be blank, and if the first cell is not hidden, all values are summed, regardless if some of the later cells are hidden.
I usually use the IF and IFERROR functions with the SUM functionto filter out values I don't want, and I assumed I could do the same with this custom function.
If I have two columns of data, one for mortgage rate and one for loan size, then use a data filter to display a subset of the data, how can I calculate a weighted average on just the hidden cells? Is there any sort of criteria flag that looks for hidden cells in a range? Sort of the inverse of the SUBTOTAL function.
I have cells, or columns hidden when I do not need them, but I have to a data entered in those cells to make my spread sheet work. Usually just 0.
When I do the all clear or " clear all contents" operation at the end of the day, it clears the hidden data/cells too. Is there anything I can do to stop excel from clearing those hidden cells?
I was wondering if it was possible to hide cells using protect sheet? I have a worksheet that contains confidential data and would like it so the cells appeared blank unless you had the password. I know you can hide rows and columns but I need everything on the sheet to be hidden.
I've unlocked it fine, revealed formulas etc, but it doesn't make sense so must be a hidden formula stopping people like me from unlocking the hidden treasures!!!!
At the moment it stops at column V and I can't get it to unhide any further???
So the find method works on visible and hidden rows depending if you use LookIn:=xlValues or xlFormulas; however, it does not work on hidden rows caused by a filter.
I have a range that is filtered, and I want to find a value in a column that may or may not be the filter criteria (and thus hidden). I do not want to show all the data, do the find, then reapply all the filters again. I could loop through the column, but I am looking to see if Excel offers a more efficient way.
The problem is, it averages all cell values (conditional upon the C column's cell being equal to A2 of course), whereas I only want to average the VISIBLE cells that match the condition.
I have tried using a little VBA with:
Function Vis(Rin As Range) As Range 'Returns the subset of Rin that is visible Dim Cell As Range Application.Volatile Set Vis = Nothing For Each Cell In Rin If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then If Vis Is Nothing Then Set Vis = Cell Else Set Vis = Union(Vis, Cell) End If End If Next Cell End Function
and then trying the formula: =AVERAGE(IF(Vis('Data Table'!$C$10:$C$65536)=A2,Vis('Data Table'!$BP$10:$BP$65536))) but it does not work.
im looking for a basic macro for workbook copying, all workbook(all hidden, unhidden, and very hidden) sheets.
i know how to do unhidden sheets of course however the hidden and very hidden are giving me some trouble...i need to also copy over all the macro's, buttons, and drop down box's as well..not just values.
I am working on a file with different users entering information on different sheets (tracking the status of individual projects and activities). Using the macro below (which I found in the forums), the individual data will be combined on a master sheet. My problem is that some of the users will be using autofilters to filter their own data (for example--filtering to show only the "Open" projects). When I do the copy of the individual data, I want ALL of the data, not just the data that is being shown by the individual filter settings.
At first, I just turned the individual filters off before copying the data. The other users have asked if there is a way around this or alternatively, restore the filter values after the copy.
A key point, there are currently 8 user sheets. Each user is allowed to set their own filters and change them at will-e.g. one day, a user may filter on projects due in Q3, another day she may be looking at all projects closed in 2013.
VB: Sheets("Combined").Select ActiveWindow.SelectedSheets.Delete Dim J As Integer On Error Resume Next
I have set my 'lastrow' to the last value in column A. The problem is, this code gets run whenever an autofilter is made. As a result, the 'lastrow' records itself as the last row of visible data. In reality, I need it to track the actual last row of total data.
I have conditionally formatted (Bold Italic) some data (the highest value in each column) and I have a macro that hides rows dependent on one value in that row. However I would like the conditional formatting to apply only to the visible cells, so that if the maximum value is in a row that is hidden the conditional formatting is applied to the highest, non-hidden value.
how to calculate a simple average after removing duplicates. The trick is the average must work after the spreadsheet is filtered. I was able to make it work with a VBA script but the users don't like this since the numbers don't automatically update whenever they apply a different filter.
What I need: average (mean) value of (in this case age in years) based on unique ID#s that doesn't include duplicates (some ID#s are in database several time):
ID# Age Filter Options
11111 27 Option A
11111 27 Option A
[code]....
So if I filter by Option C, the cell with the average should return the average age of only the filtered ID#s but without the duplicates.
Correct age w/no filter: 56.8 Correct age w/option C: 59
What I have tried using: (reason for difference in cells on the equation, I condensed it in the above example so A=C and B=G)
This array works but will return hidden and non-hidden cells b/c of average(if), so it doesn't work with filter:
{=AVERAGE(IF(C3:C13C4:C14, G3:G13,FALSE))}
Also have tried the Subtotal 101 function, also with no luck: Note, a simple subtotal101 fxn will not work b/c even when I filter there will still be a few rows that are duplicate and need to be removed before averaging
I am applying the sum function to a range of cells in a column. I've Auto Filtered my sheet to only include the data I need. Unfortunately, the sum function is adding everything in that column, even data from the 'hidden' cells that I've filtered out. I need to know what condition or parameter to apply to the sum function (or any other function for that matter), that will only add the data shown on my screen. Not the data that is hidden.
I have a column of figures in a file sent to me recently which are dates but for some reason have been reversed eg 20140321. This is 21st march 2014. However I have tried formatting the number as a date (as we would normally see it in UK eg 21/03/14 or similar) and I cannot get Excel to recognise it as a date - I just get a long row of asterisks. How do I get a recognisable date sequence?
I have searched all over and read many solutions for selecting only visible cells and copy pasting them or formatting them by doing Ctrl + G and selecting visible cells only. However the problem is once I select visible cells only, it seems that every time I do Ctrl + C on filtered Range it only selects visible cells. I want a way to toggle this setting in Excel. I'm using Office 2007. For example: if I want to select the entire range, both hidden and visible cells within the selected table array, is there anyway to reverse or toggle the setting that causes Excel to refresh the "visible cells only" setting back to default or all cells?
It would be a much faster way than to remove all filters, select & copy entire range, and then re-apply all filters again.
I have a formula to search a sheet for certain criteria and sum up the total, data is entered into this sheet by week number, where I then filter it for whichever week I need. I following formula works fine, but adds all the cells including hidden ones, how can i get it to ignore hidden cells??
=SUM(IF('FT Line 1-RX'!$C$2:$C$2100='FT costs 1-Rx'!B$1,IF('FT Line 1-RX'!$E$1:$GT$1='FT costs 1-Rx'!$A11,'FT Line 1-RX'!$E$2:$GT$2100)))
There is raw data in the 'FT Line 1-RX' sheet which is summed up in the 'FT costs 1-RX' sheet.
Any way to automatically "rank" cells, ignoring any hidden rows? I don't even really need Excel to sort for me (although I wouldn't be opposed to that), but I'd like the first non-hidden cell in column A to always say "1" and the second one to say "2" and so on.
The back story, if you need it: I have a spreadsheet that tracks sales for 2 separate brands. I've created a macro and linked it to a listbox, so that you can choose which brand you want to view and Excel will hide all rows that aren't pertaining to that brand (I'm absurdly proud of that accomplishment). The only problem is the rankings are hard-coded, so when you hide one brand the rankings no longer make sense.
Every Monday I sort the items by the most recent week's sales and rank them from there. The addition of a second brand is obviously new to my little spreadsheet!
I am having trouble with my VBA code, it hides groups of rows (projects) based on the contents of corresponding cells above. these have either 'yes' or 'no' in them (indicating if the person is active in that project). The Yes/No is formula based from another sheet.
what I am trying to do, is have particular cells, withing the projects rows, locked (as they are also populated by formulas) but have other cells open for editing.
when I try and do this i get an error with my VBA, i am assuming that as cells are locked, the VBA is prohibited form hiding inactive projects/rows. i tried an addition at the bottom but this hasn't worked
Code: Sub Worksheet_Calculate() Dim i As Long, StartRow As Long, EndRow As Long StartRow = 13
I am trying to figure out how to do the coding for a command button in a userform I've created. It has 5 textbox fields. I want the command button when clicked to transfer the data in those 5 fields to 5 specific cells in a hidden sheet. Then I also want that button to launch a word document.
Anyone know how I can go about this or where I can go that explains the specific coding?
I've tried searching with mixed confusing results & read through http://www.contextures.com/xlUserForm01.html#Top & http://www.theofficeexperts.com/down...ExcelDownloads examples.
