Collecting data from other worksheets...

[Guest]

Hi all,

I hope you can help.

I have a workbook with 25 sheets. Each sheet contains a separate customer
with details about their account.

I want to be able to have a sheet at the front of the workbook that will
give me a drop down selection list with the list of customer's names in.
When I select the customer's name I want the front sheet to be populated with
the data from the customer's worksheet.

I can do the easy bit of creating the drop down list but I can't figure out
how to get excel to go to the relevant worksheet for that customer and copy
the data across. I'm guessing I need to use a lookup or something but I'm
really stuck.

Can anyone help out?

Thanks

Andy

 

[Guest]

Assume the dropdown list is in A1

in B1

=if(A1<>"",Indirect("'"&A1&"'!B1"),"")

if A1 contains Johnson, Donald then the reference becomes

='Johnson, Donald'!B1

and brings in the data from cell b1 in the sheet named Johnson, Donald

 

[merjet]

Put this (or similar) in the ComboBox's Change event.

For iCol = 1 To 4
Sheets("Sheet1").Cells(1, iCol) = Sheets(ComboBox1.Value).Cells(1,
iCol)
Next iCol

Hth,
Merjet

 

[Guest]

Hi Tom,

Thank you for that, it worked perfectly.

I have amended it to read as follows:

=IF(A1="Clear","",(IF(A1<>"",INDIRECT("'"&A1&"'!B1"),"")))

So that "Clear" may be selected to wipe the sheet clean. However, I am now
stuck on this:

When the function is referring to a cell on another sheet that is empty it
puts 0. How can I stop that?

Any ideas?

Thanks

Andy

 

[Guest]

You would work this concept into your existing formula:

=IF(Sheet2!A1="","",Sheet2!A1)

or
=IF(ISBLANK(Sheet2!A1),"",Sheet2!A1)

It doesn't make the cell truly empty, but it appears so. (you can't have an
empty cell if there is a formula in it).