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

Linking cells together as one record?

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


Joined: 27 Apr 2008
Posts: 19

PostPosted: Tue Apr 29, 2008 9:08 am    Post subject: Linking cells together as one record? Reply with quote

I have a cell with a date reference in it. I want to enter various info into a few cells below it, i.e. so that the data comes under the date.
The problem i have is finding a way for the data cells to stay grouped with the date cell as the days of the month go by and the date cell moves across the page.
At the moment, i have the date cell moving everyday but I can't find a way to make the data cells move with it.
Clear as mud i suspect, but if it does make sense I appreciate any suggestions.
Back to top
View user's profile Send private message
Mr5o1
Power User
Power User


Joined: 05 Dec 2007
Posts: 52

PostPosted: Tue Apr 29, 2008 5:23 pm    Post subject: Reply with quote

perhaps I've misunderstood you.. but formulas are great for referencing, and processing data elsewhere in the spreadsheet, but they are not so good at moving data around, which seems to be what your trying to do?

Quote:

The problem i have is finding a way for the data cells to stay grouped with the date cell as the days of the month go by and the date cell moves across the page.

How does the date cell "move across the page"? If you have something like:
A1: =TODAY()
B1: =A1+1
etc
then the cell contents isnt really moving at all, the same formulas are just returning a different value when you open the sheet on a different day.

If I've understood you correctly, (and I'm not sure I have) then the only reason you would want to do this is because if you put the current day on the right-most column you will need to scroll over to it every time you open the spreadsheet? If thats the case, there's a couple of workarounds you might be interested in:

1: if your using a different sheet for every month then is it possible to list days by row rather than column? (down rather than across) That way when you open the sheet you'll be able to see the entire month at once, no scrolling required. (I'm sure you've already thought of this.. just thought I'd mention)

2: you could use a hyperlink in cell A1 or something to take you straight to the column for the current date. check out this thread...
http://www.oooforum.org/forum/viewtopic.phtml?t=70921&start=0&postdays=0&postorder=asc&highlight=hyperlink
If your having trouble with the hyperlink, then let us know the cell range of the date's, and the sheetname, and I'll help you out.

not sure if I've been any help (whatsoever!) haha.
Back to top
View user's profile Send private message
vinny2984
General User
General User


Joined: 27 Apr 2008
Posts: 19

PostPosted: Thu May 01, 2008 11:17 am    Post subject: Reply with quote

You nearly have it but I knew i wasn't explaining myself very well, so i;ll try again.
Your right, it's not actually moving cells, it's the same formula updating with the date.

In row 1.
A1 = (EOMONTH(TODAY();0)-TODAY()+1)+(TODAY())-(DAYSINMONTH(TODAY()+1)) Which gives me the current Month
A2=DAYSINMONTH(A1)
Gives the number of days in that month
Then B1=TODAY() giving me day of month
and then from C1 to AF i have this formulat to give me the remaining days of the month
=IF(OR(B1="";B1=DAYSINMONTH($A$1)+($A$1-1));"";B1+1)
No doubt there are better ways, I'm very new to this and by no means effecient with formulas or macro writing yet.

The problem is, below each day of the month, I have a range of cells containg variuos data i enter, diary entries for example, and the cells are colour coded, which I've done with a macro as the colour coding is date dependent too.

When i open the spreadsheet, the present day appears in B1 and yesterday is gone forever, all going to plan so far.
But I want the data i enter in the cells for example 3 days time, to still be under that date when tomorrow comes and all the cells "move" 1 cell to the left. I know they aren't moving, they're just recalculating, but i can't workout how to make my data cells do the same as my date cells. i.e the data stays in the exact same cells as i put it and the dates change there position daily.
Does that make sense?

My macro skills aren't good enough yet to conquer this one, but i thought there may be another way.
If you can help me out that would be great, and save whats left of my hair.
Back to top
View user's profile Send private message
Mr5o1
Power User
Power User


Joined: 05 Dec 2007
Posts: 52

PostPosted: Thu May 01, 2008 4:24 pm    Post subject: Reply with quote

i see... I dont believe this is possible. formulas are great for processing data within a spreadsheet, but what your trying to do is to edit that data, or the spreadsheet itself, which formulas simply arent designed to do. Macros are a possibility(??), but I personally have no experience with their usage.

If I were you, I wouldnt use formulas for this at all.. I'd put todays date in A1, tomorrows in B1, drag them right across the page, enter diary entries under each date.. then every time you open the sheet, right click on the column header and select "Delete Column".

Its not a fancy solution, but its simple, elegant, and requires only two mouse clicks. Over the course of 1 year, performing this 1-second task will require 6 minutes and 5 seconds of your time (and minimal hair loss).. vs how ever much time & hair you've already used up Very Happy

however, If you have some experience with macros then it shouldnt be too difficult to write one to delete column A where its date is less than todays?
Back to top
View user's profile Send private message
vinny2984
General User
General User


Joined: 27 Apr 2008
Posts: 19

PostPosted: Thu May 01, 2008 11:55 pm    Post subject: Reply with quote

Many thanks for your reply. Firstly, its confirmed to me that I'm wasting my time trying to achieve it with formulas which has boosted my faith in my skills as i struggling to find a way.
The manual deletion of the first column is not an option as several people will use this sheet (if i ever finish it) and it has to be tamper proof and idot proof, so i need to automate any changes other than the data entry.
But secondly, your suggestion of using a macro to delete the first column is definately a winner and i think its within my skills level.
many thanks for that suggestion, with all my brainstorming I just hadn't seen it.
Back to top
View user's profile Send private message
J_Eduardo
General User
General User


Joined: 10 Apr 2008
Posts: 10

PostPosted: Fri May 02, 2008 5:03 am    Post subject: Reply with quote

I think you are trying to make a relational database with calc. Thiw is not possible.
Repeat de date in each row below.
Or use OOBase.
Back to top
View user's profile Send private message Yahoo Messenger MSN Messenger
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