software

I have a list of expenxe categories and by month along the top. How can I
write the sumif function based on two criteria from drop-down list(one list
is Expenses and the second list is the Months) and the third (Amount) cell
would be where I input an amount to be added into the table:
Expensesmonthamount
Fuel JAN_______Expenses JAN FEBMAR
Trucks/Auto
Fuel 348.23
Oils 35.99
Maintenance 298.33
Parts 59.21
DMV 745.87

--
so many functions...!?!?!?!?!?!?

If you are trying to fill a matrix of expenses down and months across
with data you type in 3 cells that won't work because any formulas in
the matrix will at all times reflect the input in your 3 cells unless
you have a copy of the matrix somewhere that only contains values.

So, your table contains the values of the copy table (by means of
formulas)
now input your data
the formulas will identify the field in your matrix to modify, take the
according value from the copy table, add the new amount, at which stage
you need to copy your table and paste--gt;special--gt;values it to the
other location.
Empty your input fields

start all over

It seems advisable to do the coyping and emptying by means of a macro
and a button.

Mind though that it is a fairly risky procedure since if you get
interrupted you would never know whether you copied or not. If you use
a button, however, there are ways to signal that.

example:

colourblue
monthjan
amount40
row2

originalcopy
janfebmrzjanfebmrz
green15971597
blue50741074
yellow391391Formulas:

row (B4): =MATCH(B1;A8:A10;0)
Matrix (C8) copied to all other cells down and across:
=IF(AND($B$2=C$7;$B$4=ROW($C8)-ROW($C$7));G8 $B$3;G8)
You may have to replace the semicolons with commas depending on your
local Windows setting for regional and language

HansOk, I don't think I explained myself better, sorry, my fault. At the top I
have a labeled drop-down list tied to the list of expense categories(Fuel,
Repairs etc...) next to it is another drop-down list tied to the months(JAN,
FEB, MAR, etc...) next to that is a cell labeled Amount with a cell below it
to input an Dollar amount for a maybe fuel receipt for the month of JAN and
it would add itself to that particluar cell. Then say the next receipt in
line is a receipt for a REPAIR for the month of FEB and it would atumatically
add itself to that cell intersecting REPAIR and FEB. I have seen some
questions similar but in reverse it seems on this site and I can't seem to
get it right. The cell intersecting an EXPENSE category and MONTH would
recognize an amount entered under AMOUNT and added to whatever was entered
previously. Something lif IF CATEGORY IS FUEL AND MONTH IS JAN THEN ADD
appropiate cell. I had something similar under access but I kinda needed to
be in excell and when I copy and try to past in excell it won't work. Thank
you Mr. flummi for your help.
--
so many functions...!?!?!?!?!?!?

Hi,

there's 2 ways to understand your design:

1. You have row 2 at the top to specify the type of expense, the month
and to enter a Dollar amount. Below that you have the expense
categories listed in a column say A4:A10 and the months listed across
say in B3:M3 and in e.g. B4:M10 (for 10 expense categories) you have
the accumulators for the expense amounts. Everytime you select a
category and a month and enter an amount in row 2 you want to add the
amount in the cell intersecting the expense category in A4:A10 and the
month in B3:M3.

This is what I described in my first post. It is not possible with
normal Excel formulas because in Excel you cannot have a formula like
=A3=A3 B4. This will result in an error message quot;circular referencequot;.
The only sensible way to oranize this seems a command button with an
associated macro that, if the button is clicked, adds the input amount
to the correct cell in your expense matrix.

2. You have kind of a journal design. columns A, B and C hold your
input data per line. Columns D:O have the amount in Column C in the
correct month. like in this example:

Expenses table
janfebmrzaprmai
categorymonthamount
Fueljan45,0045,00
Repairfeb112,50112,50
hotelfeb245,00245,00
tyresjan256,00256,00
Penaltymrz70,0070,00

Total301,00357,5070,00

The formula in D4 is simple: =IF($B4=D$2;$C4;0)
copied down and across as required.

This means, when you have a new expense you enter it in a new row, copy
the formulas from the previous row into the new one and that's it. Any
sums you would display at the bottom. If you want the total amount per
Category and month use a different area on the same sheet or a
different sheet and extract the information from your quot;journalquot;.

