OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

auto updating a sheet and sorting data

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
tosca
General User
General User


Joined: 13 Apr 2010
Posts: 7

PostPosted: Tue May 18, 2010 6:26 am    Post subject: auto updating a sheet and sorting data Reply with quote

hi,

Does anyone know how i can automatically update information from one sheet to another.
I have 3 sheets Ongoing History(sheet 1) Outstanding Works.(sheet 2) and Refit (sheet 3) The ongoing history sheet has an outstanding column, I want to auto update the row of information to the outstanding works sheet if the cell (works completed column) is empty. Then take information from sheet 2 to sheet 3.

I have tried using Vlookup and also auto update(which I got to work)I also tried Macros but the n can't get it to do it automatically with a push button.....


help
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Tue May 18, 2010 8:44 pm    Post subject: Reply with quote

You might be able to do that with just functions.
The attached doc shows one way of automatically sending different rows of data from Ongoing History sheet to Outstanding Works sheet, according to the absence of a completion date, and then some of the transferred columns from the Outstanding Works sheet to the Refit sheet.
The formulas on the Outstanding Works sheet are single-value array functions and therefore were entered using the Ctrl+Shift+Enter key combination. They also required that the Ctrl key be pressed while the fill handle was dragged to fill the original array formula in 'Outstanding Works.A2 to the other rows and columns.
http://www.4shared.com/file/uYQu2UDP/Info_Auto_Update.html
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
tosca
General User
General User


Joined: 13 Apr 2010
Posts: 7

PostPosted: Wed May 26, 2010 6:29 am    Post subject: Auto update sheet and data Reply with quote

Hi Ken,

thankyou so much for helping me with this formula, I have now used it and populated it with some real data and it is so close to working however I am getting an error message on the 2nd sheet in the date column and cannot figure out how to stop it. Also I tried to copy the formula to 300 rows and had trouble. Can you help?

http://www.4shared.com/file/jKku2Dxz/TEST_2010_Maintenance_Schedule.html

A very grateful

Ashley
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Wed May 26, 2010 7:39 am    Post subject: Reply with quote

