Roger’s Excel Thread - Page 5 - Antsmarching.org Forums - Dave Matthews Band Discussion
Old 02-10-2017, 01:17 PM   #121
daveshookme
Altuve wore a wire
 
daveshookme's Avatar
 
Join Date: Jun 2009
Location: Supreme Courtyard by Marriott
Posts: 62,665

Shows Seen: 54

DMB Hub Stubs: 20

My Tour Central Stats

Re: Excel Help

Quote:
Originally Posted by rconverse View Post
I just sent you the file back with my notes. I think your workbook could use a couple of small changes and then you'll be able to do what you want easily. If you want to PM me your password so I can edit it, I can make the changes and then send it back the way I think it would work best.
yeah sure thing.

there is no pw.
__________________
Tape Cover Artwork
daveshookme is offline   Reply With Quote

  • Want to hide all ads on Ants? Click here
  • Old 02-15-2017, 06:14 PM   #122
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    So I'm using =offset to name a named range dynamically. When I evaluate the formula using the formula evaluator tool, it comes back with the correct range of cells. But when I plug the name into another formula, I get a #Ref error.

    WTF gives.
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 02-16-2017, 06:51 PM   #123
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by daveshookme View Post
    So I'm using =offset to name a named range dynamically. When I evaluate the formula using the formula evaluator tool, it comes back with the correct range of cells. But when I plug the name into another formula, I get a #Ref error.

    WTF gives.
    What formula/function are you using? If it's a VLOOKUP and you're sure your named range is good then are you trying to lookup more columns than your range contains? That would produce that error.
    rconverse is offline   Reply With Quote
    Old 02-16-2017, 07:00 PM   #124
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    What formula/function are you using? If it's a VLOOKUP and you're sure your named range is good then are you trying to lookup more columns than your range contains? That would produce that error.


    No it was a sum function. I decided it wasn't going to do what I actually needed it to do anyway so I abandoned it. I may come back to it though.
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 02-16-2017, 07:01 PM   #125
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    What formula/function are you using? If it's a VLOOKUP and you're sure your named range is good then are you trying to lookup more columns than your range contains? That would produce that error.


    No it was a sum function. I decided it wasn't going to do what I actually needed it to do anyway so I abandoned it. I may come back to it though.
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 02-17-2017, 08:40 AM   #126
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by daveshookme View Post
    No it was a sum function. I decided it wasn't going to do what I actually needed it to do anyway so I abandoned it. I may come back to it though.
    What were you trying to do?
    rconverse is offline   Reply With Quote
    Old 02-17-2017, 08:51 AM   #127
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    What were you trying to do?


    If you still have the file I sent you I was defining JanExpenses (actually creating a new name called JanExpensesNew as a trial so I didn't fuck anything up while I was working out the kinks...) as all expense transactions in the month of January straight from the checkbook sheet, instead of copying all that info over to the misc sheet and then in monthly summary summing JanExpensesNew.
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 02-17-2017, 10:07 AM   #128
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by daveshookme View Post
    If you still have the file I sent you I was defining JanExpenses (actually creating a new name called JanExpensesNew as a trial so I didn't fuck anything up while I was working out the kinks...) as all expense transactions in the month of January straight from the checkbook sheet, instead of copying all that info over to the misc sheet and then in monthly summary summing JanExpensesNew.
    There's really no need for a new named range.

    In the spreadsheet that you sent me, you have the following formula:

    Quote:
    =IF(TODAY()>=A2,SUM(JanExpenses),"")
    You can use this formula. The only thing you'll need to change is in bold.

    Quote:
    =SUMIFS(CheckBook!$D:$D, CheckBook!$A:$A, ">=" & 'Monthly Summary'!A2, CheckBook!$A:$A,"<=" & EOMONTH( 'Monthly Summary'!A2, 0))
    Feb will be H2, Mar will be O2, etc...
    rconverse is offline   Reply With Quote
    Old 02-17-2017, 10:10 AM   #129
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    There's really no need for a new named range.

    In the spreadsheet that you sent me, you have the following formula:



    You can use this formula. The only thing you'll need to change is in bold.



    Feb will be H2, Mar will be O2, etc...
    No there really is no need for it. One of the joys in this project is it's just a learning thing for me, overcomplicating things just to learn new functions, logic my way through new problems/find alternative or easier ways to solve something, etc.

    I was learning how to dynamically name ranges using the offset function. It's not a huge deal, I figured it that part out. It just wasn't working with the sum. No big deal.
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 02-17-2017, 10:11 AM   #130
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    You can use the same formula for income, just change the sum range to E:E..


    =SUMIFS(CheckBook!$E:$E, CheckBook!$A:$A, ">=" & 'Monthly Summary'!A2, CheckBook!$A:$A,"<=" & EOMONTH( 'Monthly Summary'!A2, 0))
    rconverse is offline   Reply With Quote
    Old 02-17-2017, 10:15 AM   #131
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by daveshookme View Post
    No there really is no need for it. One of the joys in this project is it's just a learning thing for me, overcomplicating things just to learn new functions, logic my way through new problems/find alternative or easier ways to solve something, etc.

    I was learning how to dynamically name ranges using the offset function. It's not a huge deal, I figured it that part out. It just wasn't working with the sum. No big deal.
    I mostly use dynamic ranges when using pivot tables. They're really great if you have no empty columns across the top row and at least one row with no gaps in data. Otherwise, at least in my experience, they begin to get very tricky to create. By utilizing the SUMIFS function, you can produce the same result and save yourself the time of creating a bunch of named ranges.
    rconverse is offline   Reply With Quote
    Old 02-17-2017, 10:19 AM   #132
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    You can use the same formula for income, just change the sum range to E:E..


    =SUMIFS(CheckBook!$E:$E, CheckBook!$A:$A, ">=" & 'Monthly Summary'!A2, CheckBook!$A:$A,"<=" & EOMONTH( 'Monthly Summary'!A2, 0))
    Yeah I may end up using a variation of these formulas eventually. I'm going to change the data validation to all sources of expenditures (right now it's all from my one checking account. i'm going to take out the different methods of check/transfer/etc and replace with my credit card, student loan, etc).

    It's all just a work in progress.
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 02-17-2017, 10:20 AM   #133
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    I mostly use dynamic ranges when using pivot tables. They're really great if you have no empty columns across the top row and at least one row with no gaps in data. Otherwise, at least in my experience, they begin to get very tricky to create. By utilizing the SUMIFS function, you can produce the same result and save yourself the time of creating a bunch of named ranges.
    Yeah, I've got a lot of them. One of the things on my to do list at work this weekend is try to downsize and simplify the workbook.
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 02-17-2017, 10:23 AM   #134
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Yeah, for such a little workbook, you are not shorting it of named ranges.
    rconverse is offline   Reply With Quote
    Old 02-17-2017, 10:28 AM   #135
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    Yeah, for such a little workbook, you are not shorting it of named ranges.
    like I said, it's equal part functional and educational. Named Ranges are easier for me to remember than the specific ranges. Plus, if I can have them expand/contract with the amount of data that's easier yet and less work for me. I'll get to it this weekend.
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 05-10-2017, 02:06 PM   #136
    DIDriveOut2SPAC
    American Aquarium Drnkr
     
    DIDriveOut2SPAC's Avatar
     
    Join Date: Aug 2006
    Location: Boston, MA
    Posts: 26,291

    Shows Seen: 35

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    is their a quick way to alter the formatting of specific information in a column? For instance adding spaces as such:



    Before:
    12345-06
    12345-07

    After:
    12 34 56-06
    12 34 56-07


    Greatly appreciated if you can help out!
    __________________
    Dan|
    DIDriveOut2SPAC is offline   Reply With Quote
    Old 05-10-2017, 03:08 PM   #137
    BTBaboon
     
    BTBaboon's Avatar
     
    Join Date: Jul 2015
    Posts: 51,333

    Shows Seen: 31

    DMB Hub Stubs: 10

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by DIDriveOut2SPAC View Post
    is their a quick way to alter the formatting of specific information in a column? For instance adding spaces as such:



    Before:
    12345-06
    12345-07

    After:
    12 34 56-06
    12 34 56-07


    Greatly appreciated if you can help out!
    Is the data all uniform as in your example (5 characters, a dash, followed by 2 more)?
    BTBaboon is offline   Reply With Quote
    Old 05-10-2017, 03:39 PM   #138
    DIDriveOut2SPAC
    American Aquarium Drnkr
     
    DIDriveOut2SPAC's Avatar
     
    Join Date: Aug 2006
    Location: Boston, MA
    Posts: 26,291

    Shows Seen: 35

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by BTBaboon View Post
    Is the data all uniform as in your example (5 characters, a dash, followed by 2 more)?
    Yes for the most part.

    I have some in that format with an additional -R1, -R2 for resubmittals of the same information, but they are so few I can do those ones manually.
    __________________
    Dan|
    DIDriveOut2SPAC is offline   Reply With Quote
    Old 05-10-2017, 03:54 PM   #139
    mandy18
     
    Join Date: Feb 2002
    Location: Pittsburgh, PA
    Posts: 8,970

    Shows Seen: 55

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    If I have a spreadsheet that I have locked all cells, except for one column, and protected the sheet with a password, on the surface, there's no way for a user to sort, correct (without the password)?

    I know with a few additional steps, I can allow users to edit a range, but assuming I didn't do that (or use macros), there's no way a user can sort my locked sheet, right?

    I'm arguing this with a so-call excel expert...
    mandy18 is offline   Reply With Quote
    Old 05-10-2017, 04:02 PM   #140
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by mandy18 View Post
    If I have a spreadsheet that I have locked all cells, except for one column, and protected the sheet with a password, on the surface, there's no way for a user to sort, correct (without the password)?



    I know with a few additional steps, I can allow users to edit a range, but assuming I didn't do that (or use macros), there's no way a user can sort my locked sheet, right?



    I'm arguing this with a so-call excel expert...


    I don't believe so, although I don't know for certain.
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 05-10-2017, 04:35 PM   #141
    BTBaboon
     
    BTBaboon's Avatar
     
    Join Date: Jul 2015
    Posts: 51,333

    Shows Seen: 31

    DMB Hub Stubs: 10

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by DIDriveOut2SPAC View Post
    Yes for the most part.

    I have some in that format with an additional -R1, -R2 for resubmittals of the same information, but they are so few I can do those ones manually.
    Okay, so what I'd do (for this example say your column of data formatted that way starts in A2) is in the adjacent column/cell type:

    =LEFT(A2,2)&" "&MID(A2,3,2)&" "&MID(A2,5,1)&"6"&RIGHT(A2,3)

    See link below for screenshot of excel to see if that's what you'd like. You'll have to manually edit the formula for those exceptions; changing the last '3' in the formula to 6 to include the extra dash and 2 characters

    http://i.imgur.com/oKDNBDa.jpg

    Let me know if that works for you
    BTBaboon is offline   Reply With Quote
    Old 05-10-2017, 04:39 PM   #142
    Arby
    We Have The Meats
     
    Arby's Avatar
     
    Join Date: Jul 2004
    Posts: 89,484

    Shows Seen: 24

    DMB Hub Stubs: 12

    My Tour Central Stats

    Re: Excel Help

    I need to look into the cell lock thing.

    So...there is a way to lock all cells/formulas so that no changes can be made except to select non-locked cells?

    We have a lot of people aggregating data from different teams into one place. Nothing bad has happened yet, but it feels like a matter of time
    __________________
    -Arby
    Arby is offline   Reply With Quote
    Old 05-10-2017, 04:52 PM   #143
    BTBaboon
     
    BTBaboon's Avatar
     
    Join Date: Jul 2015
    Posts: 51,333

    Shows Seen: 31

    DMB Hub Stubs: 10

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by Arby View Post
    I need to look into the cell lock thing.

    So...there is a way to lock all cells/formulas so that no changes can be made except to select non-locked cells?

    We have a lot of people aggregating data from different teams into one place. Nothing bad has happened yet, but it feels like a matter of time
    You can, and to the bold - yes it is

    https://support.office.com/en-us/art...3-042a5f2cd93a
    BTBaboon is offline   Reply With Quote
    Old 05-10-2017, 05:19 PM   #144
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by DIDriveOut2SPAC View Post
    Yes for the most part.

    I have some in that format with an additional -R1, -R2 for resubmittals of the same information, but they are so few I can do those ones manually.
    Do you mean R1 and R2 as in there are sometimes 1 character after the dash and sometimes 2 characters after the dash?

    If so, why not use the LEN function to check for that?

    =IF(LEN(TRIM(MID(H3, FIND("-", H3, 1)+1, 40)))<2, "Formula for 1 character", "Formula for 2 characters")

    Also, is this just a static 6 that you're adding out of nowhere?

    Quote:
    Before:
    12345-06
    12345-07

    After:
    12 34 56-06
    12 34 56-07
    Where the heck does that come into play?
    rconverse is offline   Reply With Quote
    Old 05-10-2017, 05:20 PM   #145
    Arby
    We Have The Meats
     
    Arby's Avatar
     
    Join Date: Jul 2004
    Posts: 89,484

    Shows Seen: 24

    DMB Hub Stubs: 12

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by BTBaboon View Post
    You can, and to the bold - yes it is

    https://support.office.com/en-us/art...3-042a5f2cd93a
    haha thanks

    Ill be looking into this
    __________________
    -Arby
    Arby is offline   Reply With Quote
    Old 05-10-2017, 05:23 PM   #146
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by mandy18 View Post
    If I have a spreadsheet that I have locked all cells, except for one column, and protected the sheet with a password, on the surface, there's no way for a user to sort, correct (without the password)?

    I know with a few additional steps, I can allow users to edit a range, but assuming I didn't do that (or use macros), there's no way a user can sort my locked sheet, right?

    I'm arguing this with a so-call excel expert...
    Maybe they're smarter than the MS Support website...

    https://support.office.com/en-us/art...3-042a5f2cd93a

    Quote:
    Sort

    Using any commands to sort data (Data tab, Sort & Filter group).

    Users can't sort ranges that contain locked cells on a protected worksheet, regardless of this setting.
    rconverse is offline   Reply With Quote
    Old 05-10-2017, 05:23 PM   #147
    BTBaboon
     
    BTBaboon's Avatar
     
    Join Date: Jul 2015
    Posts: 51,333

    Shows Seen: 31

    DMB Hub Stubs: 10

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    Do you mean R1 and R2 as in there are sometimes 1 character after the dash and sometimes 2 characters after the dash?

    If so, why not use the LEN function to check for that?

    =IF(LEN(TRIM(MID(H3, FIND("-", H3, 1)+1, 40)))<2, "Formula for 1 character", "Formula for 2 characters")

    Also, is this just a static 6 that you're adding out of nowhere?

    Where the heck does that come into play
    ?
    The bold was also super confusing to me too
    BTBaboon is offline   Reply With Quote
    Old 05-10-2017, 05:26 PM   #148
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by Arby View Post
    I need to look into the cell lock thing.

    So...there is a way to lock all cells/formulas so that no changes can be made except to select non-locked cells?

    We have a lot of people aggregating data from different teams into one place. Nothing bad has happened yet, but it feels like a matter of time
    These type spreadsheets are the worst. I'd def recommend to use data validation wherever manual entry is necessary as well as create an error check macro if you're having data appended into workbooks or anything like that.

    Edit: As BTB indicated, it's only a matter of time before that workbook gets jacked up in some manner.
    rconverse is offline   Reply With Quote
    Old 05-10-2017, 05:32 PM   #149
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by BTBaboon View Post
    The bold was also super confusing to me too
    So is that actually needed or was it just him kind of getting a little to crazy with the example?

    Also, if there are some cells that have an additional dash and then the "R1" or "R2", then he doesn't need that long IF(LEN(MID))) nested function. He can just use the LEN of the entire cell, which should make it a little easier and faster.
    rconverse is offline   Reply With Quote
    Old 05-10-2017, 05:35 PM   #150
    Arby
    We Have The Meats
     
    Arby's Avatar
     
    Join Date: Jul 2004
    Posts: 89,484

    Shows Seen: 24

    DMB Hub Stubs: 12

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    These type spreadsheets are the worst. I'd def recommend to use data validation wherever manual entry is necessary as well as create an error check macro if you're having data appended into workbooks or anything like that.

    Edit: As BTB indicated, it's only a matter of time before that workbook gets jacked up in some manner.
    ahh macros. Ive feared I would be subject to them at some point.

    Thats a good call though. I'll look into that as well
    __________________
    -Arby
    Arby is offline   Reply With Quote
    Reply

    Tags
    excel

    Thread Tools
    Display Modes

    Posting Rules
    You may not post new threads
    You may not post replies
    You may not post attachments
    You may not edit your posts

    BB code is On
    Smilies are On
    [IMG] code is Off
    HTML code is Off

    Forum Jump


    Want to hide all ads on Ants? Click here

    All times are GMT -7. The time now is 02:41 PM.


    Powered by vBulletin® Version 3.8.14
    Copyright ©2000 - 2024, vBulletin Solutions Inc.


       
    Site LinksAbout AntsAnts MobileTweet Tweet
    Home
    Ants+
    Tour Central
    Search bar
    RSS Feeds
    About Us
    Contact Us
    The Ants Blog
    Advertise on Ants
    Privacy Policy
    Ants on your cell phone
    iAnts
    mobile news
    mobile setlists
    antslive!
    Ants' Twitter
    DMBLive Twitter
    Ants Facebook
    Ants Instagram