+ Reply to Thread
Results 1 to 9 of 9

Multisheet Formula while Skipping Rows and Autofill

  1. #1
    Registered User
    Join Date
    09-19-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Multisheet Formula while Skipping Rows and Autofill

    Hello everyone,

    I've been struggling to figure this out for a while and I'm hoping someone out there knows the answer.

    I have a workbook that I use to keep track of monthly bills. Each billing company has it's own sheet with relavant information including the bill total. Then I have one main sheet which lists just the totals of each month. I've created and attached a test sheet of what I'm trying to accomplish.

    In this book I've created two sheets "Totals" and "Gas-Electric". In the "Totals" sheet I just want the totals from the "Gas-Electric" sheet. Typically in the "Totals" sheet Cell B3 the formula ='Gas-Electric'!E4 would be just fine but in the "Gas-Electric" sheet each month the total will be 3 rows lower. When you autofill it doesn't know to skip 3 cells. So Cell B4 in the "Totals" sheet should read ='Gas-Electric'!E7. B5 = E10, B6 = E13 and so on. I've color coated the cells I want equal to help out anyone trying to help. I'm looking for a way to create a formula that is able to autofill this in the Totals sheet.

    I've tried the solution in this thread http://www.excelforum.com/excel-gene...ping-rows.html and haven't had any luck. I know it can be done with index and match but I'm looking for an easier way.

    Thank you in advance for your help.
    Attached Files Attached Files

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Multisheet Formula while Skipping Rows and Autofill

    ..I know it can be done with index and match but I'm looking for an easier way.
    Easier than this..?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    09-19-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Multisheet Formula while Skipping Rows and Autofill

    You're right index and match would be easy but I've had this spreadsheet in operation for 2 years. Unfortunatley back then I was ok with manually manipulating the cells and didn't set it up in the brightest way. I would need to rearrange a lot of data or cut ties with the current sheet and start over in order for index and match to work. The sheet I provided was just a quick small scale of how I currently have it set up. When you break it down, all I want is autofill to skip 3 cells place that data and repeat. It seems simple.

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Multisheet Formula while Skipping Rows and Autofill

    ...The sheet I provided was just a quick small scale of how I currently have it set up.
    Unfortunnately, if i can not see how your real data looks like, then how can i give you a suggestion?

    ...It seems simple.
    Simple for whom?

  5. #5
    Registered User
    Join Date
    09-19-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Multisheet Formula while Skipping Rows and Autofill

    The sheet in the example is identical in every way except instead of the month spelled out in the "Gas-Electric" sheet it's the date the bill was issued, ex. 9/18/12. That is why index and match wouldn't work. The two sheets don't have the same wording because in the "Totals" sheet it doesn't matter when the bill was issued just the month and year. I've re-attached the file with the correct updates. I'm currently on business with some downtime and don't have the exact sheet with me. This revised sheet should be exact.
    Attached Files Attached Files

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Multisheet Formula while Skipping Rows and Autofill

    Even in this case, we can use INDEX & MATCH in Array formula.

    Take a look to the example.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-19-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Multisheet Formula while Skipping Rows and Autofill

    It's getting closer to what I'm looking for. When adding new data to the "Gas-Electric" sheet below the existing data the information is not relayed to the "Totals page". If I copy the existing data and insert it above the last months data it will work. Is there a way I can keep adding data without having to copy and insert?

  8. #8
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Multisheet Formula while Skipping Rows and Autofill

    You need to adjust the renge in my Array Formula.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-19-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Multisheet Formula while Skipping Rows and Autofill

    I wasn't changing enough of the formula. I'll look at the sheet more indepth tonight. I'm trying to mulitask on this and work but it looks like this could be our answer.

+ 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