Dear all,
this is my first post on this board, hi everyone!
I have been struggling with an issue for two days now, and so far haven't been able to find a solution. Also, I'm a total VBA beginner, so any help is greatly appreciated!
I have two sheets in my workbook: The first is an output dashboard, the second a data table.
The data is structured in the following way:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]2016[/TD]
[TD]2015[/TD]
[TD]2014[/TD]
[TD]2013[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]58-XYZ,59-ABC,...[/TD]
[TD]52-DEF,48-CDE,...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
On my dashboard sheet, I've set up a dropdown filter menu to choose the respective year of interest for this issue as well as some other analyses (2016,2015,2014,2013,2012).
Now to the issue at hand: I need Excel to select the correct cell from my data sheet, based on the year selected for filtering (e. g. when selecting "2016" from my dropdown, Excel should select cell B2 from the data sheet; for "2015", select C2, etc.).
Next, I need Excel to split up the string in cell B2 (52-DEF,48-CDE,...) into separate strings (comma as seperator) and insert these separate strings as distinct rows into my output table like so:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Data[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]52-DEF[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]48-CDE[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
To top things off, the output table should appear to automatically expand based on how many separate values were pulled from the source cell.
For this, I've already successfully employed the following code within another part of the worksheet:
Any help putting all this together into VBA code would be greatly appreciated!
Cheers
this is my first post on this board, hi everyone!
I have been struggling with an issue for two days now, and so far haven't been able to find a solution. Also, I'm a total VBA beginner, so any help is greatly appreciated!
I have two sheets in my workbook: The first is an output dashboard, the second a data table.
The data is structured in the following way:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]2016[/TD]
[TD]2015[/TD]
[TD]2014[/TD]
[TD]2013[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]58-XYZ,59-ABC,...[/TD]
[TD]52-DEF,48-CDE,...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
On my dashboard sheet, I've set up a dropdown filter menu to choose the respective year of interest for this issue as well as some other analyses (2016,2015,2014,2013,2012).
Now to the issue at hand: I need Excel to select the correct cell from my data sheet, based on the year selected for filtering (e. g. when selecting "2016" from my dropdown, Excel should select cell B2 from the data sheet; for "2015", select C2, etc.).
Next, I need Excel to split up the string in cell B2 (52-DEF,48-CDE,...) into separate strings (comma as seperator) and insert these separate strings as distinct rows into my output table like so:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Data[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]52-DEF[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]48-CDE[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
To top things off, the output table should appear to automatically expand based on how many separate values were pulled from the source cell.
For this, I've already successfully employed the following code within another part of the worksheet:
Code:
Sub HideUnhide()
Dim LEB As Range
Application.ScreenUpdating = False
For Each LEB In Range("D56:D145")
If LEB.Value = 0 Then
LEB.EntireRow.Hidden = True
Else
LEB.EntireRow.Hidden = False
End If
Next LEB
Application.ScreenUpdating = True
End Sub
Cheers