Return Colum headers based on positive and negative cell value

[Fahad2898]

Hi

For the last hour or so I have been trying to find an answer through youtube but just can't it. Really appreciate if some one can help me with this, I just created a table from randon figures which represent the actual table at work, so the modules are products and the dis module colum is the discount given to the particular prodct it is next to, and at what time in the future that negative figure will change into zero if the discount is no longer there, what I am trying to do is drop down list of all rows and when I choose say row 6, I will get 2 drob down cells, one representing all the headers that has a positive value and the other one all the headers that has a negative value

6	Module 2	Disc Module 2
	Module 4	Disc Module 4

<tbody>
</tbody>

		Module 1	Dis Module 1	Module 2	Dis Module 2	Module 3	Dis Module 3	Module 4	Dis Module 4	Total
1						20	0			20
2				10	-5					5
3		10	-5				20	-5		20
4				10	0	10	0			20
5		5	0	5	0	5	0			15
6				10	0			10	-7	13
7				10	0					10
8		20	-10			20	-10			20
9						10	0	10	0	20
10		30	-10							20
11		20				20		20		60
12				10	-5					5

<tbody>
</tbody>

 

[JLGWhiz]

I think worksheet event code could do what you want. I don't know what you intend to do with the values once you return them so I wwill illustrate by using a message box. Assume the dropdown list is in Range("A1")

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        For Each c In Cells(Target.Value, 2).Resize(, 8) 'assumes data starts in column B.
            If c.Value > 0 Then txt = txt & vbLf & c.Value & "   " & c.Offset(, 1).Value
        Next
        MsgBox txt
    End If
Application.EnableEvents = True
End Sub

 

[Fahad2898]

Thanks, Appreciate your help.

To be honest I was hoping if the answer was a little bit easier and didn't involve VBA as my knowledge is almost none.

As to the reason or what I want it for, each row represnt a live quote and this whole function or what I am trying to do will be added to a dashboard so when we are looking for the value numbers of a quote we can also find what products are included or what discount are given.

Thanks again.

 

[JLGWhiz]

Thanks, Appreciate your help.

To be honest I was hoping if the answer was a little bit easier and didn't involve VBA as my knowledge is almost none.

As to the reason or what I want it for, each row represnt a live quote and this whole function or what I am trying to do will be added to a dashboard so when we are looking for the value numbers of a quote we can also find what products are included or what discount are given.

Thanks again.

I am not that good with formulas these days. I used them frequently back in the twentieth century when I was still working, but after I learned VBA my usage has dropped to almost nil. You would need a pretty elaborate IF() formula to pull the values into another sheet using a drop down. It can probably be done but it would take me a lot of trial an error to get it right because of my lack of usage. Maybe somebody else more skilled in that area will jump in and give you some assistance with it.

 

[AhoyNC]

Maybe you could work with something like this.
It assumes that all your Module 1, Module 2, etc will be positive numbers.
The formula in B1 is an array formula and must be entered with CTRL-SHIFT-ENTER.
Formula in C1 just ENTER.
Drag formulas down as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	6	Module 2	Dis Module 2
2		Module 4	Dis Module 4
3
4
5			Module 1	Dis Module 1	Module 2	Dis Module 2	Module 3	Dis Module 3	Module 4	Dis Module 4	Total
6
7	1						20	0			20
8	2				10	-5					5
9	3		10	-5				20	-5		20
10	4				10	0	10	0			20
11	5		5	0	5	0	5	0			15
12	6				10	0			10	-7	13
13	7				10	0					10
14	8		20	-10			20	-10			20
15	9						10	0	10	0	20
16	10		30	-10							20
17	11		20				20		20		60
18	12				10	-5					5
Sheet

<colgroup><col width="100"></colgroup>

 

[Fahad2898]

Thank you very much.

it wourks abosultey perfect with the example above and your formula, unfortunatley when I tried to copy it with my data which is a table, i think i got stuck with the rows section at the end, the message that there is a problem with this formula keeps coming up.

IFERROR(INDEX(RCFDATA[[#Headers],[TTT NG - Entry Level]:[Old Agreement License]],SMALL(IF(INDEX(RCFDATA[[TTT NG - Entry Level]:[Old Agreement License]],MATCH(Dashboard!N18,RCFDATA[Order],0),0)>0,COLUMN(RCFDATA[[#Headers],[TTT NG - Entry Level]:[Old Agreement License]])-COLUMN(RCFDATA[[#Headers],[TTT NG - Entry Level]]+1),ROWS(RCFDATA[[#Headers],[TTT NG - Entry Level]]:RCFDATA[[#Headers],[TTT NG - Entry Level]]))),"")

 

[AhoyNC]

Using table nomenclature with absolute cell references can be tricky (at least for me).
What you might try is to set up Excel not to use table names in formulas that will allow you to enter the formula as is with cell references. The other option is to turn your table back into a range, enter the formula and then turn the range back into a table.

To setup the first option of not using table names:
Go to FILE in the ribbon.
-Options
-Formulas
-Working with formulas
-Then uncheck the box by "Use table name in formulas"
-OK

If you want to use Table nomenclature then take a look at this site on how to enter absolute references for a formula in an Excel table.
https://exceloffthegrid.com/excel-tables-absolute-references/

 

[Fahad2898]

Thank you very much, you are a lifesaver, that worked exactly as I intended it to. Changed it to range and back to table when the formula worked.

Using table nomenclature with absolute cell references can be tricky (at least for me).
What you might try is to set up Excel not to use table names in formulas that will allow you to enter the formula as is with cell references. The other option is to turn your table back into a range, enter the formula and then turn the range back into a table.

To setup the first option of not using table names:
Go to FILE in the ribbon.
-Options
-Formulas
-Working with formulas
-Then uncheck the box by "Use table name in formulas"
-OK

If you want to use Table nomenclature then take a look at this site on how to enter absolute references for a formula in an Excel table.
https://exceloffthegrid.com/excel-tables-absolute-references/

 

[AhoyNC]

Your welcome. Thanks for the feedback.