The only way I know to ask this - with an example -- Counting & Inserting (Easy)

  • Thread starter Thread starter BaLLZaCH
  • Start date Start date
B

BaLLZaCH

I have a project that adds numbers across the rows, then adds the total
down the colums ... I use it for billing, for each apartment that
work on. It tells me how many wires I ran in total, but breaks it dow
between each apartment.

Here is what I need help with: There are 3 givens, static numbe
references that are always the same ...

A-B-C-D-E-F (Rows & Colums)
2-2-1-1-1=7
2-2-1-1-0=6
2-2-0-0-0=4
___Total=17

The rows represent different apartments, where as the colums are th
wires ran (different rooms) - Note that row F is the total wires. Ther
are 3 apartment types (3 bed, 2 bed, and 1 bed) which have either
wires, 6 wires, or 4 ....


Right now the spreadsheet is set up to add the numbers in A-E to give
total in F, but since I know that the numbers are static and always th
same, I want to be able to enter, say, "7" in row F *AND THE FUNCTIO
AUTOMATICALLY INSERT THE COROSPONDING NUMBERS IN ROWS A-E .... Or fo
example, enter "4" and have that referenced as well.*


ALSO, I want to leave this spreadsheet as it is as far as "count
because if I need to go back and change a few wires, it will stil
count the correct numbers and totals ...



Did I forget to mention that I am a newbie ? I have only about 3
minutes of excel experience as of now :-)


Did that make any sense ? Here is a copy of the spreadsheet, if yo
look at it you will see what I mean .... Like I said, I just want to b
able to plug in the "total" directly and have the spreadsheet inser
the other numbers for me....


Thanks all !!!!!!
I would be extremely grateful if somebody could please explain this t
me. Thanks again !!!!


Zac

Attachment filename: example.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=43543
 
Hi Zach
what is the logic for entering '1' or '2' in columns A-E depending on
the value in column F?
One way to do this could be the use of the IF function. e.g. enter the
following in A1:
=IF(OR(F1=7, F1=6, F1=4),2,"")
or for D1 enter
=IF(OR(F1=7, F1=6),2,"")
if I understood your example data correctly

Frank
 
Whats up frank...


Right, an "IF" function would work ... (Logically) - I have done V
programming and that sounds logical, like "On CLICK, IF f=7 then a=2
b=2, etc.. etc)


Anyways, the reason behind wanting to change the numbers is simple ...

I install outlets in apartments - Some apartments are 7 bedroom, som
6, some 4. Basically, every apartment, be it 7, 6, or 4 all have th
same layout.

When I bill for my work, i have to show how many outlets I installe
per eash apartment, and in what rooms. But see the logic behind it i
that they are always the same .... (Example, 1 bedrooms always have
outlets and they are always 2 in the living room and 2 in the master)

So instead of having to type "2", then "2" again, then "0" "0" "0" i
would be great if I could just enter "4" in the "total" column and th
text string of "2, 2, 0, 0, 0" automatically be entered...


Did that make sense ? Sometimes I try to explain things way too i
depth :-)


But anyways thats the logic behind wanting to change the rows o
numbers by only changing the totals .... (Basically, when I write dow
my work at the jobsite, I dont even keep count of what rooms got ho
many outlets, I only keep count of the total outlets, because like
said I know that 3 rooms have 7 outlets, 2 rooms have 6 outlets, and
room has 4)


Cool thanks for you help, Ill try what you said might work and you hav
any other ideas please let me know buddy !!!

Thanks man !
Zac
 
Frank, any ideas ??? I appriciate your help, and yes, it did work, bu
not exactly what I was talking about ...


See, there is a catch - I need 2 arguments per column ....


EXAMPLE...


The "IF" thing works, so "IF" F=7 or F=6 or F=4, then A = "2" ..
Right, that works, BUUUUUUTTTTTT

