Creating Summary Report on Seperate Sheet

[onlycatfud]

Im hoping this really isn't as complicated as I've found it to be, but
it's 4am and I don't seem to be getting very close.

I know 0 VBS stuff, so that may be a quick answer for me to learn a
little, but here is the general idea.

I have a spreadsheet with two sheets, the first is a checklist, where
you can enter numbers that correspond to the items that apply to you.
At which time formulas derive other information.

For example, column A is how much, on a scale of 1-10 the person likes
pie, and then a formula in column C decides how much that will cost the
person.

So a user could enter.

A B C
1 - I like Pie = SUM(A1*5)
0 - I like Cake = SUM(A2*4)
0 - I like Cookies = SUM(A3*1)
1 - I like Candy = SUM(A4*1)
2 - I like Chocolate = SUM(A5*2)

Now, here is what I would like to do. Because the person chose they did
not like cookies or cake, that a report be generated on the second
sheet saying simply.

I like Pie = 5
I like Candy = 4
I like Chocolate = 10

In other words, IF they enter 0 or the item does not apply to them,
that it would be left out of the report...

My file uses ALOT of seperate options and is attached below.

(In case anyone is really interested, it automatically calculates the
price to repair a mech for the Battletech miniatures game system.)

File is attached with some notes on what I had been trying.

Attachment filename: damage calculator.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=396507

 

[Bob Phillips]

Try formulae like

=IF(SUMIF(Sheet1!B:B,"I Like Pie",Sheet1!C:C)<>0, "I Like Pie =
"&Sheet1!C:C,"")

By the way, in the formula
= SUM(A1*5)
The SUM is superfluous, you only need
=A1*5

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Ooops, that should read

=IF(SUMIF(Sheet1!B:B,"I Like Pie",Sheet1!C:C)<>0, "I Like Pie =
"&SUMIF(Sheet1!B:B,"I Like Pie",Sheet1!C:C),"")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Try formulae like

=IF(SUMIF(Sheet1!B:B,"I Like Pie",Sheet1!C:C)<>0, "I Like Pie =
"&Sheet1!C:C,"")

By the way, in the formula
= SUM(A1*5)
The SUM is superfluous, you only need
=A1*5

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[onlycatfud]

Thank you for the help, its given me alot more to take in and try with
the SUMIF command, but I can't say I quite got the hang of it.

And I am still somewhat confused :-\

What I have been trying to do, and this is an example from the file
itsself:
<tt>
-B- -C- -D- -E-

1 Streak SRM 2 Cost: 15,000
0 Streak SRM 4 Cost: 0
0 Streak SRM 6 Cost: 0
1 TAG Cost: 50,000
1 Ultra AC/2 Cost: 120,000


And I want the result to be:

Sheet2!

-A-
Streak SRM 2
TAG
Ultra AC/2

I have been attempting this, on the same page (i'll worry about
seperate sheets once i get down how it works)

In column F, for example, right next to the item, i have the
formula...

Cell F2
=IF(B1=1;C1;IF2)

Basically, If they checked the item B1=1, then display the name of the
item, otherwise, use the cell below (which is the same formula moved
down).

This works perfectly with this exception- The cell below ALSO displays
the item above, for example.

Sheet2!
-A-
Streak SRM 2
TAG
TAG
TAG
Ultra AC/2

I've tryed solving this by making another argument saying IF the cell
above yourself is NOT the same, then display it, otherwise move on (yet
again).

But alas I don't think I have quite figgured it out and got lost in my
own syntax, here is what I came up with. Again, this is all more
explained if you download the attached file.

 

[Bob Phillips]

All you seem to need is

Sheet2!A1: =IF(Sheet1!B1<>0,Sheet1!C1,"")

and copy down.

It will leave blank rows where the value is blank


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[onlycatfud]

Then is there like an easy command I am completley unaware of that
condenses the rows that are blank to make my summary not look like:

-A-
Item 1
Item 2


Item 5

Item 7
Item 8

Item 9
...?

If so, yea, I really wasted 8 hours of headache and no sleep.

 

[Dave Peterson]

Manually???

You could even just use Data|Autofilter.

Copy your list and paste it.
Apply Data|filter|autofilter
use the dropdown to show just the blanks
delete those visible rows.
Remove the filter.

Or do the filter on the original data
Data|filter|autofilter
but filter on non-blanks
select the range
edit|goto|special|visible cells only
paste to the new location

======
and one more

copy the range
paste it to the new location
select the new range (whole column)
edit|goto|special|blanks
Edit|delete|Shift cells up
(or entirerow--if it's just a single column, there'd be no difference)

====
Via a macro?

J.E. McGimpsey just posted a reply in this thread:
http://google.com/groups?threadm=#[email protected]