Author Topic: Any Spreadsheet gurus here?  (Read 2191 times)

0 Members and 1 Guest are viewing this topic.

Offline TB-AV

  • Honorable Ex-Mod
  • All Time Legend
  • *****
  • Posts: 14966
  • Good Vibes 329
Any Spreadsheet gurus here?
« on: February 18, 2014, 04:16:03 am »
ETA: Hold the phone... I think I figured it out. I believe I had two pages linked to Page2 adn deleted some calculations which broke page 2....

Leaving this here until I know for sure.

====================================================





I'm trying to make a spreadsheet in Libre Office and am pretty much fumbling my way through with help files and google lookups... but I have this problem that I can see will be ongoing.

Let's say I data on an online system. I can export any or all that I need. Now let's say I select 10 items out of say 100.

So I get my 10 csv items and they drop on a line in Calc.

Ok, now lets say column 2 and 3 need to be combined into one cell and maybe column 8 needs some basic math done on it.

So far so good. I've figured out how to do all that. what I did was make a little area that made a new cell for each of those operations and got the desired result.

Next I need that data a little easier to read so I make a page 2 and I link from page 1 the fields I need and the order I need them in. I put this in column format on page 2. whereas page 1 is row format.

After I  get his page 2 column done I can link it to another program and it will stick all the data in the proper place.

Wonderful.. everything works.... EXCEPT..... if tomorrow I need to add another item or two to my initial export.

So my whole setup was based on this..

1 2 3 4 5 6 7 8 9 10

and now I might need

 1 2 3 4 Blue 5 6 7 Red 8 9 10 ... where Blue and Red are my new fields...

Well that totally screws up my page 2 links that are simply references from page 1.

Now I -could- export 1 2 3 4 5 6 7 8 9 10 Blue Red ... but it doesn't read as well. It would be nice if I could keep it in some logical order.

Sooo... the question is... is there some method, workflow or whatever that allows you to add or subtract and not blow out the references between page 1 and page 2?

As an example... on Page2 if Row3-A5 references say Page1:cell R10   is I simply add a row in page like add a row at 3 the A5 will become A6 but it keeps it's reference to Page1:R10.

BUT... If I change anything on Page1.... the Page2 doesn't know what the hell is going on and breaks.

So basically Page2 knows to look back to Page1, but Page1 doesn't know to keep Page2 correct if Page1 is altered.

I can understand Page2 breaking if I delete something from Page1... obviously it's no longer there. But if it simply moves over a cell it should know to tell Page2 about it.

Is there a term for this?  I mean to make it work..... I know what the term is for what I have now. I just can't think what to search for.


« Last Edit: February 18, 2014, 04:32:41 am by TB-AV »
Gone

Offline Majik

  • Stadium Superstar
  • ******
  • Posts: 2062
  • Good Vibes 124
Re: Any Spreadsheet gurus here?
« Reply #1 on: February 21, 2014, 08:45:44 pm »
Did you sort this out in the end?

Cheers,

Keith
Guitars: PRS Singlecut S2, Fender Tele Lite Ash, G&L Legacy Tribute, Freshman Apollo 2 OCBX, Gibson SG Special P90
Amps: Bugera G5 Head, Boss Katana 100
All sorts of other stuff.

Offline TB-AV

  • Honorable Ex-Mod
  • All Time Legend
  • *****
  • Posts: 14966
  • Good Vibes 329
Re: Any Spreadsheet gurus here?
« Reply #2 on: February 21, 2014, 09:17:11 pm »
I got a lot of it coming along but I will need a few things that I can't even seem to think what term to search for.

Like some of my import data will have a blank space and I need a zero. Then when I transfer the blank to page 2 it puts in a zero? instead of keeping a blank? I can't figure that out...

But another thing I will need to do... as an example.

Let's say the import data says...

ITEM X  ITEM X #
-------- -------------
True ------ 2

Ok, I get verification that I actually an ITEM X because sometimes they leave out the quantity.

So then I need text appended. I end up with...

2 Item X ---- No problem ... I did that... but if my import shows FALSE and then a blank space, I need my value to be "None" ... and I have no idea how to do that or what it's even called.

Now if I could convert "blank" to "0" ( zero )... then I -could report "0 Item X" but I prefer "None"

Also ... and this is more rare so I could probably do it just as easily manually..

Let's say Item X is always Blue.. well I still just need to say 2 Item X... but Let's say Item X is Red... In that case I need to report "2 Item Y" ... again this may be more trouble than it's worth because all of this will be right on my screen an dI can easily type this random and rare Item Y.. .so I really may jsut forgo that... But the "blank" ="None" I really need to figure out.