Does that make sense?

HansMr. Flummi
thank you for answering my questions. yes, I was getting a quot;circularquot;
answear. it works in access, but I have to keep tables for every expense and
I wanted to see the table and also input. it's just much easier to set up in
excell and much faster to set up.
so many functions...!?!?!?!?!?!?quot;flummiquot; wrote:

gt; Hi,
gt;
gt; there's 2 ways to understand your design:
gt;
gt; 1. You have row 2 at the top to specify the type of expense, the month
gt; and to enter a Dollar amount. Below that you have the expense
gt; categories listed in a column say A4:A10 and the months listed across
gt; say in B3:M3 and in e.g. B4:M10 (for 10 expense categories) you have
gt; the accumulators for the expense amounts. Everytime you select a
gt; category and a month and enter an amount in row 2 you want to add the
gt; amount in the cell intersecting the expense category in A4:A10 and the
gt; month in B3:M3.
gt;
gt; This is what I described in my first post. It is not possible with
gt; normal Excel formulas because in Excel you cannot have a formula like
gt; =A3=A3 B4. This will result in an error message quot;circular referencequot;.
gt; The only sensible way to oranize this seems a command button with an
gt; associated macro that, if the button is clicked, adds the input amount
gt; to the correct cell in your expense matrix.
gt;
gt; 2. You have kind of a journal design. columns A, B and C hold your
gt; input data per line. Columns D:O have the amount in Column C in the
gt; correct month. like in this example:
gt;
gt; Expenses table
gt; janfebmrzaprmai
gt; categorymonthamount
gt; Fueljan45,0045,00
gt; Repairfeb112,50112,50
gt; hotelfeb245,00245,00
gt; tyresjan256,00256,00
gt; Penaltymrz70,0070,00
gt;
gt; Total301,00357,5070,00
gt;
gt; The formula in D4 is simple: =IF($B4=D$2;$C4;0)
gt; copied down and across as required.
gt;
gt; This means, when you have a new expense you enter it in a new row, copy
gt; the formulas from the previous row into the new one and that's it. Any
gt; sums you would display at the bottom. If you want the total amount per
gt; Category and month use a different area on the same sheet or a
gt; different sheet and extract the information from your quot;journalquot;.
gt;
gt; Does that make sense?
gt;
gt; Hans
gt;
gt;

Hi,

You are welcome.

What I can offer is that you send me via email a spreadsheet how you
want it to look and I'll organize it for you.

Hans

am trying to design a template that I can use for a auto fuel expense sheet.
I need something that includes:- date, place, odometer reading, number of
kilometers, number of liters, cost per liter, number of liters per hundred
kilometers. I am a brand new user of excel and need all the help I can get.
Is thewre a template that I can download for this or will I have to make one.
Please help me.
Thank you.
Ron G

quot;flummiquot; wrote:

gt; Hi,
gt;
gt; You are welcome.
gt;
gt; What I can offer is that you send me via email a spreadsheet how you
gt; want it to look and I'll organize it for you.
gt;
gt; Hans
gt;
gt;

Hi Ron
Go to Cimjet
office.microsoft.com/en-us/te...s/default.aspx
Do a search for your template, select the one you like and most likely it
will need to be converted to metric.
If you need held to convert the form, will be more than happy to help.
Regards
Cimjet
quot;Ron Gquot; gt; wrote in message
...
gt; am trying to design a template that I can use for a auto fuel expense
gt; sheet.
gt; I need something that includes:- date, place, odometer reading, number of
gt; kilometers, number of liters, cost per liter, number of liters per hundred
gt; kilometers. I am a brand new user of excel and need all the help I can
gt; get.
gt; Is thewre a template that I can download for this or will I have to make
gt; one.
gt; Please help me.
gt; Thank you.
gt; Ron G
gt;
gt; quot;flummiquot; wrote:
gt;
gt;gt; Hi,
gt;gt;
gt;gt; You are welcome.
gt;gt;
gt;gt; What I can offer is that you send me via email a spreadsheet how you
gt;gt; want it to look and I'll organize it for you.
gt;gt;
gt;gt; Hans
gt;gt;
gt;gt;