SUM of Changing source value, pls help!!!

[americasrecycler]

I have two sheets in a work book

SHEET ONE providing source data ex: A1 to A3 is changing and so is SUM A1:A3
(example are three sets of A1:A3 data)
And another factor lets say B1 is also changing.

A A A---------B
1 1 2 3 4
2 1 2 3
3 1 2 3
4 3 6 9

SHEET TWO ( IF F1=B1 then A1='sheet1' A4 .... and so on for B1='sheet' A4,
C1='sheet1'A4 ... REMEMBER that B1 and F1 is constantly changing so that the
data (A4) being plugged appropriately )

A B C................F
1 3 6 9................4

So if I do Isnumber, only the last (C1) shown as TRUE, all other isnumber=0
(even though it still shows the number)

Now I want to sum A1:C1 (sheet 2), it only give me Zero "0" , IS THERE ANY
WAY that I GET AROUND and SUM them ???

 

[Sheeloo]

You are using IF formula... what is the ELSE path in IF F1=B1 then
A1='sheet1' A4?
I mean what do you have when F1 is not equal to B1?

SUM only sums up numeric values.. What you are getting as a result of your
formula is a text value hence the 0 sum (your isnumber() points to that...

Share the complete IF statement and values in the cell it refers to (one set
only)

 

[americasrecycler]

Hi Sheeloo,

ELSE Path for F1=B1 is A1=A1 (that's why the number shows but isnumber=0)

thanks

 

[Sheeloo]

Try testing the numbers on Sheet1 with ISNUMBER....

I suspect they are stored as text...

You can manually enter the numbers and test your formula

 

[Max]

It's a pity that you seem to have deserted your earlier thread despite the
patience of 2 responders who hung in there with you to try and get you going:

http://tinyurl.com/cry7ba

My last response to you in that thread was:

.. still, the results are zeros.

Believe you may not be array-entering the formulas correctly ..

In the formula bar, did you see the curly braces { } inserted by Excel
around the formula, viz. it should look like this:
{=SUM(A1:A4+0)}


If you don't see the curlies, then it wasn't confirmed correctly. Re-click
inside the formula bar, press CTRL+SHIFT+ENTER to confirm it, then check for
the curly braces again in the formula bar

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[americasrecycler]

Hi Max,

I couldn't find that thread this morning for some reason (this is my very
first and only thread here)

Pls read this thread again, I think it better explain what I'm trying to do.

Again the source data is always changing that's why I couldn't add them
(although I still could be able to see the numbers), and the only data
registered a real number is the LAST SET (see explaination above)

thanks for your help

Henry

 

[Max]

Please keep all discussions within the newsgroup
And never send any file/private email unless it is requested for

Your root prob is that you have numerous** circular reference errors.
Nothing downstream will compute until you remove these circular errors and
re-do your expressions correctly.

**In sheet: SUM, you had this in B2, filled across/down to H4:
=IF(B1='Source data'!$B$7,'Source data'!$C$6, B2)
All of the above are circular ref errors because the expression contains a
reference to the same cell that its placed in. That's a definite no-no. I'm
not sure what you were trying to do in in B2:H4. You could take this up in a
new fresh thread.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[americasrecycler]

Hi Max,

If you look at the invoice #, being changed and the "Qty" also changing with
the "Inv#" on sheet 1.

What I'm trying to do is to add multiple invoices (with diff. Qty and item
within those Qty ) in sheet 2

I understand that inorder to keep those result in real numbers, I have to
have different input sheet (similar to sheet 1) for each invoice, but that
would make the workbook overloaded (there are hundreds of invoices).

So my question is, is there any way to use only sheet one as data input for
all INV#, then add the total in sheet2 ???

thanks

 

[Max]

I'm not sure that you understood the core problem of circular referencing
which I identified/mentioned in my earlier response.

Anyway you could try the below, which should resolve the circular
referencing bit, and possibly return exactly what you're seeking to do

In sheet: SUM,
Put instead in B2:
=IF(B$1='Source data'!$B$7,INDEX('Source data'!$C$6:$E$6,ROWS($1:1)),"")
Copy B2 across/down to H4.

And in sheet: Source data,
an alternative to replace what you have in C6:E6
would be to place this in C6:
=SUMPRODUCT($B$2:$B$5,C2:C5)
and copy C6 across to E6

Btw, pl mark ALL responses which help you in any way by clicking the YES
buttons (like the ones below)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik