Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieve Data from Multiple sheets (Office 2003)

    Hi all,

    Is there a way that I can find an id and then get excel to retrieve data for all rows of the first 4 column of 'Holding" with this id in the Workbook onto one worksheet "Report"? Further, I would like also to add in the row data of the first three column under the sheet named Account to the Report . Lastly, can the sheets "Holding" and "Account" be hide?

    attached a sample file for your assistance, thanks

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Retrieve Data from Multiple sheets (Office 2003)

    What do ypu mean by 'eind an ID'? Will the user enter it, or...?
    Can there be more than one row with the same ID in the Holding sheet? Or in the Account sheet?

    You can hide a worksheet by selecting Format | Sheet | Hide. You must leave at least one sheet unhidden.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Data from Multiple sheets (Office 200

    Hi Hans

    What I means is that when I enter an id in the Report, Excel will find the matching data of the other columns namely; locname, secname, hldg, account, RM and contact, thus showing them in the Report's relevent headings. These ids are unqiue. I have a feeling I can use the function Match or Index and Match but aren't very sure how to Or is it Vlookup, getting confuse with some of these functions. <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Retrieve Data from Multiple sheets (Office 200

    You can use VLOOKUP - first, a series of VLOOKUP formulas to retrieve values from the Holding sheet, and another series of VLOOKUP formulas to retrieve values from the Account sheet. See the attached version.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Retrieve Data from Multiple sheets (Office 200

    Here is a slightly enhanced version in which you can select the ID from a dropdown list (using Data | Validation).

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Data from Multiple sheets (Office 200

    Hi Hans,

    Thanks, the result is basically what I am want, but can I protect the formula from accidentally erase or better still hide and protect the formula. Another thing is how can I have the #N/A not shows if there aren't any data.

    btw, notice that some formula didn't use the False and some do, why is it so.?

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Retrieve Data from Multiple sheets (Office 200

    The missing FALSE was an oversight.

    You can unlock Report!A6 in Format | Cells..., then protect the Report sheet using Tools | Protection | Protect Sheet..., specifying that only unlocked cells can be selected.
    You can use the ISNA function in combination with IF to hide #N/A values.
    See attached version.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Retrieve Data from Multiple sheets (Office 200

    1) If you lock and hide the appropriate cells (locked is default, unlock any cells the user can change) via format - cells - protection (tab), when you protect the worksheet (Tools - protection - protect sheet) the cells can not be edited and the formulas will not display in the formula bar.

    2)Instead of something like this:
    =VLOOKUP($A6,Holding!$A$2:$F$9,3,FALSE)

    You can use:
    =if(isna(VLOOKUP($A6,Holding!$A$2:$F$9,3,FALSE))," ",VLOOKUP($A6,Holding!$A$2:$F$9,3,FALSE))

    This will put a null string in the cell instead of displaying the NA

    3) It appears that only in the original posting did it have 1 formula with a FALSE. In the second they were all FALSE. I think the intial was a mistake, they should all be FALSE since they are all exact matches. TRUE (or missing) is for lookups that are approximate and then the list must be sorted.

    Steve

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Retrieve Data from Multiple sheets (Office 200

    > I think the intial was a mistake, they should all be FALSE

    Correct, it was an oversight.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Retrieve Data from Multiple sheets (Office 200

    Yes, I saw your note right after I posted mine.

    I considered deleting it, since you had already answered, but I was afraid that before I deleted it, you would have responded and that your reply would have just been hanging there, so I kept my response <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Retrieve Data from Multiple sheets (Office 200

    No problem! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  12. #12
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Data from Multiple sheets (Office 200

    Hans and Steve

    This is excellent! but if I want to add a new heading "Charges" in column H right after "Contact" and this column's data are variable which means the user need to enter it manually into the cell. However, using the protection sheet, users are unable to enter the data.
    Can I have it that the column A 's Id data is fixed and match for column B to G with the given data in Holding and Account and the other columns from H onward be editable?

    btw, even with the missing FALSE, it does match exactly. Does this means that there is no need the word False?

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  13. #13
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Data from Multiple sheets (Office 200

    Hi Hans,

    I have figured it out how the users can enter data into other cells and yet have the formulas hidden and protected.
    This forum never fail me from the first time I posted since last year till today. If you have a better idea of what I am trying to do, pls inform me.

    I am re-learning all these again after a long haul.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Retrieve Data from Multiple sheets (Office 200

    <hr>even with the missing FALSE, it does match exactly. Does this means that there is no need the word False?<hr>

    No. Not requiring an exact match with unsorted data will, on occasion, yield the correct result, but most times will not. FALSE should be used if an exact match is required.

    Steve

Posting Permissions

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