Jump to content

Archived

This topic is now archived and is closed to further replies.

gassernomore

Excel spreadsheet formulas

Recommended Posts

I NEED help from someone smarter than I with an Excel formula! I have my spreadsheet all completed except one formula. I have everything tied to a date and am wanting to save each days figures on the same spreadsheet. I was hoping that if I "saved" the sheet after each day it would keep the figures there that I wanted. But, once I put in a new date all of the info is lost. Anyone have any ideas?

Share this post


Link to post
Share on other sites

So your saying if you put in some values like what you got at the grocery store today and it formulates things like tax on each item, you want it to remember that for each time you save as a separate day? As in, if you put in something different the next day, you want it to automatically list that day and the taxes for that day upon saving?

Share this post


Link to post
Share on other sites

I am pretty good at Excel, no promises, but I can try to help. What have you gotten done sofar? Can you post some screenshots and examples of the equations? You can show the formulas with crtl+~

Share this post


Link to post
Share on other sites

[ATTACH]3320[/ATTACH] Here it is. I have it doing sales and inventory for me. The date is in cell "b2". Thanks for your help!

Ok. There are a couple things I can do... The easiest thing to do by far would be to just copy from C14:N14 and paste the totals into its correct date row below, it would require a marginal amount of manual entry and the rest of the sheet should still work. I will see if I can get a macro to work for this that would transfer to your computer, although I will say that copy and paste will by far be the easier way to go. update: I don't know of a way to make the lower rows keep their individual values when you change the date to the next day without using something like Access or making something really complex. My current thought is to automate the copy and paste operations with a macro. So basically, if I can figure out some of the details it would just copy the totals line to the running totals sections. Getting the daily totals shouldn't be too bad, since thats just two total copy and paste operations. Getting the individual rows 08, 09, 10... might take a bit more effort. The sheet looks good overall, and should end up pretty well for you. What I have been playing with sofar, you can do with the "Record Macro" function in Excel. Its pretty cool, and there are a lot of good help articles in excel for macros. I don't know a whole lot about it as most of what I know is self taught, but I will see what I can come up with. If this looks too complicated, or not like what you are looking for please let me know. Sub Macro1() ' ' Macro1 Macro ' ' Range("C14:N14").Select 'select the values Selection.Copy ' Range("C26").Select ActiveSheet.Cells(28, 3).Select 'note on above... select the place to paste, i need to figure out how to reference this to the date, that way it pastes to the correct date each day. Then repeat for lower sections. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' ActiveWindow.SmallScroll Down:=12 ' Range("C23:N23").Select ' Application.CutCopyMode = False ' S election.Copy ' ActiveWindow.SmallScroll Down:=15 ' Range("C61").Select ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ' :=False, Transpose:=False ' ActiveWindow.SmallScroll Down:=-99 End Sub

Share this post


Link to post
Share on other sites

Are you going in each day and changing all the information in the manual entry fields? I have several spreadsheets set up with daily totals, or totals, but I have pages set up for each day and then all dumped into one master page that gives me the totals that I need to see. Pretty much I always make 31 of the input pages, then tie them into 1 master page and then tie the input pages together if I need information from the prior day to dump into the next day. It seems on this page you pretty much have an if, then formula that each time you change the if it then gives you the information for the then, but each time you change the if it gets rid of your prior information. I'm sure there is some complex formula that may take care of that, and the way I always do it probably takes up more space, but that's what external hard drives are for! Looks like the post above me has it better figured out, but I always do it possibly the long way and then just copy my input page however many times I need to so I don't have to input the info 31 times.

Share this post


Link to post
Share on other sites

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

' ActiveWindow.SmallScroll Down:=12

' Range("C23:N23").Select

' Application.CutCopyMode = False

' S election.Copy

' ActiveWindow.SmallScroll Down:=15

' Range("C61").Select

' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

' :=False, Transpose:=False

' ActiveWindow.SmallScroll Down:=-99

End Sub

I don't understand what all this is! LOL I still use an old flip cell phone! If you get it to work that would be great. Thanks for working on it.

--- Update to the previous post...

Are you going in each day and changing all the information in the manual entry fields? I have several spreadsheets set up with daily totals, or totals, but I have pages set up for each day and then all dumped into one master page that gives me the totals that I need to see. Pretty much I always make 31 of the input pages, then tie them into 1 master page and then tie the input pages together if I need information from the prior day to dump into the next day. It seems on this page you pretty much have an if, then formula that each time you change the if it then gives you the information for the then, but each time you change the if it gets rid of your prior information. I'm sure there is some complex formula that may take care of that, and the way I always do it probably takes up more space, but that's what external hard drives are for! Looks like the post above me has it better figured out, but I always do it possibly the long way and then just copy my input page however many times I need to so I don't have to input the info 31 times.

Yea, I would just be changing the numbers thru line 22. Your right about the "if" formula, it does get rid of the info when you change the date. That is where I am stuck! I thought of making the 31 pages as well but (a) I thought this would be easier (b) I was trying to save time with entering the info © I am not the one that will actually be entering the info onto the spreadsheet, so I was trying to make it less confusing for the help!

Share this post


Link to post
Share on other sites

Okay I just wanted to make sure I understood what you were trying to accomplish before I got too deep into it. If you figure out a way to make it work I'm very interested, because all of mine I have the 31 pages made for and then would have pretty much everything you have from line 26 down on a separate page that is just for information dumping. Gotta head out of the office for a while not but I will take a look at it again this afternoon, and also would like to see how you get it fixed if you get it to working.

Share this post


Link to post
Share on other sites



×