CODEWITHSUNDEEP
excelvba
socool111
socool111

Reputation: 113

Using different formulas depending on the column the value input is found

So I am trying to make a calculator. The calculator will output values based on a formula depending on what column the input is found in....I have 3 different columns, all with email addresses in them. The idea is you plug in your email address, it looks up your email and finds it in column A, B or C. Then based on that it will do a few sumifs, among other things, depending on which column you are in (the calculation changes per column)

How can I do this? Also, a more basic question: how can I reference worksheets in VBA? (the vba would be in sheet 2, while the data it looks up, and sums etc is in sheet 1).

I hope this is specific enough, I do not have any code to start us off, mainly because I do not know where to start, and do not want to limit myself with poorly constructed code.

Also, I'm told this can be done without VBA, using some little known built in function...but idk what it is, so I can't look up how to use it...VBA or built in function- whichever gets the job done!

Thanks

Upvotes: 0

Views: 439

Answers (2)

EStraka
EStraka

Reputation: 373

I don't understand it but check out:

http://www.get-digital-help.com/2010/08/16/search-for-a-cell-in-a-table-and-then-display-the-column-title-in-excel/

It works. It will return the column heading without a series of cascading "IFERROR" statements.

You will have to test the results with a series of cascading "IF" statements to apply the formula you want, but I don't see any way around that.

NOTE: If the same email address appears more than once, it will only return the first column heading.

Upvotes: 0

Mardin Yadegar
Mardin Yadegar

Reputation: 437

I would use an index combined with match for the first two arguments of the index function. From there, you will be able to determine which column you're in (whether you're in A B or C). Once you have that value, it's pretty much simple programming with rudimentary if statements. Let me know if I can help you some more.

Upvotes: 0

Related Questions