+ Reply to Thread
Results 1 to 11 of 11

Can I create new rows/columns in a master sheet by entering information into a data sheet?

  1. #1
    Registered User
    Join Date
    08-13-2013
    Location
    New York
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    10

    Can I create new rows/columns in a master sheet by entering information into a data sheet?

    Hello,

    I'm new here, and not exactly sure how to search the forum for the information I'm looking for... I've searched but not found what I need, though that might just mean I don't know the right terms to use, so I apologize if I'm asking a repetitive question.

    I work for a creative company that hires mostly freelance artists and we are working on doing better tracking of the time we bid on a job vs the actual time worked. I am creating a spreadsheet to track bid hours and actual hours and display that information on a calendar sheet so we can track all of that information day-by-day and week-by-week.

    Right now, I have it set up so that as I create a new project, I have to update the calendar sheet with additional rows and the formulas contained in each cell by hand each time a new project rolls in (by copying and pasting the rows/formulas from previously created rows). I would very much like to automate that process, as it's time consuming and making sure that the new formulas are referencing the correct cells in the data sheet has to be done cell-by-cell.

    Is there a way to add a new project and have the calendar update with new rows and correct formulas automatically?

    Many thanks!

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Can I create new rows/columns in a master sheet by entering information into a data sh

    Hello Henry and welcome to the Forum!

    It would be rather difficult to make any suggestion without actually seeing what your project looks like. If you could upload a sample with fictional data, we may be able assist you.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    08-13-2013
    Location
    New York
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    10

    Re: Can I create new rows/columns in a master sheet by entering information into a data sh

    That would be very helpful! I will work on fictionalizing a sample for upload, though I don't see an upload link... where do I find that?

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Can I create new rows/columns in a master sheet by entering information into a data sh

    You have to click on 'Go Advanced' button and once there click on a paperclip.

  5. #5
    Registered User
    Join Date
    08-13-2013
    Location
    New York
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    10

    Re: Can I create new rows/columns in a master sheet by entering information into a data sh

    OK, here we go.

    As you'll see, I'm still working on the spreadsheet; I'm trying to dial things in before expanding it to include everything we need.

    I will need to add additional artists as they come and go, as well as add new projects.

    I built much of it using the subtotal function 109 so I could hide project/artists whenever I want to, to try and keep things from getting too cluttered.

    Looking at it, I think it's probably overly bulky and there are a number of formulas that could be used to eliminate a lot of the current formula mess but right now, my goal is to find a way to automate the addition of artists and shows so that someone else can use the spreadsheet without me needing to teach them the ins and outs of everything.

    If you see a way to revise the sheet radically to make it better/easier I am definitely all ears. I have no problem starting from the ground up if it means a better end result.

    Thank you!BidActualHoursCalculator_SAMPLE.xlsx

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Can I create new rows/columns in a master sheet by entering information into a data sh

    I have created a Data Validation list for the employees and added it to the Calendar sheet. Where you can now select them from the list. All need to do to maintain is the list on the Employees tab. I wasn't sure about the Admins, whether they should be together with the employees or not.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-13-2013
    Location
    New York
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    10

    Re: Can I create new rows/columns in a master sheet by entering information into a data sh

    This is a very cool addition... but it changing the Artist on the calendar page doesn't update the information in the Weekly Payroll Totals column (F). And, as we plan on continuing to add weeks in columns continuing into the future to the right of what is currently there, I would need that to update for each new week. Can that be done?

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Can I create new rows/columns in a master sheet by entering information into a data sh

    If you will keep inserting/adding columns, you will have to keep changing formulas and that's a pain. I think the process of how data is stored makes a huge difference when you need to retrieve it.
    Last edited by AlKey; 08-13-2013 at 07:15 PM.

  9. #9
    Registered User
    Join Date
    08-13-2013
    Location
    New York
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    10

    Re: Can I create new rows/columns in a master sheet by entering information into a data sh

    Yes, right now the plan is to keep adding columns. Is there another way of storing data you can suggest that would make retrieving it better/more efficient?

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Can I create new rows/columns in a master sheet by entering information into a data sh

    Typically, if you look at any database, you would see that all data stored in two-dimensional format.
    You should have only rows and columns where each column carries specific information. Thus, information located in such setting will allow you successfully utilize formulas to retrieve necessary data for reporting purposes. One should never confuse how the data stored and how it is presented.

  11. #11
    Registered User
    Join Date
    08-13-2013
    Location
    New York
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    10

    Re: Can I create new rows/columns in a master sheet by entering information into a data sh

    So, when you say "two-dimensional", what do you mean by that?

    Also, I'm not sure what you are referring to when you say "One should never confuse how the data stored and how it is presented." Is there something in my spreadsheet that is confusing storing vs presenting? If so, what is it and how can I change it?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. copy data from multiple sheet to master sheet with specific columns only
    By sinha.riteshabap in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2013, 06:59 AM
  2. Replies: 0
    Last Post: 04-16-2013, 07:29 AM
  3. How to create Macro For 100 sheet data into one master sheet
    By datta9381 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-24-2012, 04:59 AM
  4. [SOLVED] Create new sheets based on template from master sheet data/information?
    By jlaurnm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-13-2012, 08:09 PM
  5. create sheets' data from master sheet
    By ShadowRider in forum Excel General
    Replies: 9
    Last Post: 10-17-2010, 03:23 AM

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