... and I think that may get me going pretty well. It's really not a lot a complicated math or anything. Actually the program it goes into is set up to all that as much as I need it to. This is more of less to save me from reading one screen or piece of paper and typing it to another screen. I am simply pulling data from one system and shoving it into another.

But right now that whole zero vs blank and then figuring out to say ok if this is zero then write the word None.   OR....... actually... I can say if that first field is False then write None. If it's True then write Item X preceded by the number in the next field

ITEM --- ITEM #
--------------------
False

None

OR

ITEM --- ITEM #
--------------------
True --- 2

2 Item


that's what need ... Item and Item # are two different columns btw.

Gone

Offline TB-AV

  • Honorable Ex-Mod
  • All Time Legend
  • *****
  • Posts: 14966
  • Good Vibes 329
Re: Any Spreadsheet gurus here?
« Reply #3 on: February 21, 2014, 09:43:54 pm »
Oh, I just thought of something else

Imagine this.....

Date xx/yy/zzzz
Price xxx,xxx.xx

Now ... from my Target form. .the one I am pushing this new data to... there will be a date in that form. I can link that date to my data form that I am making... so let's say the result looks like this....


------------------------- REFERENCE DATE 02/20/2014 ( this from the target form )


Date1 xx/yy/zzzz
Price1 xxx,xxx.xx

Now I need to compare Reference Date to Date1 and if it's within 3 years I need to push the Date1 and Price1 values up to the target... otherwise the fields in target simply need to remain empty... ie. no action... no zeros.. just leave it alone...

I don't know what the correct way to look back 1,095 days is and then tell it what to do.
Gone

Offline Majik

  • Stadium Superstar
  • ******
  • Posts: 2062
  • Good Vibes 124
Re: Any Spreadsheet gurus here?
« Reply #4 on: February 21, 2014, 09:51:10 pm »
Sorry, but I'm slightly struggling to understand exactly what you are trying to do. That's not your fault, it's just difficult to describe this stuff, it's been a very long day here, and I've had a beer!

One question I have: is there any reason you are using, or have to use LibreOffice for this? It's a bit of an odd question because there's nothing wrong with LibreOffice: it's perfectly excellent.

The reason I'm asking is I was wondering if there was a possibility of uploading it to a Google Apps account (you have a Google Login?). The advantage there is you can share it and work collaboratively with other people, like myself, who can see first hand what you are trying to do, and maybe help you much more easily and quickly. You can always download the results into a ods file to use in LibreOffice Calc afterwards.

Cheers,

Keith
Guitars: PRS Singlecut S2, Fender Tele Lite Ash, G&L Legacy Tribute, Freshman Apollo 2 OCBX, Gibson SG Special P90
Amps: Bugera G5 Head, Boss Katana 100
All sorts of other stuff.

Offline TB-AV

  • Honorable Ex-Mod
  • All Time Legend
  • *****
  • Posts: 14966
  • Good Vibes 329
Re: Any Spreadsheet gurus here?
« Reply #5 on: February 21, 2014, 10:03:05 pm »
I guess I could made a screen shot.... I think I might have a google act but I tend to not do anything on Google because every time I do something it ends ok....... now let's link that FB,,, oh and your Twitter and ...almost done... we are just verifying your birth certificate.......

Maybe I can write it more simply.

Reference Date
02/20/2014

12/01/2013 | $100,000 |

Ok, I simply want the spreadsheet to compare 12/01/2013 to 09/20/2014 and if it's within three years I want it to take those two fields and simply copy them to two new fields... like this...

02/20/2014

12/01/2013 | $100,000 |

12/01/2013 | 100000 | ------- I will then link these fields to my form which is no problem.

NOW... let's say the date was.....
02/20/2014

12/01/2001 | $100,000 | -- notice not within 3 years ... so it places nothing in fields below

|"these are | now blank" | ------ so when they get linked basically no data gets transferred.


======================================================================
======================================================================
Gone

Offline Majik

  • Stadium Superstar
  • ******
  • Posts: 2062
  • Good Vibes 124
Re: Any Spreadsheet gurus here?
« Reply #6 on: February 21, 2014, 10:27:04 pm »
I guess I could made a screen shot.... I think I might have a google act but I tend to not do anything on Google because every time I do something it ends ok....... now let's link that FB,,, oh and your Twitter and ...almost done... we are just verifying your birth certificate.......

That doesn't sound right. Certainly Google aren't in the habit of wanting you to link to FB and/or Twitter. They do like to push you towards G+ these days, but if you ignore most of that you can normally set up an account quite quickly and easily.

Anyway...

Quote
Maybe I can write it more simply.

Ok, I simply want the spreadsheet to compare 12/01/2013 to 09/20/2014 and if it's within three years I want it to take those two fields and simply copy them to two new fields... like this...

