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

dantheman · « **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!

anandcoral · « **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 · « **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 · « **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.
-dantheman (November 09, 2017, 08:30 AM)

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

wraith808 · « **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.
-dantheman (November 09, 2017, 08:30 AM)

you can zip it, or changing the extension might work. (You cant send files per forum PM)
-tomos (November 09, 2017, 01:37 PM)

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

PPLandry · « **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.
-dantheman (November 02, 2017, 09:06 AM)

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

Target · « **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 · « **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 · « **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 · « **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

dantheman · « **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 · « **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

dantheman · « **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 · « **Reply #13 on:** November 14, 2017, 04:39 PM »

Just to openly express my gratitude to PPLandry.

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 · « **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

skwire · « **Reply #15 on:** November 14, 2017, 10:47 PM »

Nicely done, PPLandry.

Target · « **Reply #16 on:** November 15, 2017, 01:57 AM »

please post the outcome so everyone that was interested can see

anandcoral · « **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 · « **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 · « **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

anandcoral · « **Reply #20 on:** November 16, 2017, 06:10 AM »

Thanks dantheman for sharing the xls and PPLandry for explaining it.

Regards,

Anand