VBA finding column number and add specific values in column

[NewbieMacro]

Hello,

I have a sheet named "FinalOverview":

another sheet called "Data". The "Data" sheet has N columns and each column has specific numerical values. Ex:

The user enters values such as "2020" or "2021" in the Sheet "FinalOverview" and will click a btn to trigger macro. The macro then searches for column having "2020" and then uses the corresponding row values to do some calculation such as "0.85*2000+(12-45)" and then the value needs to be put into the cell of FinalOverview below 2020 (C3)

I am fully confused as to how to extract the column ID, use it to do calculation and put it back into the cell.

Can you please guide me

 

[Andrzejek]

Since you have mentioned VBA, you may have a better luck asking your question in forum707

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[NewbieMacro]

Ah okay will do, thank you!

 

[mintjulep]

But it seems likely that this could be done with formulas. Or a Pivot Table.

 

[NewbieMacro]

Completely agree with you on pivot and formula. I did the same but my supervisor wants it in macros for some reason and I am trying to piece together how to do it

 

[SkipVought]

Hi, and welcome to Tek-Tips.

Your DATA sheet is a nightmare for Excel to analyze. You have structured your data in a way that inexperienced users do because it seems to make sense. It is actually in a report structure rather than a data table structure. The way it is will make it difficult for Excel to analyze.

This is how Excel would rather "see" your data...

[pre]
Legend Year LegVal
Legend1 2019 1000
Legend2 2020 2000
Legend3 2021 3000
Legend1 2019 10
Legend2 2020 12
Legend3 2021 15
Legend1 2019 34
Legend2 2020 45
Legend3 2021 98
[/pre]

Make your Data table a Structured Table named tDATA
So now if you want the 2020 Legend3 value...
[tt]
=SUMPRODUCT((tDATA[Legend]="Legend3")*(tDATA[Year]=2020)*(tDATA[LegVal]))
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

Chances are, "my supervisor wants it" to be "a nightmare for Excel to analyze" [this DATA]
So, all you can do is

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[NewbieMacro]

Yes, I have no idea why I would need a Macro when it can be done with Tables, Named References or any other way. But thanks again Andy for the snippet, I am already modifying it to meet my exact needs

 

[SkipVought]

Here's your formula, 0.85*2000+(12-45), using my table...

Assuming that the cell that the user will enter a year, has a Range Name SelectedYear then this is the formula for the calculated value:
[tt]
=0.85*SUMPRODUCT((tDATA[Legend]="Legend1")*(tDATA[Year]=SelectedYear)*(tDATA[LegVal]))+
(SUMPRODUCT((tDATA[Legend]="Legend2")*(tDATA[Year]=SelectedYear)*(tDATA[LegVal]))-
SUMPRODUCT((tDATA[Legend]="Legend3")*(tDATA[Year]=SelectedYear)*(tDATA[LegVal])))
[/tt]

BTW, I'd suggest adding a worksheet, I call mine Factors and on that sheet list the years or better yet, query the DATA sheet using...
[pre]
SELECT DISTINCT YEAR
FROM [DATA$]
[/pre]

Then, use this list in a Data/Validation/List Source, in-cell drop down, for the user to Select the desired year.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!