How to refer to column name from Excel table in formula

Hi guys,

Is there a way to refer to the column name from a table in a formula using a cell reference?

I am trying to count if my columns are non-blanks or blanks using the COUNTIFS function. The thing is that I am making a formula for this for each column in my table.

So I have 4 columns in my table:

Column 1 = Source File Column 2 = Apples Column 3 = Oranges Column 4 = Grapes

So the formula looks like this
=COUNTIFS(KS_CR2_1_ARK[Source File];CONCAT($C$2;".rvt");KS_CR2_1_ARK[Should be a reference to cells in A with the names ];">""")

The thing is that if you use the table reference you need to select the correct name each time. However I would just like to refer to a list of names corresponding to the column name and drag the formula down.

So the list would be:

Cell = Name
A1 = Apples
A2 = Oranges
A3 = Grapes

So my formula for each would look like this

=COUNTIFS(KS_CR2_1_ARK[Source File];CONCAT($C$2;".rvt");KS_CR2_1_ARK[A1];">""") => COUNTIFS(KS_CR2_1_ARK[Source File];CONCAT($C$2;".rvt");KS_CR2_1_ARK[Apples];">""")
=COUNTIFS(KS_CR2_1_ARK[Source File];CONCAT($C$2;".rvt");KS_CR2_1_ARK[A2];">""") => COUNTIFS(KS_CR2_1_ARK[Source File];CONCAT($C$2;".rvt");KS_CR2_1_ARK[Oranges];">""")
=COUNTIFS(KS_CR2_1_ARK[Source File];CONCAT($C$2;".rvt");KS_CR2_1_ARK[A3];">""") => COUNTIFS(KS_CR2_1_ARK[Source File];CONCAT($C$2;".rvt");KS_CR2_1_ARK[Grapes];">""")


However I can't do this? Is there anyone who has an idea of how to do this?

AliGW thanks for your response. However I don't think that helps, since I need to look in the correct external data source that is named KS_CR2_1_ARK. I have more data sources with same column names, hence why I need to look in the specific ones.