topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Thursday March 28, 2024, 4:56 pm
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Anyone familiar with Excel? [Case Closed]  (Read 7504 times)

dantheman

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 742
  • Be good if you can!
    • View Profile
    • Donate to Member
Anyone familiar with Excel? [Case Closed]
« on: November 02, 2017, 09:06 AM »
Have an Excel file set up with 14 tabs (12 for each month, 1 for reserve & 1 for monthly results).
Let's say the reserve tab has various $ or Euro amounts to be used on a monthly basis.
So far, i've had to copy/paste from the reserve onto the specified month then clear the reserve items, thus leaving a space of many items that can not be deleted without breaking code.
What i'm hoping to do, is that the empty space created in the reserve tab be automatically filled or moved up with remaining items (monetized items) instead of having to copy/paste again and clear the lines where they were before.

If i can get this part done, i would be a very happy man!   :)
« Last Edit: November 14, 2017, 04:35 PM by dantheman »

anandcoral

  • Honorary Member
  • Joined in 2009
  • **
  • Posts: 777
    • View Profile
    • Free Portable Apps
    • Donate to Member
Re: Anyone familiar with Excel?
« Reply #1 on: November 03, 2017, 05:06 AM »
Very easy.

Just upload your xls file and let us have a go on it.  :)

Regards,

Anand

dantheman

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 742
  • Be good if you can!
    • View Profile
    • Donate to Member
Re: Anyone familiar with Excel?
« Reply #2 on: November 09, 2017, 08:30 AM »
Sorry for the delay anandcoral.
Forgot to tick off notification to replies! duh!

Anyway, will p.m. Excel file to you as it seems it can't be uploaded here.
Hope it's not too messy for you to work with.  :-[

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,959
    • View Profile
    • Donate to Member
Re: Anyone familiar with Excel?
« Reply #3 on: November 09, 2017, 01:37 PM »
will p.m. Excel file to you as it seems it can't be uploaded here.

you can zip it, or changing the extension might work. (You cant send files per forum PM)
Tom

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 11,186
    • View Profile
    • Donate to Member
Re: Anyone familiar with Excel?
« Reply #4 on: November 09, 2017, 03:15 PM »
will p.m. Excel file to you as it seems it can't be uploaded here.

you can zip it, or changing the extension might work. (You cant send files per forum PM)

Or you can share it with a service like dropbox if you have one...

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: Anyone familiar with Excel?
« Reply #5 on: November 09, 2017, 06:22 PM »
So far, i've had to copy/paste from the reserve onto the specified month then clear the reserve items, thus leaving a space of many items that can not be deleted without breaking code.
It is not clear to me why copy / paste does not do what you want. Are the equations row-based ? Do you also have column equations ?
Row equations copy / paste correctly. As for column equations, the trick is to insert blank lines in the specified month and then paste the recurring items
Perhaps share with us more details about the equations...
HTH !

Pierre Paul Landry
IQ Designer
http://www.infoqube.biz

Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Anyone familiar with Excel?
« Reply #6 on: November 09, 2017, 06:38 PM »
as stated elsewhere you need to post the file (use dummy data if its sensitive)

Without an example any/all suggestions are likely to purely speculative as we have to guess the use case

dantheman

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 742
  • Be good if you can!
    • View Profile
    • Donate to Member
Re: Anyone familiar with Excel?
« Reply #7 on: November 10, 2017, 05:24 AM »
Here's a Dropbox link.
Wasn't expecting such a good response!
Will try to fill in with some data today give a better idea.

Thanks a bunch guys! :)

dantheman

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 742
  • Be good if you can!
    • View Profile
    • Donate to Member
Re: Anyone familiar with Excel?
« Reply #8 on: November 10, 2017, 08:31 AM »
Btw, it's the "reserve" tab I need most help on for now.
the last tab has a list of things to do.

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: Anyone familiar with Excel?
« Reply #9 on: November 10, 2017, 10:30 AM »
Hi dantheman,

I'm very fluent in spreadsheets and Excel in particular. I looked at the dropbox file but fail to see what you're trying to achieve. My feeling is that you're starting on the wrong foot altogether, so if I were you, I'd start by detailing here your objectives, typical information flow and analysis / reports you want.

Pierre
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

dantheman

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 742
  • Be good if you can!
    • View Profile
    • Donate to Member
Re: Anyone familiar with Excel?
« Reply #10 on: November 10, 2017, 12:04 PM »
Thank you Pierre.

A simple point here (i hope)...
The "reserve to start with" tab has items to be assigned/transferred to the months of the year.
Once the date assigned, they are manually copy/pasted to their respective months.
The next step is to clear those assigned from the "reserve", thus leaving empty spaces/lines.
In order to fill the empty spaces, so far, all i can do is to manually copy/paste up the remaining to be assigned.
What would be helpful is to have these automatically be "upped".
If the ones that are finally assigned could be transferred automatically to their respective months, then maybe that would facilitate the "upping" of those waiting to be assigned in the "reserve" tab?

PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: Anyone familiar with Excel?
« Reply #11 on: November 10, 2017, 12:13 PM »
Hi,

The use of tabs and moving items from one to the other just doesn't seem right. Is there any reason you approaching this in this unusual way ?
Would it not be simpler to have a flat list of items, with or without dates and use filters and/or pivot tables or other means to see monthly / yearly reports

Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

dantheman

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 742
  • Be good if you can!
    • View Profile
    • Donate to Member
Re: Anyone familiar with Excel?
« Reply #12 on: November 10, 2017, 12:46 PM »
I'm open to suggestions Pierre.
This file is, however, the result of many hours of work with a friend who knows so much more about Excel than little ol' me.
That person has helped me in so many ways and when you find someone like that, it's not easy to go to someone else who might help you today and be gone tomorrow.

dantheman

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 742
  • Be good if you can!
    • View Profile
    • Donate to Member
Re: Anyone familiar with Excel? [Case Closed]
« Reply #13 on: November 14, 2017, 04:39 PM »
Just to openly express my gratitude to PPLandry.  :Thmbsup:
He has graciously offered some of his precious time and talent to offer a pleasant, simplified and most viable alternative to what i have been doing so far.

mouser

  • First Author
  • Administrator
  • Joined in 2005
  • *****
  • Posts: 40,896
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • Read more about this member.
    • Donate to Member
Re: Anyone familiar with Excel? [Case Closed]
« Reply #14 on: November 14, 2017, 04:41 PM »
Just want to say how nice it is to see people here helping each other out  :up: :up:

skwire

  • Global Moderator
  • Joined in 2005
  • *****
  • Posts: 5,286
    • View Profile
    • Donate to Member
Re: Anyone familiar with Excel? [Case Closed]
« Reply #15 on: November 14, 2017, 10:47 PM »
Nicely done, PPLandry.    :D :up:

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Anyone familiar with Excel? [Case Closed]
« Reply #16 on: November 15, 2017, 01:57 AM »
please post the outcome so everyone that was interested can see

anandcoral

  • Honorary Member
  • Joined in 2009
  • **
  • Posts: 777
    • View Profile
    • Free Portable Apps
    • Donate to Member
Re: Anyone familiar with Excel? [Case Closed]
« Reply #17 on: November 15, 2017, 03:34 AM »
Hi dantheman, happy to hear your problem is solved. I was thinking querying a base table with rules for different sheets, will solve your case but it will be of different approach. Any way let us know your solution.

Regards,

Anand

dantheman

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 742
  • Be good if you can!
    • View Profile
    • Donate to Member
Re: Anyone familiar with Excel? [Case Closed]
« Reply #18 on: November 15, 2017, 08:16 AM »
Oh boy!
Wasn't really expecting this feedback for sure!
Will try to express it to the best of my abilities.

Anyway, with PPLandry (convenience wise) we did a Google Hangout while working it out on a Google Spreadsheet.
As you can see from this link, the project no longer has 14 tabs but only 2 now.
The second tab (monthview) can be obtained in a different way from the data one.
While in data tab, clicking on "data", then "filter" has many different options to choose from.
Seeing that i imported the working file into Microsoft Excel, i've discovered a few things and a few glitches to iron out.
PPLandry was kind enough to let me know that i can reach out to him again when needed.
So that is a huge plus.
Will try to iron out all the bugs before then.


PPLandry

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 702
    • View Profile
    • InfoQube Information manager
    • Read more about this member.
    • Donate to Member
Re: Anyone familiar with Excel? [Case Closed]
« Reply #19 on: November 15, 2017, 10:49 AM »
My pleasure dantheman,

For the record, the chosen approach was to use a Google spreadsheet with 2 sheets.
The first contains the data (the who, what, when)
The second contains a pivot table to summarize. It was setup as a year/month view of $, but could slice and dice on other fields
Since Google pivot tables (unlike Excel) cannot group by dates, 2 calculated columns were added to the data sheet: Year, Month (using built-in functions)

While Google spreadsheet is not as powerful as Excel, it does provide these easy to use features, which could be useful, either now or down the road:
- Sharing (View, View and Edit)
- Mobile apps (phone, tablets)
- History with easy revert back, in case of mistakes
- Chat window
- Live collaboration
- Browser-based and free (so nothing to install)

Pierre Paul Landry
IQ Designer
http://www.infoqube.biz
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz

anandcoral

  • Honorary Member
  • Joined in 2009
  • **
  • Posts: 777
    • View Profile
    • Free Portable Apps
    • Donate to Member
Re: Anyone familiar with Excel? [Case Closed]
« Reply #20 on: November 16, 2017, 06:10 AM »
Thanks dantheman for sharing the xls and PPLandry for explaining it.

Regards,

Anand