Creating new columns based on inputs

I'm familiar with Excel's basic functions and have used it many times for calculations and whatnot, but what I'm currently trying to do is beyond me - I come here for help.

This is for my fantasy football league, so perhaps being familiar with fantasy football will be of help. It's somewhat complicated, so I'm just going to explain what I would like to do:

Upon opening the Excel file, there will be a few questions. The first will ask what type of draft, with just two choices, A or B. Below that, it will have inputs asking how many teams, how many players per team, and, if option B was chosen, how many dollars per team. After answering the questions, there should be a button that will "generate" what I'm about to describe.

Choosing option A or B will result in two completely different sheets:

-Option A will have X columns for the number of teams entered, and Y rows for the number of players per team. If possible, something will pop up allowing the user to input each team name.

-Option B will be a bit more complicated. I have a template of what one column will be for each team, and would need to have that repeated for the number of teams entered. Additionally, the number of players per team and dollars per team will need to be placed into an existing formula that will perform certain calculations. And, if possible, the number of players per team will alter the column template if the number is over a pre-defined number. And, like the previous option, it would present a pop up allowing the user to input each team name.

I apologize for the length of this question, and while it is too extensive to ask someone here to do, I would like to know what I can expect if I try to achieve this. Will I need to know programming code? Can you point me to some articles that will help me in terms of turning an inputted number into a spreadsheet with that number of columns after hitting a "submit" button?

Any help would be greatly appreciated. Thanks to anyone that's still reading this post. ;-)

Hi and welcome to the forum.

The first thing you might want to try is search this site for posts about fantasy leagues, they seem to crop up fairly regularly. Similarly I'm sure that if you google 'Excel Fantasy' you'll probably get some ideas.

Anything you can do manually you can also do with programming code, so you don't necessarily need code, but it does speed things up immensely and can avoid input errors.

It would make eminent sense to have a VBA user form with input boxes for capturing team names, player names etc, but you'll certainly need to do some reading about VBA/macro code since you can't capture that sort of stuff with the macro recorder.

Option B is not necessarily more complicated. The formula you write should use cell variables for things like the number of players and dollars per team, and these cells themselves can just be formulae which do counts, sums or whatever.

As to the last point, (if I've understood it), the following might help. If you hold the number of columns you want to add in a cell and name the cell "Teams", and then name the column of cells that you want to replicate "ColData", the following macro would copy this column accordingly.

Please Login or Register  to view this content.

Originally Posted by Richard Buttrey

Hi and welcome to the forum.

The first thing you might want to try is search this site for posts about fantasy leagues, they seem to crop up fairly regularly. Similarly I'm sure that if you google 'Excel Fantasy' you'll probably get some ideas.

Anything you can do manually you can also do with programming code, so you don't necessarily need code, but it does speed things up immensely and can avoid input errors.

It would make eminent sense to have a VBA user form with input boxes for capturing team names, player names etc, but you'll certainly need to do some reading about VBA/macro code since you can't capture that sort of stuff with the macro recorder.

Option B is not necessarily more complicated. The formula you write should use cell variables for things like the number of players and dollars per team, and these cells themselves can just be formulae which do counts, sums or whatever.

As to the last point, (if I've understood it), the following might help. If you hold the number of columns you want to add in a cell and name the cell "Teams", and then name the column of cells that you want to replicate "ColData", the following macro would copy this column accordingly.

Please Login or Register  to view this content.

Richard, thank you very much for taking your time to help me.

The first thing I did before posting was search for topics on fantasy football, and the posts I found did not really deal with something like this. They were more focused on rankings or standings for a league.

And I agree - I'll definitely have to do some reading about macros because just trying to do something with the code you provided proved to be confusing. If you don't mind getting a bit more detailed, what exactly do I do with that code? I opened up the VB editor, clicked 'View Code', and entered the code you wrote. Then I named a cell 'Teams' and a column 'ColData', but after that, I had no idea what to do in order to actually make the columns replicate.

Thanks again for your help.

Hi,

Can we just confirm that you've given a range name to the cell which holds the number of teams, and a name to a range of formulae/data in a particular column? i.e. you've used 'Insert Name Define', or entered the names directly into the name box in the formula bar.

If you've done that and entered the code in the VBE, then you should be able to run the macro by choosing the macro name 'RepeatColumns' from the 'Tools Macro Macros' menu, selecting and then running it.

HTH

Richard,

I had done everything you said...up to actually running the macro! I figured that out, and actually created a button to click that runs the macro. So far, so good!

I tried going back into the code and editing it so the replicated columns would end up in a desired cell in Sheet 2, but no luck - the VBE gave me parameter errors.

Thanks for sticking with me through this - I already feel like I've learned a bit more about macros just by experimenting with your original code.

Hi,

I'm attaching an example workbook which should show you what I was getting at. There are two macros, one to copy the original column range to the same sheet and the other to copy it to another sheet.

I suspect your problem may lie with not explicitly including the name of the sheet to where you want the copy to take place. In this example since I've given a range name to the cell on sheet 2, even this is not necessary, and another good reason for naming ranges rather than hard coding them. You can see the two macros are exactly the same - apart from the named cells.

HTH

Richard,

Thanks again for your help. I've been reading about VB and toying around with Excel for a while. I've attached where I'm currently at, as I've made the move from just replicating one column to a two-column section featuring a merged column as the header.

Naturally, it has led to some problems, from the formatting to actually replicating the number desired.

Feel free to check out my file - I'm unsure what to do in order to go on from here.

Thanks!

Hi,

I'm attaching an updated workbook to cater for your changed layout. Hopefully you should be able to follow the logic in the macro.

Just one tip. Merging cells, as you did with the header in J1:K1, causes more problems than it's worth, and only means you have to complicate code to get round the problem. (Excel doesn't like it when you try to copy unmerged cells into already merged cells). I learned this lesson many years ago.

It's always preferable in my opinion to use the Format Cells 'Center across Selection' option. This achieves exactly the same effect, but without the problems associated with merges. Why MS ever bothered with merges when centering across a selection is available has always puzzled me.

HTH