13 replies to this topic

[+McCordRm]

First problem.
I'm trying to PUSH data to a cell from multiple locations. Let's say I have cell A1.
The default value of A1 is 0. I'd like to have other cells push their values to A1, so like I could set B1 to be
add +1 and C1 to be add +1 to A1, and now A1 shows up as 2. Is this possible?

[EddieF]

I'm guessing there is a reason you can't simply just set cell A1 like:

=SUM(B1,C1)

? As it will automatically readd if values are changed (if you haven't disabled that)

To actually PUSH data, I think you'll need a VB script or such.

[+McCordRm]

The reason is this:
If I have A1 go out and look for those cells (B1 and C1 in this example) then I need to keep track of all those Cells.
However, if I can have Cells push their data to A1, I can add or remove them at my leisure without having to
adjust the formula for A1 every time. Having to continue adding the formula string in A1 gets confusing, long,
and heaven forbid I move those cells around.

With a push method, it's a one-step process: Add new cell, have it send data to A1.
With a pull method, I add a new cell and THEN have to go back to A1 and change it as well.

[rancid-lemon]

Could you not just name a range, then when you add new cells make sure they lie within that range? Should be able to use simple equations then and not alter A1 every time.

[EddieF]

What if you use another worksheet and in cell A1, sum the entire second worksheet?

[+McCordRm]

No. The cells that would add values to A1 are scattered, some in different worksheets. I can't pull them out of their location
and put them together... it's just the nature of the data.

[+primortal]

Excel's formula's are designed as pull. Either you have to re-look at how you organizing you data or update the formula for A1 accordingly.

[+McCordRm]

Fixed

[+McCordRm]

New Issue.

I have cells D6 -D11 formatted to use a drop-down list.
The options are: C, S, F. The F option is default in every cell.
Only one cell can choose an option of C or S, but they can all have F.
I need to be able to forbid someone from selecting C or S if C or S appears
in any other cell. Thanks!

[Haggis]

more than likely gonna have to use vba for this

So do something like

on cell change

if range (blah:blah) has "C" then msgbox "error C already exists"

[+McCordRm]

I'm trying not to use any scripts.
How about a Conditional Formatting formula so I can make the cells red
if C or S appear in more then one cell?

Just to be clear, all cells begin with F... so multiple F is ok.
If any cell contains a C or an S then every other cell must contain F.

[Haggis]

yeah suppose you could have something like

if(OR(D6="C",D6="S",D7="C",D7="S",D8="C",D8="S",D9="C",D9="S",D10="C",D10="S",D11="C",D11="S"),,)

[Haggis]

are you using the newest office?

if so in confitional formatting there is a Duplicate bit

[+McCordRm]

Yea, I tried the Duplicate function... however, there are duplicates by default: F
Thus, it doesn't work.

The formula you posted didn't work, either.