+ Reply to Thread
Results 1 to 7 of 7

General question regarding hidden rows

  1. #1
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    General question regarding hidden rows

    Is it typically better to have a separate sheet for calculations or is it better to have everything on one sheet with the formula rows hidden? I am working on a workbook that will contain 12 separate sheets (for each month) and each sheet requires a whole set of calculations. The preliminary version of this was more than 25 sheets long. To simplify, and not deal with Sheet Name reference issues, is it just easier to do all the calculations on each monthly sheet and just hide them away?

  2. #2
    Forum Contributor
    Join Date
    12-28-2006
    MS-Off Ver
    2010
    Posts
    118

    Re: General question regarding hidden rows

    I would prefer less sheets and hidden formulas

  3. #3
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    636

    Re: General question regarding hidden rows

    I think you should also use Named Ranges also. So if you have a date input source at A1 - you would name the range A1 FirstDate.
    Then your formulas (and if you write and VBA) would say:
    Please Login or Register  to view this content.
    instead of
    Please Login or Register  to view this content.
    That way if you move things around (insert columns or rows) it won't break your formulas or VBA (in most cases)

  4. #4
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: General question regarding hidden rows

    Whoa. Can you elaborate more on that last point please. I'm not familiar with that process, and it could be very useful.

  5. #5
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    636

    Re: General question regarding hidden rows

    Highlight a cell or range (group) of cells - Under [formula]Please Login or Register to view this content.[/CODE] etc...
    The main thing, if you get to using VBA and have a line like:
    Please Login or Register  to view this content.
    But then you insert a row, VBA does not adjust from A1 to A2
    But if you were to Name the Cell, the naming feature would make the adjustment and VBA doesn't break.
    Learned the hard way.
    Note: I started naming my ranges with a set predictable pattern Instead of: Rep I would name it sheetnameRep etc...

  6. #6
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: General question regarding hidden rows

    Okay, so if I define the original list as A2:A27 as Replist, what happens when row 28 comes along. Also, how would I utilize this later? Can you give me an example or two please. Thanks

    EDITED TO ASK: instead of naming the range, in whatever formula I am using, I will just choose this name....as you listed in your =Firstdate-Seconddate example

    Also, in your second example, what is Rep?
    Last edited by tapsmiled; 02-20-2014 at 04:40 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    636

    Re: General question regarding hidden rows

    Rep would be another named range. So if I wanted to enter the rep name in the spreadsheet @ A1 and then return a result in B1. I would name A1 Rep. B1 would contain =vlookup(Rep,Replist,2,False) ... in other words lookup the value in range Rep vertically in Range Replist and give me the value from the 2nd column but only if you find the value Rep
    You can name as many ranges as you like

    If you added Row 28 - you would have to edit the named range to include 28 - But if you inserted a row ABOVE row 27 - the range would automatically expand to include row 28

    So - If you have space, always include a blank row at the end of your named range, when you need to enter more info highlight that last bank area and insert row, then add the data. In the example A2:A27 A27 would be blank. I would click on the 27 to select the whole row and insert new row (or if you don't want the whole row click A27 right click - insert - shift down) the range would then automatically read A2:A28

    More to the point - now if I wanted to choose the value for cell Rep - I could set Data validation list to RepList - now I have a dropdown that will expand as I add new reps

    Back to naming ranges - If I name the tab that contains the list RepInfo, then I would name the range RepInfoRepList - that way if I added tab Equipment I could have a new different range RepList on it (by naming it EquipmentRepList) - Or if I wanted to edit or refer to lists and I forget what I named it - I can start typing the tab name and Excel would remind me

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Question on hidden rows..
    By Ellen 2Excel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-15-2014, 05:01 PM
  2. general question
    By dealer in forum The Water Cooler
    Replies: 18
    Last Post: 07-14-2010, 01:49 PM
  3. General Question
    By Ragdyer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 07:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1