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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

SanjayGMusafir

Well-known Member
Joined
Sep 7, 2018
Messages
660
Office Version
  1. 2021
Platform
  1. MacOS
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")
 

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,281
Office Version
  1. 365
Platform
  1. Windows
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.
 

David_9627

New Member
Joined
Aug 1, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
If its a structured table then you can use like
Excel Formula:
=COUNTIF(TableName[Status],"Closed")
1659513755502.png
 

David_9627

New Member
Joined
Aug 1, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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?
 

David_9627

New Member
Joined
Aug 1, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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?
 

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,281
Office Version
  1. 365
Platform
  1. Windows
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"))
 

David_9627

New Member
Joined
Aug 1, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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!
 

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,281
Office Version
  1. 365
Platform
  1. Windows
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"))
 

Forum statistics

Threads
1,182,203
Messages
5,934,259
Members
436,938
Latest member
Mthalaj

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
Top