Return Colum headers based on positive and negative cell value

F

Fahad2898

New Member
Joined
Apr 7, 2019
Messages
4
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

[TABLE="width: 500"]
<tbody>[TR]
[TD]6[/TD]
[TD]Module 2[/TD]
[TD]Disc Module 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Module 4[/TD]
[TD]Disc Module 4[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 711"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Module 1[/TD]
[TD]Dis Module 1[/TD]
[TD]Module 2[/TD]
[TD]Dis Module 2[/TD]
[TD]Module 3[/TD]
[TD]Dis Module 3[/TD]
[TD]Module 4[/TD]
[TD]Dis Module 4[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]-5[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
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")


Code: 
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
 
Upvote 0
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.
 
Upvote 0
Fahad2898 said:
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.
Click to expand...

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.
 
Upvote 0
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
ABCDEFGHIJK
16Module 2Dis Module 2
2Module 4Dis Module 4
3
4
5Module 1Dis Module 1Module 2Dis Module 2Module 3Dis Module 3Module 4Dis Module 4Total
6
7120020
8210-55
9310-520-520
10410010020
11550505015
12610010-713
13710010
14820-1020-1020
15910010020
161030-1020
171120202060
181210-55
Sheet



[TABLE="width: 100"]
<colgroup><col width="100"></colgroup>[TR]
[TD="class: xl63, width: 100"][/TD]
[/TR]
[/TABLE]
 
Upvote 0
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]]))),"")
 
Upvote 0
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/
 
Upvote 0
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.

AhoyNC said:
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/
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
AutoFilter Field Macro Returns a Header Row only
2
Replies
11
Views
102
MARK858
MARK858
S
  • Question Question
SUMPRODUCT of positives and negatives values
Replies
1
Views
727
STFG
S
E
  • Solved
If formula
Replies
3
Views
58
Peter_SSs
Peter_SSs
JenniferMurphy
  • Question Question
Is this a good solution to Excel's table header weaknesses?
Replies
3
Views
301
zero269
Z
I
  • Question Question
Divide cell value so weekly stock is balance between warehouses
Replies
1
Views
229
Kaper
Kaper

Forum statistics

Threads
1,223,888
Messages
6,175,203
Members
452,617
Latest member
Narendra Babu D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top