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

Imported Data deleted cells, messing up REF and formulas

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


Joined: 07 Apr 2011
Posts: 5

PostPosted: Thu Apr 07, 2011 8:23 am    Post subject: Imported Data deleted cells, messing up REF and formulas Reply with quote

I came across an extremely annoying problem.

I made a spreadsheet that imports data from a website and then does analysis on that data. To explain what is happening i'll give a simplified version of how the spreadsheet is set up.

Lets say it has 3 sheets. page1 is a data table (called table). page2 is a "schedule " (imported) . And page 3 is analysis that copies over the schedule and that looks up the names in the schedule in the table and works from there.

Page3 part that copies the schedule looks something like this:
A1
=Schedule.A1
------------------
A1-A30 look the same, incremented.
-----------------

Now page2 ("schedule") imports the names into cells A1-A30. When i set it up it worked the 1st day. The problem is that the schedule is different every day, some days may have 10 names listed, some may have 20.

So if on day1 i had 20 names, (A1-A20) they were all copied over to page3 just fine.
Now on day2 there are only 10 names (A1-A10). When they are imported into page2 those 10 names are copied over to page 3.(same Schedule.A1-A10)

The problem is that everything past cell 10 on page 3 now has a REF error. Instead of still saying Schedule.A11 and so on its just REF.....

The reason this is happening is that the day1 schedule occupied 20 cells, day2 schedule replaced it with just 10 and what it did is delete all the cells after the last 1. So what was A21 on day1 is now A11 on day2. And silly page3 seems to think A11 is gone never to be seen again! If day3 was to have 15 names then that A11 cell would be shifted to A16.

Needless to say this is killing page3 completely. I tried making it constant with $Schedule.$A$21 but it doesn't help. When new 10 name schedule is imported that $21 is changed to $11

Its a matter of when the data is important of completely deleting the cells rather then filling them.

How can i fix this?


Last edited by demens13 on Thu Apr 07, 2011 8:29 am; edited 1 time in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Apr 07, 2011 8:27 am    Post subject: Reply with quote

Use scenarios to fill the same range with different sets of data and/or formulas.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
demens13
General User
General User


Joined: 07 Apr 2011
Posts: 5

PostPosted: Thu Apr 07, 2011 8:33 am    Post subject: Reply with quote

Villeroy wrote:
Use scenarios to fill the same range with different sets of data and/or formulas.


I'm not sure what you're saying there.

The imported data is in a table, table size is different each day so the range of cells it fills is different. Is there a way to change that?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Apr 07, 2011 8:41 am    Post subject: Reply with quote

Describing the data is pointless.
If you could paste them into a text file (*.csv) it could be fairly easy to import the resizing data areas dynamically through the Base component.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
demens13
General User
General User


Joined: 07 Apr 2011
Posts: 5

PostPosted: Thu Apr 07, 2011 8:53 am    Post subject: Reply with quote

If i wanted to be copy and pasting things everyday i wouldn't have set up this spreadsheet in the 1st place. The whole point of it is to get the data and analyze it automatically.

How can there not be a solutions to this, it seems like such a simple problem.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Apr 07, 2011 9:05 am    Post subject: Reply with quote

=INDEX(some range; row_number ; column_number) gives a value at a given row and column index.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
demens13
General User
General User


Joined: 07 Apr 2011
Posts: 5

PostPosted: Thu Apr 07, 2011 9:43 am    Post subject: Reply with quote

Villeroy wrote:
=INDEX(some range; row_number ; column_number) gives a value at a given row and column index.


You might be onto something here. Testing it now, seems to be working.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Apr 07, 2011 9:49 am    Post subject: Reply with quote

OK,

=INDEX(some range; row_number) gives the entire row at the given row position when entered as array formula (Ctrl+Shift+Enter)
=INDEX(some range; 0 ; column_number) gives the entire column at the given column position when entered as array formula (Ctrl+Shift+Enter)

=OFFSET(some_range ; row_offset ; column_offset ; resize_rows ; resize_columns)
can return single values or arrays of values in the most flexible manner.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
demens13
General User
General User


Joined: 07 Apr 2011
Posts: 5

PostPosted: Thu Apr 07, 2011 10:21 am    Post subject: Reply with quote

I dont think i need OFFSET.

INDEX is doing the job i want it to do.

Not sure if i did something wrong but i had to increment the row numbers manually, they would not do it automatically. No big deal.

Only issue i have with it is that it changes the range to whatever the table range is. I set it up initially with a range of A1:A30. If the table is 20 rows, it changes the range to A1:A20, if its 10 then it changes to A1:A10.

Technically this is exactly what i want it to do but i would prefer that the range of stayed consistent at A1:A30 at no matter the table size. This way i can add things manually as well.

The way things are now, if the table is lets say 10. Ranges A1:A10 are filled automatically. Perfect. A11-A30 are empty and produce Err:502 on the next sheet.

I would prefer it to be N/A. So if i typed something in A11 it would be recognized but the range stays at A1-A10 so it doesn't.

This is a very minor issue. For the most part my problem is fixed. Thanks for the help. If you have an idea on how to fix this last minor detail let me know, but like i said, its not important.
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