Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Adding Numbers depending on preceding column letter

0

I keep fantasy football spreadsheets to track players scores, I would like to have it add starters scores to one spot and bench scores to another slot. Spreadsheet is formatted as follows:

Name      Team     Start/Bench     Wk1     S/B      Wk2    etc.

Under the S/B column I put an S for start and B for bench, at the bottom of the page I have a block for Starters combined scores and one for Bench combined scores.

Is there a way that if there is an S in cell E4 that it adds the score in F4 to cell F28, if there is a B in cell E5 that it adds the score in cell F5 to cell F29 and so on.

Any thoughts from all you experts out there, thanks for any assistance you can provide.

excelformulas-functions
MS Office Version: 2016
posted by: Starbuk7 on Wed Oct 4, 2017 at 4:10 pm
Answer
Discuss

Discussion

More info I guess:
Name      Team     S     Wk1     S      Wk2
Player      LA         S     20        B       5
Player      NY         B     10       S       15

This is how the entries look for the team, 16 entries per WK, 9 Starters and 7 Bench. I would like to know if there is a way that if there is an S in the S column, to have those scores add at the bottom of the column in one cell, and if there is a B in the S column to have those scores add to a different cell at the bottom of the WK column. Hope that makes more sense.
Starbuk7 Oct 5, '17 at 9:04 am
I have answered that question. Please look at my answer below.
Variatus (rep: 4889) Oct 5, '17 at 9:32 am
Add to Discussion

Answers

0

Gee, really be nice to see you example so we can upload it and really understand it.  I dont understand what cell F28 has to do with F4.

Please submit sample with expected results

posted by: queue (rep: 467) on Wed Oct 4, 2017 at 4:48 pm
Discuss

Discussion

Definitely agreed it needs a sample file! Though I think your comment might work better in the Discuss section next time, just to keep things a little tidier.
don (rep: 1989) Oct 4, '17 at 4:56 pm
  Ok.  I didn't know what the Discuss section was used for.  How does this show up?  I was going to attempt to answer the question by using Named Ranges or Lookup values but without a firm understanding of the question, i was stumped.
queue (rep: 467) Oct 4, '17 at 5:58 pm
It showed up in the "Answer" section and this in the discuss section. I know its confusing but what I'm trying to do is to keep the answers to a question separate from statements that clarify the question, which should be in the Discuss section.
don (rep: 1989) Oct 5, '17 at 3:15 am
Add to Discussion
0

No worksheet function can change anything on a worksheet except the display in the cell in which it resides. Therefore, if you wish to have cell F28 include the number written in E4 conditional upon the letter in E4, you must describe the sum to be shown in F24 - all of that sum, not just one part of it coming from F4. Lacking such a description it is impossible to construct a formula, to reside in F24, which will change the display of F24 whenever E4:F4 changes.

However, your question was whether it is possible to modify F24 whenever E4:F4 change. Yes. That is possible, too. However, only user action can change any cell on the worksheet, or VBA. Code could be written to modify F24 in a certain way whenever certain changes occur in E4:F4.

As you see, different solutions are required just depending upon whether you are looking at F24 or from F24. I sense that creating a VBA project isn't what you set out to do. Therefore I suggest you describe the total you wish to display in F24 and look for a worksheet function to create that total.

posted by: Variatus (rep: 4889) on Wed Oct 4, 2017 at 9:29 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login