02/20/2014

12/01/2013 | $100,000 |

12/01/2013 | 100000 | ------- I will then link these fields to my form which is no problem.

NOW... let's say the date was.....
02/20/2014

12/01/2001 | $100,000 | -- notice not within 3 years ... so it places nothing in fields below

|"these are | now blank" | ------ so when they get linked basically no data gets transferred.


OK, what I would do: In the two fields which have conditional data (that get linked to the form) I would put a conditional formula in the target cells. Let's use some proper spreadsheet references:

So if:

the date 02/20/2014 was in cell B3
the date 12/01/2013 was in cell A5
the number 100,000 was in cell B5

In cell A6 you could have the formula: =IF(YEARS($A5,$B$3,0)<3,A5,"")
In cell B6 you could have the formula: =IF(YEARS($A5,$B$3,0)<3,B5,"")

Cheers,

Keith
« Last Edit: February 21, 2014, 10:44:59 pm by Majik »
Guitars: PRS Singlecut S2, Fender Tele Lite Ash, G&L Legacy Tribute, Freshman Apollo 2 OCBX, Gibson SG Special P90
Amps: Bugera G5 Head, Boss Katana 100
All sorts of other stuff.

Offline Majik

  • Stadium Superstar
  • ******
  • Posts: 2062
  • Good Vibes 124
Re: Any Spreadsheet gurus here?
« Reply #7 on: February 21, 2014, 10:49:56 pm »
I've done this as a Google Spreadsheet as well. I had to use slightly different formulas, because Google spreadsheets doesn't support the "YEARS" function. However, this version should also work for LibreOffice Calc too. I've made it public so you shouldn't need a login:

https://docs.google.com/spreadsheet/ccc?key=0An0Qj6CCbMQGdFB1aUtkc3hEcHVSRVNkYmdRZ1FodlE&usp=sharing

Cheers,

Keith
Guitars: PRS Singlecut S2, Fender Tele Lite Ash, G&L Legacy Tribute, Freshman Apollo 2 OCBX, Gibson SG Special P90
Amps: Bugera G5 Head, Boss Katana 100
All sorts of other stuff.

Offline TB-AV

  • Honorable Ex-Mod
  • All Time Legend
  • *****
  • Posts: 14966
  • Good Vibes 329
Re: Any Spreadsheet gurus here?
« Reply #8 on: February 21, 2014, 11:48:23 pm »
Perfect!... the first one works!!

Now is there some easy reference for beginners to figure this stuff out? I've been using the help system and it might take me two hours to get one formula.

Like I had tow number fields of say |3|2|  to display in a filed as 3.2 .... oh... that's the other issue I have to hunt down... I think.... If I was given the number pair of |3|blank|  I needed my field to display 3.0... I may have figured that one out I can't remember right now.

So in other words....  =IF(YEARS($A5,$B$3,0)<3,A5,"")   I have no idea what that means. I can't say it. I think if I could say it maybe I could figure it out.

=IF ... ok the formula will be.... IF ....

($A5,$B$3,0)  first I don't get the $ ... next I suppose look at A5.... after that $B$3,0  I don't get.

Then I suppose it's saying if the result is <3, then stick A5 here,  I know the "" means basically text of nothing... how it knows when to do that I have no idea.

Is there some sort of general everyday syntax glossary... something better than the index of help. I usually end up with a Google search on an excel forum and then have to alter that to work in Calc.


I was actually able to get logged into the Google thing but I couldn't work on the sheet.
« Last Edit: February 22, 2014, 12:04:45 am by TB-AV »
Gone

Offline Majik

  • Stadium Superstar
  • ******
  • Posts: 2062
  • Good Vibes 124
Re: Any Spreadsheet gurus here?
« Reply #9 on: February 22, 2014, 12:13:25 am »
Not that I know of.

The function YEARS returns the number of years between two dates. So YEARS(A5, B3,0) returns the number of years between the two dates in A5 and B3. The last parameter says the type of comparison you want: "interval" or "calendar". TBH I'm not sure the difference.

You can ignore the $ symbols (although they are useful to know about). They allow you to "freeze" parts of the formula for copying.

The IF is basically IF(comparison, value_if_true, value_if_false)

Cheers,

Keith
Guitars: PRS Singlecut S2, Fender Tele Lite Ash, G&L Legacy Tribute, Freshman Apollo 2 OCBX, Gibson SG Special P90
Amps: Bugera G5 Head, Boss Katana 100
All sorts of other stuff.

Offline TB-AV

  • Honorable Ex-Mod
  • All Time Legend
  • *****
  • Posts: 14966
  • Good Vibes 329
