Formula only for visible Cells

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Misc
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - date sort I have a column with a short date formatted: 1/1/08, 10/20/07, showing this way. Question: It does not sort correctly, I am finding some 12/02/07 example mixed in with 1/20/08 and as I look down through the worksheet there are several 07 mixed in with the 08, how do I correct this? Thanks, Tag: Formula only for visible Cells Tag: 802678
  - 2
    - Find account, move them to different sheet MACRO I have a workbook with two sheets. Sheet one has customer information with account numbers in column A. I need a macro that will allow me to search for an account number. When it finds the account number, it will cut the entire row and paste it into the next empty row in sheet 2 in the workbook. All methods I have tried have failed so any help is appreciated. I am using Excel 2007. Thanks in advance. Tag: Formula only for visible Cells Tag: 802674
  - 3
    - Regular expression search/replace in Excel Without delving into VBA (I have limited experience, and now is not the time to learn), and without installing augmentation software, is it possible to search for all text matching a search pattern, then prepend each occurance with given sequence of characters? If not, is it possible to simply prepend a given sequence of characters the string in each cell of a selected region? Thanks. Tag: Formula only for visible Cells Tag: 802672
  - 4
    - ?can I wrap displayed formulas? I have multiple large formulas in a spreadsheet, and need to print a version of the spreadsheet showing the formulas for verification purposes. The "Format" "Cell" "Alignment" "Wrap text" doesn't seem to work on formulas. Is there an alternate way to get the formulas to wrap within the cells? the current alternative is to widen the columns extremely wide, making the spreadsheet unwieldy to verify, or changing the font to a point where it is difficult to read. Not a desirable option. Tag: Formula only for visible Cells Tag: 802669
  - 5
    - find a value from 1 column in another column on another sheet I need to see if the value of cell in a column on sheet1 is also in a column on sheet2, if it is I want it to return a value of TRUE. Is there a function I can use for this? Tag: Formula only for visible Cells Tag: 802668
  - 6
    - random merged cells Does anyone know why excel is randomly merging cells across multiple worksheets. I haven't found any viruses, reinstalled excel 2007, but issue still occuring. any insights would greatly be appreciated. Tag: Formula only for visible Cells Tag: 802667
  - 7
    - How to Average elapsed time values? I have a column with elapsed time values like 01:45, 02:25, 01:58, etc. I can't seem to get the average to work out right using other posts, so can anyone help me out! I have 667 values in colum M. All the values have a 0 as first number. If I remove the 0 from the time, I have no problem, but that would take forever, at least with my skill level! Tag: Formula only for visible Cells Tag: 802662
  - 8
    - How to return formatting to cells rather than value for "IF" cmnd Can formatting of cells be returned after an "IF" logic formula rather than a value? I'd like to have cells be colored when a specified cell has a specific number entered in it when the "IF" statement is true and not colored when the "IF" statement is false. Tag: Formula only for visible Cells Tag: 802659
  - 9
    - Hide/Unhide Using the function Group and Ungroup, I wonder if it is possible to ungroup a set of rows without displaying/unhiding any hidden lines included within the grouped area/block? The problem is that every time I ungroup the rows any hidden lines (not grouped ones) are displayed/unhidden. Thank you. Tag: Formula only for visible Cells Tag: 802658
  - 10
    - Excel graphics fill options I need to use the "old style" fill options with Excel graphics. In 2007, I can't find anything but textures. I need diagonal lines, checkerboard, etc. for overlaying on maps. Surely they didn't just do away with all of that. Suggestions, please. Tag: Formula only for visible Cells Tag: 802657
  - 11
    - AutoCalc Excel 2007 - I am trying to turn off the AutoCalc function. I am also turning it off (so it manually calculates when I press F9) in the default. However, when I close and re-open the spreadsheet it is back on and starts calculating right away and when I am working on it. Is there a way to turn this off so it never calculates unless I press F9? Tag: Formula only for visible Cells Tag: 802656
  - 12
    - How do I take ownership of a document I i created a document in EXCEL and would like to make sure people could view or add data to it but without being able to change who created it originally. Is there a way to mark for a word or excel doc as mine. Thank you Tag: Formula only for visible Cells Tag: 802652
  - 13
    - Copy (Formulas and Formatting ONLY) no values to other rows I have a workbook (tracker) that adds new records from a workbook called (data). When I add a new record from data to tracker, I want to copy the formulas and formatting from the row above the empty row in tracker and paste them into the empty row. Then when the data is copied over it uses the formulas and formatting as above. I do not want to have these formulas copied into every row on tracker until there is a need for them. Every time I write a vb script to copy and special paste formula only to the new row it copies cells that just have values as well. Can anyone show me code that will copy a rows formatting and formulas only to a row below it? Thanks! Tag: Formula only for visible Cells Tag: 802651
  - 14
    - Tool Tips for UDFs? Is there a way (involving C++) to add tool tips to UDF's? I have several formulas defined, and want to help users when they try to complete the formula in the cell. I'm looking to mimic the functionality provided for existing Excel formulas. I am using both 2003 and 2007. thanks, Harry Tag: Formula only for visible Cells Tag: 802649
  - 15
    - how do i stop my arrow keys from scrolling? My mouse scroll is zooming in and out and my arrow keys are scrolling. How do I reset the keys so that my arrow keys will move from cell to cell in Excel and the mouse scroll will not zoom in and out but scroll? Tag: Formula only for visible Cells Tag: 802638
  - 16
    - Publishing a spreadsheet online I have a spreadsheet that's a combination of data input sheets and graphs (which are created based on the data). I need to send this spreadsheet out to a large number of people, and I'm wondering whether there's a way to publish the spreadsheet online, where people can input data and see the resulting graphs, but not be able to see or change the formulae in the spreadsheet. Essentially, I'm looking for a way to publish a protected spreadsheet online. Tag: Formula only for visible Cells Tag: 802637
  - 17
    - How do I add a label to a macro on the quick access toolbar? I've recorded a macros, assigned a symbol to it, and placed it on the Quick Access Toolbar. When I hover over the symbol, it displays the macro name. Is there any way to have the name label display along with the symbol, or instead of the symbol? Tag: Formula only for visible Cells Tag: 802635
  - 18
    - Forgot Password Hi, I have an excel file with password to open and modify but now i forgot the password to open the specific file.....is there any way to open that file. Please help Thanks in advance Ravi Kumar Tag: Formula only for visible Cells Tag: 802630
  - 19
    - Hide/Unhide boxes I know to hide/unhide columns but I recently saw something cool that I can't recreate. There are small boxes in the rows above the columns that hold the hidden columns. When you click the box it unhides, click again it hides. Much easier for users to naviagate this way. Thanks! Tag: Formula only for visible Cells Tag: 802624
  - 20
    - numbers overlapping in line graph I have a line chart were the numbers are overlapping. Any suggestions? Tag: Formula only for visible Cells Tag: 802623
  - 21
    - Count matches within two columns On sheet 2....I have a lookup name in cell A1, and a lookup time (hour) in B1. On sheet 1, I have a list of names in A:A, and a list of (hours) in C:C. How do I count the number of matches between the two? AMY 12 AMY 15 BRUCE 12 BRUCE 22 AMY 12 AMY 12 AMY 12 BRUCE 33 AMY 23 So the formula would look up how many AMY are in the same row as 12 which equals 4. Thanx Tag: Formula only for visible Cells Tag: 802622
  - 22
    - Link a .DWG file? I have excel 2003. Im using charts to plot part geometry and dimension lines, based on user inputs, much like you would see in a CAD drawing (.DWG). I want to insert a link to a .DWG made in A9CAD (free version) so that I can see the drawing in excel. The .DWG would reference the user inputs in excel to draw the part, then the same excel file would reference the drawing so I can cut out dozens of huge value-tables used to plot the geometry currently. I havent explored the capabilities of A9CAD yet to see if it can reference an excel file, or a .txt output file...but I thought I would ask if this is possible first. Anyone know if this is possible??? Thanks! Tag: Formula only for visible Cells Tag: 802621
  - 23
    - Look Up Help Needed Again Hi, I had a similar question yesterday, related to a table of data that gets updated daily. The top row is this month's date, and the row below is the bank balance for that particular day. Then, the final column (to the right) is for totals. See below: Daily Total Dates| 7/1 7/2 7/3 etc. Value| $1 $4 $5 etc. ? Can someone help me create a formula that will add up the most recent seven days worth of bank balances? For example, after I input today's (7/24) balance I will want the formula to automatically sum July 18th - July 24th's balances. Thanks in advance for your help! Tag: Formula only for visible Cells Tag: 802619
  - 24
    - Constants in Excel Hi, I am using MOSS and Office 2007. I am collecting data via the SharePoint list and then exporting the list to Excell on my desktop to manipulate and aggregate the data. I have removed the table and broken the link with the SharePoint connection, unlocked the cells. When I attempt to add formulas to the excel spreadsheet, the formulas will work - when I evaluate the formula I get "The cell currently being evaluated contains a constant". I have found the cells that contain constants but need to remove the constants and can't figure out how to do that. Can you help? This is very urgent for my business. Tag: Formula only for visible Cells Tag: 802609
  - 25
    - Combine Text Cells Is there a formula to combine the text in multiple cells into one cell? I.E. if Cell A1 says RNA and Cell B1 says Te4 and Cell C1 says Late, is there a way to make Cell D1 combine them and be "RNA Te4 Late"? Thanks! Tag: Formula only for visible Cells Tag: 802607
