Retrieve Data from Multiple sheets (Office 2003)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

cheers, francis

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

[indent]

---

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

---

[/indent]

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Correct, it was an oversight.

Reply | Quote

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

Steve

Reply | Quote

No problem!

Reply | Quote

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

Reply | Quote