Hi Ashley,
I changed the IF(Row($A1)>27-COUNT($'Ongoing History'.$F$2:$F$28) parts in the Outstanding Works sheet formulas to...
IF(Row($A1)>COUNT($'Ongoing History'.$D$2:$D$28)-COUNT($'Ongoing History'.$F$2:$F$28)
because 27-COUNT($'Ongoing History'.$F$2:$F$28) does not equal a count of the jobs entered into the Ongoing History sheet.
I think the error 504 in Outstanding Works D2 was just Calc 'playing hard to get'!
All I had to do to get it to work was Backspace out the leading equals sign, type the equals sign back in, then Ctrl+Shift+Enter. Sometimes when you accidently do something wrong when trying to get an array formula in place, Calc can be a bit unforgiving and makes the correction of your mistake more difficult than you might expect.
Also, not sure why, but the row references in that one cell's formula went up to 500 (perhaps you were experimenting with different possible solutions). The other formulas' row references only go up to 28. It's now back to 28, but that was not the cause of the error.
http://www.4shared.com/file/LhhjxUKH/TEST_2010_Maintenance_Schedule.html
Ken Johnson
EDIT: Oops, forgot about the 300 rows bit. Just needed to change all the references to row 28 to references to row 301 in 1 formula then Ctrl+fill down (and across) to get the formulas to work with 300 rows of data on the first sheet. You probably don't need the formulas to go down to row 301 on the other sheets but it doesn't really matter. Never know, you just might have 300 incomplete jobs someday.
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
tosca
General User
General User


Joined: 13 Apr 2010
Posts: 7

PostPosted: Wed May 26, 2010 10:11 am    Post subject: auto updating a sheet and sorting data Reply with quote

Hi Ken,

I think my head is a bit fryed I can't get it to take the 300 rows, do I also have to change the count 27 part?

rgds
ashley

Confused
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Wed May 26, 2010 7:56 pm    Post subject: Reply with quote

Sorry Ashley, something must have gone wrong when I updated the link. The updating did seem to take a very long time. I have had another go at the update and it now downloads a file with the formulas able to process 300 rows of data from the first sheet.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
tosca
General User
General User


Joined: 13 Apr 2010
Posts: 7

PostPosted: Thu May 27, 2010 3:26 am    Post subject: auto updating a sheet and sorting data Reply with quote

hi Ken
thankyou so much for your reply,I think I'm doing something stupid...
I can't get it to copy the formula across so it applies to 300 rows, I have tried ctrl, and fill but it is only taking across a single rows formula Confused
Also the count formula in column one is only to 27 should that also change to 301?
The formula is different in column 1 (count 27) than the rest of the columns, is that why i may be having trouble with the copying?

regards ashley

btw, love the photo of the Jack Russell, I also have one, is yours a girl or a boy?
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Thu May 27, 2010 4:34 am    Post subject: Reply with quote

Hi Ashley,
If you are having trouble with the Ctrl + fill stuff you can always use just copy/paste.
In 'Outstanding Works'.$B$2 the formula should be...
IF(ROW($A1)>COUNT($'Ongoing History'.$D$2:$D$301)-COUNT($'Ongoing History'.$F$2:$F$301);"";INDEX($'Ongoing History'.B$2:B$301;SMALL(IF($'Ongoing History'.$F$2:$F$301="";ROW($F$2:$F$301)-1;"");ROW($A1))))
which of course must be committed using Ctrl+Shift+Enter.
You can then copy that cell then select B1:D301 (this selection can be easily done by selecting B2 then change the address in the name box on the left of the formula bar from B2 to B2:D301 then pressing Enter) and go Edit|Paste or Ctrl+V or if there is any formatting that you are wanting to be left alone, Edit|Paste Special or Ctrl+Shift+V.
When the above formula is pasted into columns C and D the Bs change to C and D respectively.
The same technique can be used on the SXM sheet where the formula in A2 is...
IF(ROW($A1)>SUMPRODUCT($'Outstanding Works'.B$2:B$301<>"");"";INDEX($'Outstanding Works'.B$2:B$301;ROW($A1)))
again committed using Ctrl+Shift+Enter. The Bs change to Cs when pasted into column B.
If you still have trouble you might have to copy just the text of the formula (copy everything in the formula bar except the leading equals sign) in the first cell (B2 on 2nd sheet and A2 on 3rd sheet), Backspace out all of your previous attempts, then paste the text back into the first cell. Then, add the equals sign back in and continue with the rest.
The Jack Russell's a de-sexed boy. Tommy's nearly 3 years old. We got him March 2008 from Jack Russell Rescue. He's very cute and pampered.
And yours?
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
tosca
General User
General User


Joined: 13 Apr 2010
Posts: 7

PostPosted: Thu May 27, 2010 8:49 am    Post subject: auto updating a sheet and sorting data Reply with quote

hi ken,

me again, I have got the copy to work fine, i have another problem. The data stops auto moving across when it gets to row 116?

http://www.4shared.com/file/UPZaP0bg/test_maintenance_template_v2.html

I also need to only auto copy the TWCC works (issued to in first sheet) to the 3rd sheet,
how do I do that?

also put a photo of my Jack Russell, Chester, he is running in his sleep at the moment with his head on my foot, while I'm working on this Also spoilt rotten!!
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Fri May 28, 2010 2:50 am    Post subject: Reply with quote

Hi Ashley,
The problem was caused by the dates.
Most are numbers formatted as date.
I assumed they would all be this most desireable data type.
The problem dates are strings. Their first character, visible only in the formula bar, is an apostrophe.
The COUNT function only counts numbers, not strings, and this was stuffing up the calculation of incomplete jobs.
So, all I had to do was change the COUNT functions to the COUNTA function so that the number dates and string dates were both counted.
If the data on the Ongoing History sheet was values returned by formulas, as opposed to raw keyboard-entered data, the COUNTA function could also not be used because it counts numbers, strings and formulas, even formulas returning a blank (""). The SUMPRODUCT function would then have to be used...
=SUMPRODUCT(Whatever Range<>"")
The TWCC sheet has had condition that the incomplete job be issued to TWCC added to the formula.
http://www.4shared.com/file/J81-3ZG5/test_maintenance_template_v21.html

I've made a different version that uses fewer array formulas.
To do it this way I've had to introduce a JOB NO column to the TWCC sheet.
Only column A on the 2nd and 3rd sheets have array formulas. In both sheets the array formula compiles the JOB NOs that satisfy the particular criteria.
The other columns have VLOOKUP formulas with a nested MATCH formula to determine the column containing the values to be returned.
For the MATCH formula to work properly it is essential that the headings on the 2nd and 3rd sheets be identical to the same headings on the 1st sheet.
I have had to change the DESCRIPTION heading on the 2nd and 3rd sheets to JOB DESCRIPTION so that the MATCH formula would return a number rather than an NA error. (the weird sheet1 JOB DESCRIPTION heading has been edited to remove the leading spaces and double space between the two words).
This version is a little larger than the all array formula one, because of the extra JOB NO column on the TWCC sheet, but it has the advantage of being easier to understand (your average spreadsheet user struggles to understand array formulas). Also, array formulas can be slow.
http://www.4shared.com/file/yYDBZ5Ny/test_maintenance_template_v3.html

It is possible to avoid array formulas totally but one or more helper columns would have to be used. The helper columns can be hidden. If your interested in an array-formula-free version just let me know.

You mentioned a pic of Chester. I think you have forgotten to add the link!

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
tosca
General User
General User


Joined: 13 Apr 2010
Posts: 7

PostPosted: Fri May 28, 2010 5:03 am    Post subject: auto updating a sheet and sorting data Reply with quote

solved

Hi Ken.

The new version 3 is working perfectly, I'll see how it goes when I start using it for 20 + new worksheets, but I think I can now manage it!!

thank you so much, I would never have figured it out.

very grateful
ashley

here's a photo of Chester, he's a sea dog lived on a boat since he was 6 weeks old...

http://www.4shared.com/photo/A--yy23H/Chester_on_watch.html
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Fri May 28, 2010 6:10 am    Post subject: Reply with quote

Chester is very handsome and intelligent looking.
Good luck with finishing the spreadsheet.

Ken Johnson
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group