Using Column Names In Formulas And Macros

David_9627

New Member
Joined
Aug 1, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello,

Simple question.... Can I use a column Name (Status), instead of it's alphanumeric reference (L:L) ?

I'm using Spiceworks as my Helpdesk Ticketing service for Users to log their helpdesk requests. Using the Reports function in Spiceworks I can export ticket details as XLXS files containing columns of information that I can then sort and reference to create a useful pie chart showing number of ticket opened, number of tickets closed. etc. to show to Management.

I export the data from Spiceworks and create a spreadsheet named Last_Month, which I then use to build the pie chart and other reported information.

Example here....

=COUNTIF(Last_Month!L:L, "Closed")

Column L in this case is a column named Status.

In this example it counts the number of tickets in a Closed status.

Management have asked me to capture some additional information for the monthly reports. Currently I have to go in and change each of the existing formulas to adjust for the shift in the column reference.
In this case column L is no longer my ticket status information, so I have to go in, find the correct column and update the formula - =COUNTIF(Last_Month!**NEW_COLUMN**:**NEW_COLUMN**, "Closed")

A time consuming pain in the @rse.

Is there a way for me to use COUNTIF and the column name "Status" rather than L:L (or whatever it changes to)?

Additionally, can this same principle be used to Sort a column by referencing the column name within a VBA Macro?

Any guidance or a solution would be greatly appreciated.

Regards,

David
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hello,

Simple question.... Can I use a column Name (Status), instead of it's alphanumeric reference (L:L) ?

I'm using Spiceworks as my Helpdesk Ticketing service for Users to log their helpdesk requests. Using the Reports function in Spiceworks I can export ticket details as XLXS files containing columns of information that I can then sort and reference to create a useful pie chart showing number of ticket opened, number of tickets closed. etc. to show to Management.

I export the data from Spiceworks and create a spreadsheet named Last_Month, which I then use to build the pie chart and other reported information.

Example here....

=COUNTIF(Last_Month!L:L, "Closed")

Column L in this case is a column named Status.

In this example it counts the number of tickets in a Closed status.

Management have asked me to capture some additional information for the monthly reports. Currently I have to go in and change each of the existing formulas to adjust for the shift in the column reference.
In this case column L is no longer my ticket status information, so I have to go in, find the correct column and update the formula - =COUNTIF(Last_Month!**NEW_COLUMN**:**NEW_COLUMN**, "Closed")

A time consuming pain in the @rse.

Is there a way for me to use COUNTIF and the column name "Status" rather than L:L (or whatever it changes to)?

Additionally, can this same principle be used to Sort a column by referencing the column name within a VBA Macro?

Any guidance or a solution would be greatly appreciated.

Regards,

David
If its a structured table then you can use like
Excel Formula:
=COUNTIF(TableName[Status],"Closed")
 
Upvote 0
Hi David and welcome to the board!

The short answer is yes to both questions.
Before (1)
David.xlsm
KLM
1header1Statusheader3
2OpenOpenOpen
3ClosedOpenOpen
4ClosedOpenOpen
5ClosedClosedOpen
6OpenClosedOpen
7ClosedOpenOpen
8ClosedOpenOpen
9ClosedClosedOpen
10ClosedClosedOpen
11ClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:M1="Status")*(K1:M11="closed"))


After(1)
David.xlsm
KLMNOP
1header1header2header3header4Statusheader5
2OpenOpenOpenOpenOpenOpen
3ClosedClosedClosedClosedOpenOpen
4ClosedClosedClosedClosedOpenOpen
5ClosedClosedClosedClosedClosedOpen
6OpenOpenOpenOpenClosedOpen
7ClosedClosedClosedClosedOpenOpen
8ClosedClosedClosedClosedOpenOpen
9ClosedClosedClosedClosedClosedOpen
10ClosedClosedClosedClosedClosedOpen
11ClosedClosedClosedClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:P1="Status")*(K1:P11="closed"))


Before (2)
David.xlsm
KLM
1header1Statusheader3
2OpenOpenOpen
3ClosedOpenOpen
4ClosedOpenOpen
5ClosedClosedOpen
6OpenClosedOpen
7ClosedOpenOpen
8ClosedOpenOpen
9ClosedClosedOpen
10ClosedClosedOpen
11ClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:M1="Status")*(K1:M11="closed"))


Code
VBA Code:
Sub David_9627()
    Dim i As Long
    i = Range("1:1").Find("Status", Cells(1, 1)).Column

    With Cells(1, i).CurrentRegion
        .Sort key1:=Cells(1, i), Order1:=xlAscending, Header:=xlYes
    End With
