Page 6 of 6 FirstFirst ... 456
Results 51 to 58 of 58

Thread: Excel Sorcerers!

  1. #51
    Corvette Enthusiast Kchrpm's Avatar
    Join Date
    Jan 2014
    Location
    Cincinnati, OH
    Posts
    7,200
    1) File names with periods in them scare me
    2) Your shit's broken.
    3) Without looking at the actual file, it'd be hard to figure out what the quirk is in the data/settings/formulas that could be causing it. Everything looks right with what you've presented, but usually there's some weird coincidence that is left out when trying to translate. The fact you miss it during translation is the same reason you miss it while troubleshooting.
    Get that weak shit off my track

  2. #52
    High Plains Luddite George's Avatar
    Join Date
    Jan 2014
    Posts
    3,969
    Fair enough. Thanks for taking a look.

    Part of the problem is I'm combining six spreadsheets into one, and who knows what each person did to his spreadsheet in terms of formatting changes and whatnot.

  3. #53
    High Plains Luddite George's Avatar
    Join Date
    Jan 2014
    Posts
    3,969
    I finally figured it out.

    One person in another location was a running large report and saving it in Excel. This was the report I used to run, but they wanted someone else to learn how since I'm getting the boot soon.

    A second person (also learning how - go team!) was receiving the full report in Excel, and, as I was told, SPLITTING UP the report (more on that later) and sending individual spreadsheets to other people to add additional information.

    Each person would add their data and then send HIS OR HER INDIVIDUAL SPREADSHEET (more on that later, too) back to Person 2 for review, questions, etc.

    Once all that was complete through daily work, at the end of the week, Person 2 would send it to the only person left in the department *ahem* who knows how to do a vlookup to combine them and produce a finished spreadsheet for the week.

    Sounds like too many cooks in the kitchen, right? Damn straight! Poor communication was the problem here, as it so often is.

    I thought I had solved a lot of problems a couple weeks ago after explaining to everyone that they may not delete columns, insert columns with notes just anywhere, or otherwise customize their individual spreadsheets to the point that I had to combine everything manually, instead of letting Excel do what it can do so quickly and easily, if everyone will just put their notes in the columns provided and in the order provided.

    Simple, right?

    Well, no.

    As it turns out, Person 2 was not SPLITTING UP the report at all. And, each person was not working on HIS OR HER INDIVIDUAL SPREADSHEET at all. Oh no. That would have made too much sense.

    Person 2 was color coding rows of the spreadsheet for each person to work on. Remember my earlier question about vlookups and color formatting? Well, she was color-coding certain rows, based on date ranges, and sending the exact same spreadsheet to everyone and saying, "Joe - you work the blue rows. Shirley - you work the yellow ones", and so forth. Then, unbeknownst to me, she asked everyone to remove the colors from the spreadsheets before returning them, "so as not to mess up the combining process", or something like that, after I merely mentioned that I was not able to keep the colors intact through the vlookup process. I didn't say there couldn't be colors, just that that information would be lost on the combined report. Had I seen all the different colors, I might have understood what has happening before now.

    Well, this is kind of funny, now that I have a clue. My problem today is that I thought each person had a unique spreadsheet, so there could be no possibility of duplicate information. That wasn't the case, and that's why all the cars on my report were blue, in my earlier example.

    Additionally, there are date ranges on the report. What made things worse, to continue with my earlier example of cars, is that one person was working on '67 - '69 Camaros, another with '70 - '81 Camaros, and so forth. What a mess!

    At least I've learned something today. Thanks again for the help, Keith. You've made me look good at work more times than I can adequately thank you for.

  4. #54
    High Plains Luddite George's Avatar
    Join Date
    Jan 2014
    Posts
    3,969
    What is up, sheetheads?


  5. #55
    High Plains Luddite George's Avatar
    Join Date
    Jan 2014
    Posts
    3,969
    Anyone still giving out free Excel advice here? I sure could use some, if so.

    I am sent a monthly PDF file originally created in Excel with perhaps fifty to seventy lines of text that I have to key in to other software. The dates are all in 2020.

    This is a "saved as a PDF" file from Excel rather than a scan of a printout, so I can highlight and copy the text of the PDF and paste it into Excel.

    However, when I do this, all the dates change from 2020 to 2016. I've tried pasting as values and everything else I can think of, such as making sure the cells are formatted as Short Dates, but the dates always paste in as 2016 no matter what.

    Since the person who is creating these spreadsheets is using Excel, I requested they send me the Excel version along with the "official PDF file" just to help me enter their data faster (it's faster if I can sort the data the way I want it, rather than how they send it to me). And they did. Cool!

    But, their spreadsheet now shows 2016 on all rows also.

    Am I (or we) missing something here? How can I get 2020 dates from a PDF to paste into Excel without the year changing?

    Also, I know if I cell says 1/1/2020, I can enter a 4 in the next cell and then do =A1+B1 and get 1/5/2020 as a result. But I'm thinking doing that over four years, with two of them being leap years, would be a mess.

    Thanks for any ideas.

  6. #56
    Senior Member
    Join Date
    Jan 2014
    Posts
    5,885
    I want to start by saying I am not an Excel expert. I once made a formula and it worked most of the time.

    Have you tried pasting into another app, like Notepad? I'm wondering if there's some weirdness going on with the PDF rather than Excel.

  7. #57
    Corvette Enthusiast Kchrpm's Avatar
    Join Date
    Jan 2014
    Location
    Cincinnati, OH
    Posts
    7,200
    That is a weird problem. Here's a formula to convert all the dates to 2020, using A2 as the cell where your date is:

    =DATE(2020,MONTH(A2),DAY(A2))

    It takes the month and day from A2, but uses 2020 as the year.

    No idea what's causing the issue, but that's at least an easy way to get the right numbers out.
    Get that weak shit off my track

  8. #58
    High Plains Luddite George's Avatar
    Join Date
    Jan 2014
    Posts
    3,969
    Quote Originally Posted by Tom Servo View Post
    I want to start by saying I am not an Excel expert.
    Who let this guy in here?



    Have you tried pasting into another app, like Notepad? I'm wondering if there's some weirdness going on with the PDF rather than Excel.
    Yes, I did. I've tried a blank email, Word, and I guess it's Notepad that comes up when you right-click on the desktop and select New Text Document. I was trying to get the date column to lose any connections it might have had to the spreadsheet, such as some formula from 27 versions ago that's still there, even though I can't see it.

    Thanks to Kchrpm for the formula. Looks like I won't need it now, as the sender provided a corrected version. Her comments: "Whoa, that was odd." Yes, indeed.

    Much appreciated, gents.

Posting Permissions

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