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.
View 9 RepliesI have got a multisheets database where both rows and columns may be
hidden according to a given criteria. Only blanks rows and columns
should be hidden.
Now I need a formula to check if there is no "valuable" data hidden.
Checking test might be:
SUBTOTAL(9,reference) = SUBTOTAL(109,reference)
I have a linear count from 1 to 160 (J3:FM3) and I hide columns manually over time depending on a certain criteria. However, I would like to count how many columns I have left. I believe you need to use the subtotal function, but I do not understand how to use it.
View 14 Replies View RelatedI posted a thread a while back about how to ignore hidden rows from autofilter when using formulas. http://www.excelforum.com/excel-work...ml#post2039071
The solution worked perfectly. But I have since had to use this on another sheet which is laid out a bit differently, I have tried everything to try and modify the formula to work on the new sheet but so far I have failed miserably!! I need to do the exact same thing on this sheet - filter the sheet by week and search the data for certain criteria ('Line' and 'Fail Reason') and sum up the total quantity, while ignoring autofilters hidden rows. Here is an example of the sheet, along with an attempt of mine to modify the formula, which I think I have got TOTALLY wrong!
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.
View 7 Replies View Relatedway to do this but i have a sheet that is into 5 - 6 thous rows, in one of the columns (names) i sort it by names and then order it by subtotal for certain values.
What i need to know, is there anyway i can take just the subtotal values out and put onto another spreadsheet without copying and pasting it all as there are lots of subtotals and this would help alot as the other info is not nec. just the subtotal'd info. either that or is there anyway i can highlight the subtotal'd row info in yellow/bold text anything like that that would make it stand out without having to do it manually?
I’m trying to get my sheet so that at each change in month it creates a sum of the value but I want to sum to show up in the subtotal value column.....
View 10 Replies View RelatedI just did a Group Subtotal. How do I fill all the blank cells in a column with the information in the cell above it without going to every individual cell?
View 8 Replies View RelatedI have a workbook with two tabs, Summary & January. The january tab has total expenses for the month of january that are subtotaled by last name. i.e. (baker total, decker total..). I would like to be able to use a formula on the summary tab to pull the values of each of the totals for each person from the january tab to the summary tab.
I tried on the summary sheet doing =jan and selecting the first persons name and then going back to the summary tab and dragging that formula down but it pulls all of the other data within the subtotal on the jan tab and I just want each persons total.
I am copying a range of rows (which include some hidden rows). When I paste them I see all the rows. Is there a way to retain the hidden attribute?
View 2 Replies View RelatedI 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.
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.
View 2 Replies View RelatedI'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???
Used the edit, go to, etc won't go.
Deleted all code in vb I can see or find
Tried clicking on all sheet and unhide
Nothing!?
I'm trying to copy a range with hidden rows,
but if i do like this:
range("A1:A10").copy Destination:=Range("A15")
but, as i said, some rows are hidden and i want copy them too.
Unfortunately Excel copy only visible...
How can i copy an paste hidden cells too?
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.
I have a large data table that has autofilters on the headings.
I also have a "Data Summary" Sheet in which I would like to get a conditional average.
Here is my *working* array formula:
=AVERAGE(IF('Data Table'!$C$10:$C$65536=A2,'Data Table'!$BP$10:$BP$65536))
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
[Code] .....
How to get the LARGE function to ignore hidden cells? Is it possible?
I have a filter in a data set and am using a macro to pull in the top 5 largest values.
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.
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
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.
View 9 Replies View Relatedhow 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
=SUBTOTAL(101,(IF(C3:C13C4:C14, G3:G13,FALSE)))
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.
View 2 Replies View RelatedHow can I block the hidden cells, so that when I want to copy/paste into another workbook, pasted data has to be all, except hidden&locked cell?
View 4 Replies View RelatedI 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
[Code]....
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.