End Sub

After (2)
David.xlsm
KLM
1header1Statusheader3
2ClosedClosedOpen
3OpenClosedOpen
4ClosedClosedOpen
5ClosedClosedOpen
6OpenOpenOpen
7ClosedOpenOpen
8ClosedOpenOpen
9ClosedOpenOpen
10ClosedOpenOpen
11ClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:M1="Status")*(K1:M11="closed"))


Although as @SanjayGulatiMusafir suggested in post #2, you're probably better off going with tables instead.

Hope this helps.
 
Upvote 0
If its a structured table then you can use like
Excel Formula:
=COUNTIF(TableName[Status],"Closed")
1659513755502.png
 
Upvote 0
You can see from the screenshot, I have separate tabs with the data, and use the formulas to capture the information and display it on the Main Page tab..

How should it work when I'm using different worksheets?
 
Upvote 0
Hi David and welcome to the board!

The short answer is yes to both questions.
Before (1)
David.xlsm
KLM
1header1Statusheader3
2OpenOpenOpen
3ClosedOpenOpen
4ClosedOpenOpen
5ClosedClosedOpen
6OpenClosedOpen
7ClosedOpenOpen
8ClosedOpenOpen
9ClosedClosedOpen
10ClosedClosedOpen
11ClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:M1="Status")*(K1:M11="closed"))


After(1)
David.xlsm
KLMNOP
1header1header2header3header4Statusheader5
2OpenOpenOpenOpenOpenOpen
3ClosedClosedClosedClosedOpenOpen
4ClosedClosedClosedClosedOpenOpen
5ClosedClosedClosedClosedClosedOpen
6OpenOpenOpenOpenClosedOpen
7ClosedClosedClosedClosedOpenOpen
8ClosedClosedClosedClosedOpenOpen
9ClosedClosedClosedClosedClosedOpen
10ClosedClosedClosedClosedClosedOpen
11ClosedClosedClosedClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:P1="Status")*(K1:P11="closed"))


Before (2)
David.xlsm
KLM
1header1Statusheader3
2OpenOpenOpen
3ClosedOpenOpen
4ClosedOpenOpen
5ClosedClosedOpen
6OpenClosedOpen
7ClosedOpenOpen
8ClosedOpenOpen
9ClosedClosedOpen
10ClosedClosedOpen
11ClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:M1="Status")*(K1:M11="closed"))


Code
VBA Code:
Sub David_9627()
    Dim i As Long
    i = Range("1:1").Find("Status", Cells(1, 1)).Column

    With Cells(1, i).CurrentRegion
        .Sort key1:=Cells(1, i), Order1:=xlAscending, Header:=xlYes
    End With
End Sub

After (2)
David.xlsm
KLM
1header1Statusheader3
2ClosedClosedOpen
3OpenClosedOpen
4ClosedClosedOpen
5ClosedClosedOpen
6OpenOpenOpen
7ClosedOpenOpen
8ClosedOpenOpen
9ClosedOpenOpen
10ClosedOpenOpen
11ClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:M1="Status")*(K1:M11="closed"))


Although as @SanjayGulatiMusafir suggested in post #2, you're probably better off going with tables instead.

Hope this helps.
Same question for you Kevin,

You can see from the screenshot I added, I have separate tabs with the data, and use the formulas to capture the information and display it on the Main Page tab..

How should it work when I'm using different worksheets?
 
Upvote 0
Same question for you Kevin,

You can see from the screenshot I added, I have separate tabs with the data, and use the formulas to capture the information and display it on the Main Page tab..

How should it work when I'm using different worksheets?
You simply put the sheet name followed by an exclamation mark before the range address, such as:

Excel Formula:
=SUMPRODUCT((Last_Month!K1:M1="Status")*(Last_Month!K1:M11="closed"))
 
Upvote 0
You simply put the sheet name followed by an exclamation mark before the range address, such as:

Excel Formula:
=SUMPRODUCT((Last_Month!K1:M1="Status")*(Last_Month!K1:M11="closed"))
Works great,

=SUMPRODUCT((Last_Month!A1:M1="Status")*(Last_Month!A1:M100="Open"))

Thanks Kevin, that's done the trick. Two-Thumbs-Up!
 
Upvote 0
You simply put the sheet name followed by an exclamation mark before the range address, such as:

Excel Formula:
=SUMPRODUCT((Last_Month!K1:M1="Status")*(Last_Month!K1:M11="closed"))
Sorry David, forgot the single quotation marks...

=SUMPRODUCT(('Last_Month'!K1:M1="Status")*('Last_Month'!K1:M11="closed"))
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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