Excel

[hungrylilboy]

Prob the wrong place to put this but never mind.

Anyone good at excel? Need some help

Question1) A1(cell/row) is a date eg 07/07/2004

how can i add a forumula to this cell so every 6 (so next one will be A7) down the date will increase by a day? Obviously =sum(a1+1) is a7 but how can i make it automatically do it?

Question2) How can i minimise a section? Each day on my table has various things. How can i minimise a day into one row. eg clicking the plus/minus sign on the side. I have seen it on other book but cant figure out how it was done.

[Storm]

A1)what do you mean by automatically? you not having to manually enter the formula (or copy pasting?)

if so, i dont think its possible

[Barbarossa]

OK, I don't know, but...

Start off like this:



        A
1    07/07/2004
2    =a1
3    =a2
4    =a3
5    =a4
6    =a5
7    =a1+1


Now highlight rows 2 to 6 and change the text colour to white (so you won't see them!

Then select lines 2 to 7 and copy them however many times you want.

To do the filtering, in the data menu set autofiltering on, then you will be able to select a date, the sheet will then only show the rows where that date is in column A.

I don't know if this is what you want, but hope I've been of some help!

[Storm]

whats the use of inserting that text into cells A2-A6??

[hungrylilboy]

autofiltering is good thanks.

other not so.

what i am looking for is something like this

a1 is 07/07/04, i want a7 to be 08/07/04 and so on.

but instead of me having to type them all in manually. i wanted to write a formula in a1. maybe give up

[Turkishjade]

Don't know if you gave up already, but based on you wanting every sixth row to add one to the date, you could do this:

A1 would be: =7/7/2004
A2...A999 would be: =A1+INT(ROW()/6)

Each cell would have to reference A1. You would have to format the whole column as a date. Hope that helped.

[hungrylilboy]

Originally posted by Turkishjade@7 July 2004 - 14:28
Don't know if you gave up already, but based on you wanting every sixth row to add one to the date, you could do this:

A1 would be: =7/7/2004
A2...A999 would be: =A1+INT(ROW()/6)

Each cell would have to reference A1. You would have to format the whole column as a date. Hope that helped.
doesnt seem to work.

a1 formula is 07/07/04

then am i highlighting everything from a2 down and copying A1+INT(ROW()/6) into the formula bar?

[Barbarossa]

Type the date into a1

Type =A$1+INT(ROW()/7) into a2

Then select a2 and press ctrl + c

The select the rest of column a down ad inifinitum and press ctrl + v...

[hungrylilboy]

Originally posted by barbarossa@7 July 2004 - 16:20
Type the date into a1

Type =A$1+INT(ROW()/7) into a2

Then select a2 and press ctrl + c

The select the rest of column a down ad inifinitum and press ctrl + v...
ok that kinda works but puts the same date for 7 cells before changing to the next date for 7 cells...i want it just to appear in every 7th..

[Barbarossa]

Oh well... ;)

[hungrylilboy]

Originally posted by barbarossa@7 July 2004 - 16:40
Oh well... ;)
:frusty: dont oh well! keep working goddamit. Seriously thanks for ur help guys

[Barbarossa]

LOL, sorry I couldn't be more help!

[Turkishjade]

Sorry, I haven't checked this thread for a couple of days.

I thought you wanted it to show 7 of the same date. To get what you want, use a simple IF statement:

I can't remember the exact formula you would need, since I don't use Excel anymore, but it would be the function MOD() or DIV(). Whatever the function is called, it works the same way. Let's say it's MOD.

MOD(ROW(),7) will tell you the remainder after dividing the ROW() by 7. If the number is evenly divisible by 7, then the remainder will be 0 and so will the function. So....

=IF( MOD( ROW() , 7 )=0, A$1+INT(ROW()/7) , "")

But then it kinda goes back to why didn't you just put in cell A1 "the date", go down 7 lines and put in cell A8 "=A1+1" and then just highlight cells A2..A8 and copy them over and over again to the rows below.

My way is technically correct, but it is inefficient. Anywayz, good luck.

[Turkishjade]

Anywayz, I helped you, now you help me: ;)

What's the best free image hosting site that I can use to update my profile with avatars and a signature pic?

[hungrylilboy]

Originally posted by Turkishjade@9 July 2004 - 14:57
Sorry, I haven't checked this thread for a couple of days.

I thought you wanted it to show 7 of the same date. To get what you want, use a simple IF statement:

I can't remember the exact formula you would need, since I don't use Excel anymore, but it would be the function MOD() or DIV(). Whatever the function is called, it works the same way. Let's say it's MOD.

MOD(ROW(),7) will tell you the remainder after dividing the ROW() by 7. If the number is evenly divisible by 7, then the remainder will be 0 and so will the function. So....

=IF( MOD( ROW() , 7 )=0, A$1+INT(ROW()/7) , "")

But then it kinda goes back to why didn't you just put in cell A1 "the date", go down 7 lines and put in cell A8 "=A1+1" and then just highlight cells A2..A8 and copy them over and over again to the rows below.

My way is technically correct, but it is inefficient. Anywayz, good luck.
thanks

Hungrylilboy
http://img69.photobucket.com/albums/v211/hungrylilboy/Footsteps_2a.gif