Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collecting data from other worksheets...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collecting data from other worksheets...
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 -- Regards, Tom Ogilvy "ajayb" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collecting data from other worksheets...
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collecting data from other worksheets...
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 "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "ajayb" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collecting data from other worksheets...
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). -- Regards, Tom Ogilvy "ajayb" wrote: 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 "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "ajayb" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Collecting data | Excel Discussion (Misc queries) | |||
Collecting data | Excel Discussion (Misc queries) | |||
Help Collecting Data | Excel Discussion (Misc queries) | |||
Collecting data from worksheets. | Excel Worksheet Functions | |||
collecting data from various worksheets | Excel Worksheet Functions |