EXCEL spread sheet help

Originally Posted by cajunwoodrick

I have a little spreadsheet i am trying to help a friend with, I am learning also here it is .We will say that in column B there is acaluculation that subtracts the day total from yesterday and that number is put in column J each day automaticly .And this number changes everyday the one in J .I want this number to automaticly be put in cell let say K2 from the that days j cell number does this make any since.I just want what ever todays total is from Column J to automaticly show up in K2 ,and so on tomorrow to change with that total from column J 30day in a month I want it to k2 change 30 or 31 time a month thanks for any help.I will have 30 or 31 entrys in column J and that numbers auto in K2 Thanks for any help.

Hi,

in J2 put

=IF(B2="","",OFFSET(J2,-1,0)-B2)

and formula fill that to at least J32, then in K2 put

=OFFSET(J2,(COUNTA(J2:J32)-COUNTBLANK(J2:J32)-1),0)

You will need to make 0 (zero) entries in B where applicable to trigger the column J / K display.

hth
---

Originally Posted by cajunwoodrick

Thanks very much Bryan I will try this and Happy new Year

It works for me -


Happy new Year -

and thanks for the response.
---

added note - formula fill is described at http://www.mvps.org/dmcritchie/excel/fillhand.htm
---

Originally Posted by cajunwoodrick

Bryan thanks very much for the help but B column is where he puts his sales he enters that daily and manually,Maybe I did not understand I need to make it zero

This is what we borrowed from somebody and trying to make it work for us
Column A has this formula in a6 threw thirty day =sum(a7+1)
Column B is where he enters manually the sales today and then the next keeps on for the rest of the month.
Column D has the formula =B5-B6
Column J has =E6-D6 I guess you would use this for a crew are B crew and that number for today sales in J is the number I want to show in K2 today sales might be 100.oo that would go in to k2 tomorrow it might be 75.00 and that number would change to 75.00 I hope I am not driving you crazy with my excel challange brain Thanks again for your time.

Hi,

should be as per the attached, but
"Column A has this formula in a6 threw thirty day =sum(a7+1)"
- the formula should be the row above +1

"Column B is where he enters manually the sales today and then the next keeps on for the rest of the month"
- yes, daily sales are entered

Column D has the formula =B5-B6
- I have absolutely no idea why you would subtract todays sales from tomorrows sales.

Column J has =E6-D6 I guess you would use this for a crew are B crew
- I have no idea what you have in column E
- the term 'a crew' has no meaning in context, did you mean 'accrue' ?
- or do you have two selling crews and if so why not enter separate figures?

Column J has =E6-D6 I guess you would use this for a crew are B crew and that number for today sales in J is the number I want to show in K2
- the formula will show, in K2, the current J-column figure, the J-column being a running stock figure.

hth
---

Originally Posted by cajunwoodrick

Thanks for all your help and time I really found out how much I don't know about excel but I am really going to try and learn this I think what you did will be close to what he wanted thanks again for all your help.And Happy New Year.

Hi,

ok - if he uses column A for the date, then columns E through I are usable for other things.

For the formula, you need to Formula Fill columns D and J down to cover the data entered in the B column, Formula Fill is discussed at
http://www.mvps.org/dmcritchie/excel/fillhand.htm

Also, the formula at K2 would be better as

=OFFSET(J$6,COUNT(J$6:J$6000)-1,0)

as this would not require change as more data were added.

hth
---