- Next
  - 1
    - conditional formula with date I have a delivery date column with mmm-yy format. How can I write a conditional formula so that the date turns yellow when it's due in 30 days and turns red when it's over due? Please help! Tag: Formula only for visible Cells Tag: 802599
  - 2
    - Chart title that changes dynamically? Hello, Folks! I have created that changes on the fly based on the date range a user wants to chart. Is it possible to include a title field at the top of the chart that imports the Start and End date so we don't have to type it by hand whenever we view or print different ranges? Something like Tacoma Bulk &[=Min(A1,B1)] to &[=Max(A1,B1)] Thanks for your time. Arlen Tag: Formula only for visible Cells Tag: 802590
  - 3
    - protect formulas in locked cells from changing if data is moved I have locked and protected a worksheet leaving only "Select unlocked cells" checked. The problem is that if a user "moves" an entry in an unlocked cell, either by ctrl X or drag and drop the cell references in the locked formulae that referred to the original position change to the new position. Is there any way of preventing this without turning off the drag and drop functionality? Tag: Formula only for visible Cells Tag: 802586
  - 4
    - how do i make a cell turn red if value is less than 0? i have a list of numbers on my worksheets, inclusing some zeros. i would a formula that means whenenver a cell just has 0 in it it will show as red. Is this possible? Thanks Tag: Formula only for visible Cells Tag: 802579
  - 5
    - code does not work anymore Hello Excel 2002 - I have a spreadsheet that I rename every month with the currents month's name,( e.g. Sales for July 2008 ) and has a worksheet for each work day of the month and a summary tab at the end. I've been using the following code but the code does not work anymore . above the code first box is worksheet second box is Selection change Private Sub Worksheet_SelectionChange(ByVal Target As Range) With ActiveSheet newname = Application.WorksheetFunction.Text(Range("J1"), "mm-dd-yy") ActiveSheet.Name = newname End With End Sub thanks in advance Tag: Formula only for visible Cells Tag: 802578
  - 6
    - Is there a macro to... Is there a macro that would make it possible to lookup a certain name in a column and anytime it finds that name it will copy a range of cells and paste it into another worksheet? I.E. anytime the name Jim Smith is found in the range A5:A200 it returns a range of cells next to it...if Jim Smith was found in A5, it would copy cells C5:FN9 and paste them in a specified location on another sheet Right now I have it copying all colmns from the master sheet and if the name is not assigned to that project it will return a 0, then I have a macro to hide all rows that equal 0. Is there a better way to do this? Any hints on the best way to do this? Thanks! Tag: Formula only for visible Cells Tag: 802576
  - 7
    - How to combine information from multiple rows into one Hi, I have a spreadsheet with multiple (duplicate) rows which I would like to combine into one. The spreadsheet lists name/address of a customer and also anyone else who may be living with them in the same property. I would like to mail merge the file and send a letter to each customer listing who we think lives in the property. Here is an example: Current file: Mr Peter Smith, 1 Nowhere Street, Amy Smith Mr Peter Smith, 1 Nowhere Street, Tracey Smith Mr Peter Smith, 1 Nowhere Street, Tim Smith The new file should show 1 row as: Mr Peter Smith, 1 Nowhere Street, Amy Smith, Tracey Smith, Tim Smith Tag: Formula only for visible Cells Tag: 802572
  - 8
    - Reassign built-in shortcut keys in excel 2007 Hi there, how can I reassign (rename) built-in shortcut keys in excel 2007? For example I would like to reassign the CTRL+F (currently assigned to "bold" in the Danish version) to CTRL+B (currently assigned to "Find"). Cheers Tag: Formula only for visible Cells Tag: 802570
  - 9
    - dates and conditional formatting I have a spreadsheet (excel 2003) that has the month in column A2 and the days in column b1, c1 etc. Please see the example below. I would like to use conditional formatting to identify ,by fill color, weekends in the appropriate cells, for example Jan 5th and 6th. I am very perplexed. Any help would be much appreciated. Thanks. a b c d e f g h i j etc 1 2 3 4 5 6 7 8 9 Jan Feb JH Tag: Formula only for visible Cells Tag: 802569
  - 10
    - Excel VBA Coding for Printing Hello Could someone help on this? I like to put an object on an Excel sheet. When I click it it should print a selected range. I tried a macro but I could go up to the pre view level only. Thank You M Varnendra Tag: Formula only for visible Cells Tag: 802563
  - 11
    - Graph Data not displaying Hi, I have a range of 4 values in a spreadsheet and when I select them for a bar graph I am getting the graph with no bar displayed. The cell references are correct in the series value tab. I have tried re-entering the data but no joy. Anyone any ideas? All help greatfully accepted. Davie Tag: Formula only for visible Cells Tag: 802559
  - 12
    - print/preview problem before saving an edited excel worksheet Hi, I am the Office 2000 user. I encounter problem with printing an excel worksheet each I edit the contents without saving. Here is the comment message appeared each time I press the print / preview button: "To print, it is necessary to overwrite the content. Do you want to overwrite?" I could only print or preview if I click yes. Please advise how to fix this. Thanks. Tag: Formula only for visible Cells Tag: 802554
  - 13
    - Seperating numbers from text Hi, I am working on an excel file which has a column that contains product names + serial numbers. Dont know how but someone combined that data. they used to be in different columns but now they are in same column and have no spaces between them. cells have names like this, productname1345 is there any easy way to scan through the coulmn and at least seperate the data like productname 1345 after this step i think i can handle it:) thanx for help. Tag: Formula only for visible Cells Tag: 802552
  - 14
    - Correlations, Group By Type? I have data for several different types of land Variable1 Variable2 Variable3 TypeOfLand 30 250 300 Desert 40 670 200 Forest 10 30 400 Forest 20 500 800 Arctic 20 180 270 Desert .... I'd like to know if there's a way using PivotTables or any other function to product the following automatically CorrelationBetweenVar1and2 CorrelationBetweenVar2and3 CorrelationBetweenVar1and3 Desert Forest Arctic Swamp Lakeside ... In other words, I'd like to run a correlation between two variables, but I'd like for it to be grouped by type. Will be very grateful for any input! Regards, Sak Tag: Formula only for visible Cells Tag: 802551
  - 15
    - how to activate the scrolling within the worksheet? i cant able to scroll the worksheet using the mouse scroll action. now im scrolling using the vertical scroll bar only.give some ideas over this problem Tag: Formula only for visible Cells Tag: 802550
  - 16
    - Excel - printing specific areas Hi, my name's Gabriella. I've been working with Microsoft Excel to build a customer database. Each column is something else - address, city, zip code, phone number, job done, date the job was completed and name. I didn't put any formulas in, nor did I mess with anything that I don't know about within Excel - namely everything. This is where my question comes in. Say I want to print a list of people who've had jobs done in a specific city, or in a specific year. Is there a way to print that without having to go through the whole file and picking them out with ctrl+click? Is there some kind of formula, etc.? I know close to nothing about Excel's special features, so any help would be greatly appreciated. Thanks! :) -Gabriella Tag: Formula only for visible Cells Tag: 802547
  - 17
    - How do i convert a date into 3 seperate columns? I have a spreadsheet with dates shown as DD.MM.YYYY in text in a single column but I would like this to be represented as 3 seperate columns. One for day, one for month and one for year. Does anyone know a formula or command to do this? Many thanks, Tony Tag: Formula only for visible Cells Tag: 802544
  - 18
    - Changing column headers from alphabetical characters to numbers? I have inherited a spreadsheet that uses column headings in numbers as well as the rows. How can I reinstate the column headings to alphabetical characters? Tag: Formula only for visible Cells Tag: 802543
  - 19
    - fill combobox depending on selection from another combobox Hi all, I have been making a userform and so far all is great thanks to al the posts here, but, i can't find anything to help me with this problem. I have put 3 comboboxes on my userform, The first one i want to fill from a list. The second one i want to fill from a list depending on what was selected from the first combobox. And the third filled depending on selection from second. then all three selections put on a worksheet in seperate columns i hope this makes sense as i am only new to this. many thanks in advance Ad Tag: Formula only for visible Cells Tag: 802539
  - 20
    - Lookup unsorted list I have a very simple two column list. Col-A is "Products" col-B is "Date Purchase". Col-A is un-sorted and various products are repeated several times. I want a lookup formula that would tell me the most recent date when a particular product was purchased. If col-A was sorted ascending, I could use =LOOKUP(1E+100,INDEX(A2:B8,MATCH(A12,A2:A8,1),0)). Can someone please help. Tag: Formula only for visible Cells Tag: 802533
  - 21
    - Dynamic Array (multiple columns) Question I'm trying to create a dynamic array of data. I had previously dimensioned at the top of my sub the following: Public ItemArray(10,7) As Variant This gets me what I want, but only for 10 entries. I may actually have 50 entries. How do you dimension an array such that the amount of entries changes / is dynamic? The array is currently inside of a loop for items, and I want the array to update as below with each item. Hopefully this is enough information to piece together what I'm looking for. Thanks, Neil ItemCount = ItemCount + 1 ItemArray(ItemCount, 0) = ItemName ItemArray(ItemCount, 1) = AAvariable ItemArray(ItemCount, 2) = BAvariable ItemArray(ItemCount, 3) = CAvariable ItemArray(ItemCount, 4) = DAvariable ItemArray(ItemCount, 5) = EAvariable ItemArray(ItemCount, 6) = FAvariable ItemArray(ItemCount, 7) = GAvariable Tag: Formula only for visible Cells Tag: 802520
  - 22
    - Stop my references cancelling Hi, got a convoluted one (at least by my low standards). Using Excel 2003. I need to set up finance reports that total data using 3 criteria. To get the data, I export a report from my finance system to a spreadsheet, which I then copy into a blank (but pre-named) worksheet in the same workbook. I then "tidy up" this spreadsheet with a simple macro to delete blank rows/columns and add some columns with conditional arguments that helps sort out different types of transaction types. In the reports I'm using the following function: =SUMPRODUCT(--('Report'!V2:V30000!="S"),--('Report'!M2:M30000!="<code>"),('Report'!H2:H30000!)) Where: "S" is one of my added conditional arguments that identifies certain transaction types; <code> is the finance project code I'm reporting on; and H2:H30000 is the amounts I want to add up. Now, this formula works just fine - provided the data's already in the cells. Trouble is that I need these to stay just as they are when I then remove the original data - remember, I need to copy this in AFTER this is all set up. What I'm trying to achieve is a workbook to produce task reports (for about 30 tasks) that requires only we paste the raw data into the blank template, run my little macro and hey presto, everything else updates. I've looked at INDIRECT, but it doesn't seem to like array references. I can't seem to stop Excel automatically updating the reference any other way. Any help would be awesome. Tag: Formula only for visible Cells Tag: 802518
  - 23
    - Countif and Hi All, How I count then no. of occurances in following condition Status Speed Harsh Braking 5 Driving 15 Harsh Braking 12 Harsh Braking 35 Harsh Braking 40 Driving 12 Harsh Braking 4 Harsh Braking 0 Driving 15 How I can count the No. of harsh Braking if speed is more then or equal to 10. Thanks Tag: Formula only for visible Cells Tag: 802516
  - 24
    - Headers and Footers in Excel 2007 I have a worksheet that contains 69 pages. I need a custom footer on each of the pages. I know in Word, you could simply unselect "same as previous". However, I can't find any information anywhere. Any help is appreciated. Tag: Formula only for visible Cells Tag: 802509
  - 25
    - How can I open .wk4 file with Excel 2007? How can I import/open/edit .wk4 file in excel 2007? Tag: Formula only for visible Cells Tag: 802508

Is there a way to make a function apply only to visible cells, in otherwords
not include a cell if it is hidden?

Thanks!

Top

Re: Formula only for visible Cells by Bob

Bob
Thu Jul 24 12:48:33 CDT 2008

In Excel 2003, SUBTOTAL includes special codes, 101 for Average, 102 for
COUNT, etc., these ignore hidden cells

--
__________________________________
HTH

Bob

"lightbulb" <lightbulb@discussions.microsoft.com> wrote in message
news:3382850A-51B5-4943-8A0D-6E1302CFA0BE@microsoft.com...
> Is there a way to make a function apply only to visible cells, in
> otherwords
> not include a cell if it is hidden?
>
> Thanks!

Top