Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Columns based on cell value
Hoping I can explain this correctly. I have multiple
spreadsheets in the same workbook that are all the same but contain different information. I am trying to get a seperate sheet to look at all other worksheets and if a cell contains a certain value, then it will copy that column into the new sheet. Example Data: This is the column view of two sheets SHEET1 ColA ColB ColC ColD Red Blue Red Green 1 2 3 4 SHEET2 ColA ColB ColC ColD Blue Red Blue Green 1 6 3 4 I would like SHEET3 to contain the columns above that contain red in the first cell so new sheet would look like: ColA ColB ColC Red Red Red 1 3 6 Is there a way to do this programatically? If so any assistance would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Columns based on cell value
Sorry, could you please explain the logic? Why in sheet3, Column A = 1, Column B = 3, Column C = 6?
I guess Column A = 1, Column B = 6, Column C = 3... If so, I understand. If not, please explain the logic. Anyway, assuming what I though is correct. The following custom function can solve your problem. '------------------------------------------------------------------------ Function SUMIF3D(ByVal sum_range As range, ByVal compare_range As range, ByVal find_value) Dim s As Object, tmp$, tmp2$ Application.Volatile tmp = sum_range.Cells(1).Address(False, False) tmp2 = compare_range.Cells(1).Address(False, False) For Each s In sum_range.Parent.Parent.Worksheets If s.Name < sum_range.Parent.Name Then If s.range(tmp2).Value = find_value Then SUMIF3D = Application.Sum(s.range(tmp)) + SUMIF3D End If End If Next End Function '--------------------------------------------------------------- sum_range = the address of the cell you want to pick out in all worksheets. (A2 in your example) compare_range = the address of the cell you want to compare in all worksheets. (A1 in your example) find_value = the value which, if found in "compare_range", pick out the corresponding value in "sum_range" (RED in your example) In Sheet3, cell A1, you type "Red". In cell A2, you type: =SUMIF3D(A2,A1,A1) Then, you can drag the formula across to the cells on the right. You may note the, instead of just "picking the values", it sums the values which satisfy the criteria. Regards, Edwin Tam ----- wrote: ----- Hoping I can explain this correctly. I have multiple spreadsheets in the same workbook that are all the same but contain different information. I am trying to get a seperate sheet to look at all other worksheets and if a cell contains a certain value, then it will copy that column into the new sheet. Example Data: This is the column view of two sheets SHEET1 ColA ColB ColC ColD Red Blue Red Green 1 2 3 4 SHEET2 ColA ColB ColC ColD Blue Red Blue Green 1 6 3 4 I would like SHEET3 to contain the columns above that contain red in the first cell so new sheet would look like: ColA ColB ColC Red Red Red 1 3 6 Is there a way to do this programatically? If so any assistance would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
graph based on the city i select in a particular cell | Charts and Charting in Excel | |||
Select a cell based on an other cell's value | Excel Worksheet Functions | |||
How do I Select Multiple Non Adjacent Rows based on a cell value? | Excel Worksheet Functions | |||
how can I select a range of cells based on a value of a cell? | Excel Discussion (Misc queries) | |||
Select a range of columns based on active cell | Excel Programming |