+ Reply to Thread
Results 1 to 15 of 15

Pasting formulas in specific columns

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Pasting formulas in specific columns

    I apologize in advance for the wordiness and # of macros posted. This isn't an easy problem for me.

    I have a worksheet used to build a forecast, with anywhere from ten to 80 columns. The columns are divided into series by month and type, so for instance I may have 4 columns titled "Base Projected April", "Base Projected May", Base Projected June", etc., followed by a single column called "Authorized", followed by 4 more named "Adjusted April", "Adjusted May", etc. The # of columns depends on when in the fiscal year (September through August) I'm doing the forecast. For instance, if I'm doing one in October, it would be for October through August (11 months) whereas if I did it in July it would only be for 2 months. After the initial population of the forecast, that number of forecasted columns would need to be replicated with new headings, and a different function in each series depending on what was needed. That's where it's getting complicated.

    I have a few macros that can do parts of what I need to do, but I don't know how to string them together to do all of what I need.

    The first one adds the number of columns needed for the first series
    Please Login or Register  to view this content.
    The next one adds the formulas I need in that first series:
    Please Login or Register  to view this content.
    Now I have an improved version of the first macro, which will add all of my column headings at one shot.
    Please Login or Register  to view this content.
    The problem with this one is that if I use it, my macro for completing the first series fills in the same formulas under all of the columns. That's not a huge deal, as I can overwrite what's in the columns, but I need to know how to paste my correct formulas under those column headings.

    I've copied and tried to adapt another macro I found on the internet, which is supposed to paste formulas based on the column heading.
    Please Login or Register  to view this content.
    However, I need it to run in conjunction with my other macros, and it needs to be able to look at PART of the column heading to determine the formula. For instance, most of the columns will have a month name, but I won't know in advance what months will be used, so can only hard code part of the name.

    I realize this is very confusing. I've attached my worksheet in the hopes that it will make clearer what I'm trying to accomplish. I've put samples of each of the formulas in the top row of the example, and started formatting it the way I want it to appear. Putting the boxes around each section also seems challenging to me.

    Any help I can get with this is greatly appreciated. AFTER this (probably another post) I'll need how to do vlookups later to some of these columns. How I'll do that without knowing what the column is called seems tough from here. Maybe by then I'll figure it out.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Pasting formulas in specific columns, with a twist

    Hi

    All I've done is to include the formula generation at the same time as you generate your headings.

    See if that gets you going.

    Please Login or Register  to view this content.
    rylo

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pasting formulas in specific columns, with a twist

    Rylo, you're a GodSend. You've indeed got me well started. I would have replied earlier but I had to set up other stuff on my sheet to best use what you've provided.

    Now, I still need help with the formatting - I want to draw a box around each series of months. Any ideas?

  4. #4
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pasting formulas in specific columns, with a twist

    Okay, I'm stumped again. I thought there was enough of this macro to enable me to go forward, but I'm stuck on two areas. The first is how to plug in my formulas. Would I basically plug in these lines
    Please Login or Register  to view this content.
    directly under the "Loop Until j = 9" lines?


    The harder of the two areas seems to be the formulas. For instance, in my "Adjusted" columns, the number needs to be the "Base Projected" if that equals or is less than the "Authorized". However, if "Base Projected" is greater then the "Authorized" the value needs to be the last amount amount reported in our History months. I've got the formula I need, but don't know how to adapt it to this situation. The formula (which works in the example attached) is
    Please Login or Register  to view this content.
    My problem is that I, K, and G are all shifting targets, so I don't know how to structure my formula to catch them.

    Since the second problem is more of a formula issue, I'll create a new post in the Formula's section for that one, but feel free to post an answer here if you've got one.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Pasting formulas in specific columns, with a twist

    Hi

    1) Yep, if you just put in the relevant formula blocks after each of the heading components, then should give you the position statements.

    2) While they are "shifting", they do have an anchor point. The last reported amount will always (I think) be the column immediately before the column with the Grand Total. So you can always determine this column. The first of the base projected will always be the column immediately after the grand total, and as you are working in the column for the first of the adjusted, you should know where that is by your heading / formula entry position. So build a variable that will capture the position of Grand Total, then use offset to get the columns either side.


    Please Login or Register  to view this content.
    In the above code, I've added a new variable GTCol, filled it (you could also use it instead of the opening loop), and in the area prefixed with *** here ***, I've put in the first of the formulas as per the above suggestion.

    HTH

    rylo
    Last edited by rylo; 10-03-2010 at 07:56 PM. Reason: added some sample code

  6. #6
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pasting formulas in specific columns, with a twist

    Rylo,

    Thanks so much for all your help. I'm sorry it's taken me so long to respond, but I've been kept busy with other projects, and am just now getting back to this one. I've tried your code, and am making good progress, but just got stuck again. I'll have to do some more adjusting to the formula, becuase the "Adjusted" column to the right of the "Authorized FTEs" is pulling up the column immediately to the left of the "Authorized FTE" column, instead of the column immediately following the "Grand Total" column. As well, the other adjusted columns to the right refer back to the "Authorized" and the columns to the right.

    Following this, when I do my "Adjusted Projections Exceeding Allocation" column I need to refer back to the "Adjusted (month)"columns just created. I'm not sure how to configure the GTCOL variable to reference back to those columns. I'm leaving for the day, so will try to fool with it tomorrow, but would appreciate some more direction if possible. Here's my amended code. The area in question is offset by ",,,Trouble Here,,,,". Thanks in advance for any help you can provide me.
    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Pasting formulas in specific columns, with a twist

    Hi

    Been so long I've forgotten what this is all doing.

    Is this the right formula?

    Please Login or Register  to view this content.
    If not then set up a new example workbook, include the code that generates the output, and show exactly what formula should go where for the example structure. Maybe add another sheet showing exactly what it should look like, and I'll have another look.

    rylo

  8. #8
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pasting formulas in specific columns, with a twist

    Rylo,

    Again, I'm sorry I've been away so long. I'm constantly switching from one project to another, so usually can't stay long on any one.

    Here's the background:
    What we're trying to do in this scenario is insert various formulas to derive different results for a projection. We begin (1) by picking history months; depending on which months we choose, we do projections (2) (Columns I:K in the attached sample) for between 1 and 12 months, to complete the fiscal year. For the series of months we (3) take the projection and compare it to our authorized list (ColumnL in the attached sample), then (4) adjust up or down to comply with the authorized or with the last reported (columns M:0), then (5) see if these adjustments exceed the allocation (column P). Next (6) is a series by month for Budget Analyst corrections ("First Altered", Q:S)., then (7) a column for justification (T), then (8) a series of columns for manager corrections ("final Adjusted", U:W), then last (9)a "Final Adjusted Projection Exceeding Allocation" column (X), basically a clone of column P.

    So far I've got steps 1-4 done. I'm working on 5, but am having trouble with the formula, trying to work it out by your example. Your help has been invaluable, but I'm having real trouble with this one, and see the empty fields coming giving me more trouble.

    In the sample I've placed the formulas that need to go in the code. To run the code you'll need to have the ESS and the Staffing documents open; code is in the staffing. You can delete columns I through X on the Worktab spreadsheet; the macro will recreate them. I've put the formulas needed in the spaces above the column headings and in the formulas worksheet.

    Thanks again for all your help.

    John
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Pasting formulas in specific columns, with a twist

    John

    This is based on the 2 example files, and the formulas. I've tried to make it dynamic enough to cover more or less columns. Here's hoping.

    Please Login or Register  to view this content.
    rylo

  10. #10
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pasting formulas in specific columns

    Rylo, you are a wonder! I've been testing your code, trying to break it, and it seems to do the trick every time. I'm going to be studying this for months to fully understand how it works. Thank you so much!

    I'll mark this one solved, but I'll probably be back soon with another post. I have so much to learn!

  11. #11
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pasting formulas in specific columns

    Hey, how do I mark it SOLVED when I can't edit my first post?

  12. #12
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pasting formulas in specific columns

    Oh Man, I'm in trouble now! Rylo, are you still there?

    I marked one of my calculated fields as an Open field, not needing a formula, in my sample, and just realized I need a formula. I'm trying to piece one together from what you've provided, but so far it's escaping me.

    The columns I need should add together the Methodology Adjustments (columns M,N,O in the new attached sample) and the RBA Recommended Adjustments (Q,R,S) to get a Final Projection.

    Here's my code as it now stands. I've done a bit of work formatting, but still not done (I'm slow at this stuff, still trying to learn my way). I'd appreciate any steerage toward the right direction.

    Thanks,
    John

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jomili; 10-22-2010 at 04:30 PM.

  13. #13
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Pasting formulas in specific columns

    Here goes.

    Please Login or Register  to view this content.
    rylo
    Last edited by rylo; 10-24-2010 at 08:07 PM. Reason: bit of a tidy up

  14. #14
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pasting formulas in specific columns

    Rylo,

    I know it's been forever, and I hope you're still out there. I've got it 99.9% working. The only problem remaining is in the Justification area. Here's the code from that section (full code is at bottom of post):
    Please Login or Register  to view this content.
    See the attached example. The problem is that the formula in AH only looks for values in AA:AC, instead of AA:AG. I believe the reason is that currently AA:AG have no values. I think if I inserted values the formula as it exists would work, but I'd prefer to instead change the formula, but don't know how to configure it. Can you help?

    Full code is below.
    Please Login or Register  to view this content.

  15. #15
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pasting formulas in specific columns

    Okay, I've got it all solved. It appears to be working correctly, so I thought I'd post my final code before marking this thread as Solved.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1