+ Reply to Thread
Results 1 to 8 of 8

Multiple sheets prefer not to use V/H lookups

  1. #1
    Registered User
    Join Date
    05-26-2010
    Location
    Auckland, NZ
    MS-Off Ver
    Excel 2003
    Posts
    52

    Multiple sheets prefer not to use V/H lookups

    hi all,
    Check out my sample, Ive been trying with with index and match but keep getting errors, not sure if it is because its vertical looking up horizontal, or blank cells in the range...


    The sample workbook is pretty self explanatory -I have 1 workbook with 2 sheets:

    Sheet 1
    In column A SHEET 1 I would like all the text cells from ROW 1 SHEET 2 to appear.
    In column B I would like the contents of row 10 that matches the name in column A -

    I am trying this with index and match but constant errors - dont know if its because of blank rows...
    Also if there is a better way to point sheet 1 names to sheet 2 names :-(
    AND if rows are added or deleted it must still point to that data...


    Sample attached if you can help
    http://www.4shared.com/document/MLGp..._sample_2.html


    thanks all

  2. #2
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Multiple sheets prefer not to use V/H lookups

    Try this in column A, will let you add rows :-

    =INDEX(Sheet2!$1:$1,1,ROWS(A$2:A7))

    and in column B

    =IF(ISTEXT(A2),INDEX(Sheet2!$A$10:$F$10,MATCH(Sheet1!A2,Sheet2!$A$1:$F$1,0)),"")

    which will display 0 if you delete a column

    note column a also displays 0 if you delete an item, you can hide this by changing the cell format toa custom format of ;;;@
    Regards
    Darren

    Update 12-Nov-2010 Still job hunting!

    If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.

    Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!


  3. #3
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Multiple sheets prefer not to use V/H lookups

    Also, please attach your sheets to the post its annoying to have to wait 30 seconds to download a file to help you from a sharing site!

  4. #4
    Registered User
    Join Date
    05-26-2010
    Location
    Auckland, NZ
    MS-Off Ver
    Excel 2003
    Posts
    52

    Re: Multiple sheets prefer not to use V/H lookups

    Quote Originally Posted by squiggler47 View Post
    Also, please attach your sheets to the post its annoying to have to wait 30 seconds to download a file to help you from a sharing site!
    Hi Robert,

    thankyou so much for the response. I didnt know I could attach a spreadsheet to the forum as i only saw link? pls advise how?

    Also are these forumulas such that i can drag them down to adjust themselves.

    I dont think think i understand your comments re """""

    thanks again

    issy

  5. #5
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Multiple sheets prefer not to use V/H lookups

    If you go to anvanced mode on the editor, and use the paperclip to attach a file!
    Click choose file, and dont forget to click upload!

    here is your file, and yes the formulas will copy down!
    Attached Files Attached Files

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Multiple sheets prefer not to use V/H lookups

    It would be much simpler if you stored your data in a proper Excel formaT - A TABLE WITH ALL DATA ON ONE SHEET
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  7. #7
    Registered User
    Join Date
    05-26-2010
    Location
    Auckland, NZ
    MS-Off Ver
    Excel 2003
    Posts
    52

    Re: Multiple sheets prefer not to use V/H lookups

    Hi Roy,

    the data is auto generated :-(

    If you dont mind me asking - What makes it go to a 0 if one cell is deleted?
    can i add more data and blank cells and expand the range?

    ta

    Issy


    Quote Originally Posted by royUK View Post
    It would be much simpler if you stored your data in a proper Excel formaT - A TABLE WITH ALL DATA ON ONE SHEET

  8. #8
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Multiple sheets prefer not to use V/H lookups

    Excel interprets the blank cell as a number when fetching from another cell, hence the formatting of the cell to CUSTOM with the format string of ;;;@, the other option is to format column A as text, but this has other side effects!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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