formula help!!!

Well this will be good for a laugh. I haven't programmed for a while, but I used to enjoy it, so I'm going to try to help you. Lord knows, you need somebody who is currently doing this work. You're in Excel, right? I don't know what your syntax is, but this is how the logic should go, I think.

On problem 1, do you mean that only certain stabilized items from A go to form B? That seems like it would be relatively easy, you'd have B read the description and if it were equal to one of a set of constants (like hats) then you perform your summary function. You're either keeping a temporary running tally or posting the activity and then summarizing (maybe on a temporary chart or in a spare corner of one of these charts using white print so it doesn't show up), iterating down the column of descriptions until you come to a blank cell in the column (or two blank cells, which would probably involve a simple switch).

Or did you mean only certain items that have activity? In which case you may need to add an index code to A at the time of activity (based on whatever your criteria might be) so that B can look for only those particular indexed items when it is time to summarize.

On problem 2, the only way I can think of to do what I think you want, which is to have the most recent entry from any one of those ten cells be put into the A11, would be to have an almost identical final routine for each of those cells. In A1 if you move something there, then you move it to A11, in A2, you do the same thing, etc. It would be a running check of activity and self-correcting. The most recent change in the cells A1 through A10 superimposes whatever was in A11 as soon as the change is made.

excellchallenged

Not understanding anything of the above, I just say: "welcome to A2K"!

formula help
Piffka,thanks for taking time to help. As I stated I am excel challenged.....

Problem 1.

Form A is really like a check book. Each month will start out new. All the cells in the description column are blank. As I fill in the cells with daily transactions. Some of the entries will be items that are items in columns on form B (activities i.e. soda machine, hat sales). I need the activities on form B to recognize when an entry is made in the description column (hat sale). Then tally the type of transaction (purchase,sale) in the appropriate row on form B. In other words if I have 5 hat sales entered in the descrip column. I need the hat column on form B to know that 5 hat sales were made and for how much. I hope I am making sense?

Problem 2.

Yes, I need the amt of the last entry made between A1:A10 to be reflected in A11. This column would be for an inventory sheet. It is the latest purchase price of a item. I need A11 to show the latest price paid. Then it will be multiplied by another cell containing the total items on inventory. Giving me the total value of inventory, based on the last purchase price.

Again thanks for the help. Sorry if I am not making sense.

Sounds like you would be better off doing this in Access if you have that available.

You might also check out some Expense Report type templates. That is basically what you are asking for. You want to itemize your expenses/income, sort it by category similar to that of a Chart of Accounts and have it give a total by category of the expense/income for that month and transfer it to a profit and loss statement.

The Quicken accounting software for small businesses might do the trick for you.

Hmmm. I'm sure Butrflynet knows LOTS more about this than I do. I just like the logic problems.

For problem A: seems to me that Excel provides for a pull-down menu of variables. You could construct that to your needs, ie. Hat Sale, Hat Purchase, etc. So you'd be, in effect, sorting at the time of entry. Then your summarizing program would just look for each specific variable. I remember we had problems with our Quicken program when we'd use two descriptions that mean the same thing. Intuitively, it is the same to a human, but it wouldn't be thought that way by the program. So it is pretty important to have that pull-down menu select the exact phrase.

For problem B, I still think you should move the latest purchase price into A11 at the time you enter the purchase in A1:A10. The only other way I can see it (remember, I haven't done this for a while and Excel may have made lots of fabulous changes) would be a long-winded formula checking for the latest date and then looking to the amount.

I'm hoping that somebody who works a lot with Excel will come here and give you REAL HELP.

(Did I mention welcome to a2k?? Cheers!)

Let me start with Problem #2.This is complicated but it can be done. Essentially, you are looking for a number of nested if/then formulas.

Try this in the A11 cel: =IF(A10>0,A10,(IF(A9>0,A9,(IF(A8>0,A8,(IF(A7>0,A7,(IF(A6>0,A6,(IF(A5>0,A5,(IF(A4>0,A4,(IF(A3>0,A3,0)))))))))))))))

Essentially, what the above formula asks is, if A10 is greater than 0, use the value for A10. If the test fails and A10 is not greater than 0, move onto if A9 is greater than 0.

Now for the first question:

I'm not 100% certain of what you want to do. It appears that you want to total up various figures on the first page in a summary format on the second. Is that correct? If it is, then you can simply use = signs. E. g. wherever you want the total to be, type =Sheet1!A6+Sheet2!A2 <-- this will total the A6 cel in Sheet1 to the A2 cel in Sheet2. Of course you'd change the formula based on sheet names and cel numbers.