Look at "e" on the message ... If F=7 then e = 1, BUT if F=6 (or 4
then E=0 ...

How do you incorperate 2 arguments on the same cell ????



Wow, this is starting to look WAY more complicated than it really i
... Its not that hard. what i am trying to do, its just that the onl
way I know to explain it is give you all the details so that you kno
where I am going with this ...



Ok, check the attatched example file ... In the file you can ad
numbers and see that it adds the sum in the right (total) ... So, i
you can imagine that if you put the TOTAL in FIRST, and had the scrip
insert the corosponding numbers (that are pre-set) into the fields tha
you tell it to .... That way, if the total = 7, then it insert
"2,2,1,1,1" or if the total = 4 then it inserts "2,2,0,0,0"


Cool ???? Sorry I cant explain in laimans terms, i like to giv
detailed explinations in hope of getting detailed answers :-)


Thanks again man ...

Peace
Zach



ORIGINAL MESSAGE ---------




Re: The only way I know to ask this - with an example -- Counting
Inserting (Easy)

Hi Zach
what is the logic for entering '1' or '2' in columns A-E depending on
the value in column F?
One way to do this could be the use of the IF function. e.g. enter the
following in A1:
=IF(OR(F1=7, F1=6, F1=4),2,"")
or for D1 enter
=IF(OR(F1=7, F1=6),2,"")
if I understood your example data correctly

Frank
I have a project that adds numbers across the rows, then adds the
totals down the colums ... I use it for billing, for each apartment
that I
work on. It tells me how many wires I ran in total, but breaks it
down between each apartment.

Here is what I need help with: There are 3 givens, static number
references that are always the same ...

A-B-C-D-E-F (Rows & Colums)
2-2-1-1-1=7
2-2-1-1-0=6
2-2-0-0-0=4
___Total=17

The rows represent different apartments, where as the colums are the
wires ran (different rooms) - Note that row F is the total wires.
There are 3 apartment types (3 bed, 2 bed, and 1 bed) which have
either 7
wires, 6 wires, or 4 ....


Right now the spreadsheet is set up to add the numbers in A-E t give
a total in F, but since I know that the numbers are static an always
the same, I want to be able to enter, say, "7" in row F *AND THE
FUNCTION AUTOMATICALLY INSERT THE COROSPONDING NUMBERS IN ROWS A-E
.... Or for example, enter "4" and have that referenced as well.*


ALSO, I want to leave this spreadsheet as it is as far as "count"
because if I need to go back and change a few wires, it will still
count the correct numbers and totals ...



Did I forget to mention that I am a newbie ? I have only about 30
minutes of excel experience as of now :-)


Did that make any sense ? Here is a copy of the spreadsheet, if you
look at it you will see what I mean .... Like I said, I just want to
be able to plug in the "total" directly and have the spreadsheet
insert
the other numbers for me....


Thanks all !!!!!!
I would be extremely grateful if somebody could please explain this to
me. Thanks again !!!!


Zach

Attachment filename: example.zip
Download attachment:
http://www.excelforum.com/attachment.php?postid=435438 ---
Message posted
Click to expand...


Report this post to a moderator | IP: Logged
02-08-2004 07:40 PM



BaLLZaCH
Junior Member

Registered: Feb 2004
Location:
Posts: 2


Whats up frank...


Right, an "IF" function would work ... (Logically) - I have done V
programming and that sounds logical, like "On CLICK, IF f=7 then a=2
b=2, etc.. etc)


Anyways, the reason behind wanting to change the numbers is simple ...

I install outlets in apartments - Some apartments are 7 bedroom, som
6, some 4. Basically, every apartment, be it 7, 6, or 4 all have the
same layout.

When I bill for my work, i have to show how many outlets I installed
per eash apartment, and in what rooms. But see the logic behind it is
that they are always the same .... (Example, 1 bedrooms always have 4
outlets and they are always 2 in the living room and 2 in the master)

So instead of having to type "2", then "2" again, then "0" "0" "0" it
would be great if I could just enter "4" in the "total" column and the
text string of "2, 2, 0, 0, 0" automatically be entered...


Did that make sense ? Sometimes I try to explain things way too in
depth :-)


But anyways thats the logic behind wanting to change the rows of
numbers by only changing the totals .... (Basically, when I write down
my work at the jobsite, I dont even keep count of what rooms got how
many outlets, I only keep count of the total outlets, because like I
said I know that 3 rooms have 7 outlets, 2 rooms have 6 outlets, and 1
room has 4)


Cool thanks for you help, Ill try what you said might work and you have
any other ideas please let me know buddy !!!

Thanks man !
Zach

__________________
Sig ? How can I sign the computer screen ?
 
Hi Zach

You could set up a table on another sheet in reverse order to your data
shown
7-2-2-1-1-1
6-2-2-1-1-0
4-2-2-0-0-0
and use this as your lookup table

For A2 on your sheet1 enter
=VLOOKUP($F2,Sheet2!$A$1:$F$3,COLUMN()+1,0)
Copy this across through cells B2:E2, then copy the whole row down through
rows 3 and 4

As you enter the totals in F, the other values will be created.
column and the
text string of "2, 2, 0, 0, 0" automatically be entered...
Click to expand...

If you really did want a "Text string", of the results then then it would
be
=A2&", "&B"&", "&C2&", "&D2&", "&E2
 
Frank, any ideas ??? I appriciate your help, and yes, it did work,
but
not exactly what I was talking about ...


See, there is a catch - I need 2 arguments per column ....


EXAMPLE...


The "IF" thing works, so "IF" F=7 or F=6 or F=4, then A = "2" ...
Right, that works, BUUUUUUTTTTTT

Look at "e" on the message ... If F=7 then e = 1, BUT if F=6 (or 4)
then E=0 ...

How do you incorperate 2 arguments on the same cell ????
Click to expand...

put the following formula in E1:
=IF(F1=7,1,IF(OR(F1=6,F1=4),0))

Frank
 
Cool Frank, thats awsome ...



One more question bro if you dont mind ....


That works perfect, BUT now the "count" feature dont work - meaing, i
you manually enter all the info, it adds it up into the SUM in th
TOTAL box ... Also, if you manually enter the TOTAL it enters th
appropriate numbers into the other fields, HOWEVER, now, if yo
manually change one of the fields it no longer "SUMS" the answer int
the total. Its like, when you manually change the total, it removes th
SUM feature ...


Is there a way around that or do I just have to deal with it ?


Again, if you check the original attatched file you can see what I a
tlking about, when you change the total manually it removes the "sum"
function..


anyways, thanks VERY much for your help, you answered exactly what
wanted to know ... Let me know if you can help with the SUM, or hit m
back if you got questions on what I am talking about ..



Thanks man !!!!!!!
Zac
 
Cool Frank, thats awsome ...
One more question bro if you dont mind ....


That works perfect, BUT now the "count" feature dont work - meaing, if
you manually enter all the info, it adds it up into the SUM in the
TOTAL box ... Also, if you manually enter the TOTAL it enters the
appropriate numbers into the other fields, HOWEVER, now, if you
manually change one of the fields it no longer "SUMS" the answer into
the total. Its like, when you manually change the total, it removes
the SUM feature ...


Is there a way around that or do I just have to deal with it ?


Again, if you check the original attatched file you can see what I am
tlking about, when you change the total manually it removes the "sum"
function..


anyways, thanks VERY much for your help, you answered exactly what I
wanted to know ... Let me know if you can help with the SUM, or hit me
back if you got questions on what I am talking about ..
Click to expand...

Hi Zach
you cannot have both (entering a value in the Total box and having a
sum formula in this box). You can simulate this with a VBA procedure
(processing work_sheet change) but I wouldn't do this. I think you have
to decide which procedure you want :-)

Frank
 
Back
Top