+ Reply to Thread
Results 1 to 12 of 12

Need cell range to stay the same

  1. 05-15-2015, 09:43 PM #1
    HeatherBelle79
    HeatherBelle79 is offline
    Registered User
    Join Date
    10-17-2008
    Location
    Clinton, UT
    Posts
    28

    Need cell range to stay the same

    Hi...

    I'm doing a VLOOKUP function, and my cell range will be the same for all rows, but when I auto-fill, it changes my cell range by one-- other than manually typing in that cell range everytime, is there a way I can override the auto-fill?

    Example:
    THIS happens:
    VLOOKUP(A2, Sheet2!A2:A6, etc)
    VLOOKUP(A3, Sheet2!A3:A7, etc)
    VLOOKUP(A4, Sheet2!A4:A8, etc)

    I need THIS:
    VLOOKUP(A2, Sheet2!A2:A6, etc)
    VLOOKUP(A3, Sheet2!A2:A6, etc)
    VLOOKUP(A4, Sheet2!A2:A6, etc)

    I've tried manually filling in 3 cells, then auto-filling when all 3 cells are selected, and all that does is change my data table range to A5:A9 for 3 rows, then A9:A13 for 3 rows, etc...

    TIA!!

    Don't judge me for playing with spreadsheets on a Friday night
    Reply With QuoteRegister To Reply
  2. 05-15-2015, 10:11 PM #2
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,952

    Re: Need cell range to stay the same

    VLOOKUP(A2, Sheet2!$A$2:$A$6, ... )

    Why is your lookup range only one column. VLookup returns a value from a specified column to the right of the found value.

    VLOOKUP(A2, Sheet2!$A$2:$F$6, 3, False)

    You could also, select the block of cells (A2:F6) and give them a name, say, "LookupTable". Then the formula would be:

    VLOOKUP(A2, LookupTable,3, False)
    Last edited by protonLeah; 05-15-2015 at 10:18 PM.
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  3. 05-15-2015, 10:18 PM #3
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need cell range to stay the same

    Quote Originally Posted by HeatherBelle79 View Post
    Don't judge me for playing with spreadsheets on a Friday night
    What else is there to do?

    About cell and range references
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  4. 05-15-2015, 10:24 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Need cell range to stay the same

    Quote Originally Posted by Tony Valko View Post
    What else is there to do?
    ...wait for Saturday night so you can do it all again?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 05-15-2015, 10:55 PM #5
    HeatherBelle79
    HeatherBelle79 is offline
    Registered User
    Join Date
    10-17-2008
    Location
    Clinton, UT
    Posts
    28
    Sorry... It's not... Just made a typo (wasn't copying and pasting my formula)... But regardless, even if it's Sheet2!A1:F6, the problem remains the same. When i auto fill, it changes to A2:F7...

    How do you give the table range a name?
    Last edited by HeatherBelle79; 05-15-2015 at 11:07 PM.
    Reply With QuoteRegister To Reply
  6. 05-15-2015, 11:11 PM #6
    HeatherBelle79
    HeatherBelle79 is offline
    Registered User
    Join Date
    10-17-2008
    Location
    Clinton, UT
    Posts
    28
    Quote Originally Posted by Tony Valko View Post
    What else is there to do?

    About cell and range references
    Almost missed your link! Thanks! I remember the $ thing now, but it's waaay in the back of my closet!

    And my Saturday will probably be working on a spreadsheet, too! it's for my hobby, so combining 2 of my favorite things! My hobby is a creative one, so is an odd mix of right brain and left brain HAHA
    Reply With QuoteRegister To Reply
  7. 05-15-2015, 11:40 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Need cell range to stay the same

    So, did you get what you needed? (thanks for the rep)
    Reply With QuoteRegister To Reply
  8. 05-16-2015, 12:09 AM #8
    HeatherBelle79
    HeatherBelle79 is offline
    Registered User
    Join Date
    10-17-2008
    Location
    Clinton, UT
    Posts
    28

    Re: Need cell range to stay the same

    For that! Now I can't figure out why this doesn't work
    =IF(AND(ISBLANK(F5),ISBLANK(H5)), "HELLO", F5*H5)

    I obviously don't want it to say "hello" (I want it to be blank), but that's how I'll know I got it right... just trying to avoid having a column of nasty "#VALUE!" on my sheet
    If those 2 cells are blank, display hello, else do the multiplication... I see nothing wrong--maybe it's just too late...
    Reply With QuoteRegister To Reply
  9. 05-16-2015, 12:15 AM #9
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Need cell range to stay the same

    What is in F5 and H5? If they contain a formula, ISBLANK() wont work, because they actually DO contain something. I personally prefer to use "" for a test like that...
    =IF(AND(F5="",H5=""), "HELLO", F5*H5)

    Perhaps its time to upload a sample workbook?
    Reply With QuoteRegister To Reply
  10. 05-16-2015, 12:18 AM #10
    HeatherBelle79
    HeatherBelle79 is offline
    Registered User
    Join Date
    10-17-2008
    Location
    Clinton, UT
    Posts
    28

    Re: Need cell range to stay the same

    Oh gotcha! Yes... there's formulas... I suspected that was the problem! Thanks! It worked
    Reply With QuoteRegister To Reply
  11. 05-16-2015, 12:20 AM #11
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Need cell range to stay the same

    awesome, glad it worked for you
    Reply With QuoteRegister To Reply
  12. 05-16-2015, 08:12 AM #12
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need cell range to stay the same

    Quote Originally Posted by HeatherBelle79 View Post
    =IF(AND(ISBLANK(F5),ISBLANK(H5)), "HELLO", F5*H5)
    If cells F5/H5 contain formulas that result in formula blanks then ISBLANK = FALSE.

    If you want to test those cells for numeric values try this...

    =IF(COUNT(F5,H5)<2,"HELLO",F5*H5)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Number should stay in a certain range
    By BOB202 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-24-2014, 05:12 AM
  2. Cell reference to stay the same when data is moved in referenced cell
    By OZZIEUNITED in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-10-2014, 05:32 AM
  3. Need Max(Cell Range) to stay the same
    By miekewl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-28-2011, 03:04 AM
  4. How to point to a cell so if it gets shifted you don’t follow data but stay on Cell?
    By fido95111 in forum Excel General
    Replies: 2
    Last Post: 10-21-2010, 11:19 AM
  5. [SOLVED] Why data in cell doesn't stay aligned with cell borders when prin.
    By Danno in forum Excel General
    Replies: 0
    Last Post: 10-03-2005, 03:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1