Re: Any Spreadsheet gurus here?
« Reply #10 on: February 22, 2014, 01:04:14 am »

The IF is basically IF(comparison, value_if_true, value_if_false)


Ok, yes I get it now... after the Google lesson, it's easy to read... makes perfect sense.
Gone

Offline TB-AV

  • Honorable Ex-Mod
  • All Time Legend
  • *****
  • Posts: 14966
  • Good Vibes 329
Re: Any Spreadsheet gurus here?
« Reply #11 on: February 22, 2014, 07:02:14 pm »
Well, I've got my calculations coming along pretty well. and changing words, suffixes, etc...

But this one is driving me nuts...

A1 | --------------------- B1
1.0598 | -------------- 46729
1.06

C3 - =IF(B1<43560,B1 & " sf",A2 & " ac")

I formatted A2 to 0.00 so you can see the original number is 4 places but I have to work with 2 places. No matter how I try to format things I can't get C3 to display a 2 place decimal.

The math works fine ... I'll get  27,543 sf   or 10.6249 ac  for example but can't get  10.62 ac

One help file put me towards Tools, Options, Calculate, 'Precision as Shown' ...... and that work on A1 or A2 but it doesn't work on C3... so I was wondering is there some manner to insert "round to 2 decimal places" in my formula?

================ EDIT===

Ok... I've figured a way to use =ROUND and take my original A1 of 1.0598 and have it in a new cell. Then do the =IF deal on that cell and it works...



« Last Edit: February 22, 2014, 07:25:08 pm by TB-AV »
Gone

Offline Majik

  • Stadium Superstar
  • ******
  • Posts: 2062
  • Good Vibes 124
Re: Any Spreadsheet gurus here?
« Reply #12 on: February 22, 2014, 10:37:03 pm »
You should be able to set the formatting of C3 to be two decimal places without using a formula, if it's only the display you are concerned about.

Sent from my Nexus 7 using Tapatalk
Guitars: PRS Singlecut S2, Fender Tele Lite Ash, G&L Legacy Tribute, Freshman Apollo 2 OCBX, Gibson SG Special P90
Amps: Bugera G5 Head, Boss Katana 100
All sorts of other stuff.

Offline Scooter Trash

  • Stadium Superstar
  • ******
  • Posts: 3041
  • Good Vibes 80
Re: Any Spreadsheet gurus here?
« Reply #13 on: February 22, 2014, 10:44:39 pm »
Thanks, I just had a Lotus 123 flashback from the early 80s and broke into a cold sweat.
I dream of a better tomorrow where chickens can cross roads without their motives being questioned.

Offline TB-AV

  • Honorable Ex-Mod
  • All Time Legend
  • *****
  • Posts: 14966
  • Good Vibes 329
Re: Any Spreadsheet gurus here?
« Reply #14 on: February 22, 2014, 11:55:16 pm »
You should be able to set the formatting of C3 to be two decimal places without using a formula, if it's only the display you are concerned about.

Sent from my Nexus 7 using Tapatalk

I can.... but when I use a formula to manipulate the other fields and test them... when the results go into C3 it won't stay as 2 places. But I'm fine with my work around on that.

I've got much bigger problems now....

It turns out... I am working with Libra Calc and as you saw some of the commands are different.. well on top of that when i get my spreadsheet done... I have to save it as .xls so it works in my program.

Then I click a cell.. then I click in my program and click LINK... so that means what ever data was in that cell goes into my form and I don't have to type it...

Well, I was rolling along making spreadsheet 1, spreadsheet 2  renaming each one as it got more recent and better. Problem is I sat down and LINKED a few pages of fields, one at a time, only to find out later that an XML file was also being created so my new spreadsheets... like version 5  had lost all its LINKS

Anyway I figured that out an dfixed it.

But now I need to add a couple more fields and I'm worried if I add rows or columns things are going south again...

BUT.... I looked at the XML and they used a lot $ in there so I hope it stays referenced correctly. I think I figured if I keep the name of the .xls file the same ... like Master Import Export File.... then that .xml file will keep all my Links AND allow me to update it without breaking it.

I hope....... otherwise I'm going to feel like a USA hockey player pretty soon.

The good news is.... as I keep trying to Link things I see how I need a little more data but I also see how, ok, I will just do a formula on the extra data and be able to fill in or leave blank, etc..  whereas before  I had no idea what to do. Now it's ended up mainly being bad planning and me not knowing how alter the sheet without breaking it.

I copied and pasted a couple columns from one file to the neck in hopes of saving my Links.... man that was a mistake.... that was before I saw the .xml files that were being created.

Anyway.... I'm getting there.......

Gone

 

Get The Forum As A Mobile App