Jump to content



Photo

Excel questions


  • Please log in to reply
13 replies to this topic

#1 +McCordRm

McCordRm

    http://richardmccord.com

  • Tech Issues Solved: 1
  • Joined: 06-July 06
  • Location: Dallas, TX

Posted 14 January 2013 - 00:33

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?


#2 EddieF

EddieF

    Neowinian

  • Joined: 04-November 02
  • Location: Ohio/Michigan border
  • OS: Win 7
  • Phone: Droid Razr

Posted 14 January 2013 - 02:47

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.

#3 OP +McCordRm

McCordRm

    http://richardmccord.com

  • Tech Issues Solved: 1
  • Joined: 06-July 06
  • Location: Dallas, TX

Posted 14 January 2013 - 06:17

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.

#4 rancid-lemon

rancid-lemon

    Neowinian

  • Joined: 05-March 09
  • Location: UK

Posted 14 January 2013 - 12:31

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.

#5 EddieF

EddieF

    Neowinian

  • Joined: 04-November 02
  • Location: Ohio/Michigan border
  • OS: Win 7
  • Phone: Droid Razr

Posted 14 January 2013 - 15:28

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

#6 OP +McCordRm

McCordRm

    http://richardmccord.com

  • Tech Issues Solved: 1
  • Joined: 06-July 06
  • Location: Dallas, TX

Posted 14 January 2013 - 17:21

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.

#7 +primortal

primortal

    Why Does It Hurt!

  • Joined: 18-October 01
  • Location: USA
  • OS: Windows 8
  • Phone: Nokia Lumia Icon (929)

Posted 14 January 2013 - 17:32

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.

#8 OP +McCordRm

McCordRm

    http://richardmccord.com

  • Tech Issues Solved: 1
  • Joined: 06-July 06
  • Location: Dallas, TX

Posted 15 January 2013 - 19:36

Fixed

#9 OP +McCordRm

McCordRm

    http://richardmccord.com

  • Tech Issues Solved: 1
  • Joined: 06-July 06
  • Location: Dallas, TX

Posted 18 January 2013 - 10:57

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!

#10 Haggis

Haggis

    Neowinian Senior

  • Tech Issues Solved: 8
  • Joined: 13-June 07
  • Location: Near Stirling, Scotland
  • OS: Debian 7
  • Phone: Samsung Galaxy S3 LTE (i9305)

Posted 18 January 2013 - 11:01

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"

#11 OP +McCordRm

McCordRm

    http://richardmccord.com

  • Tech Issues Solved: 1
  • Joined: 06-July 06
  • Location: Dallas, TX

Posted 18 January 2013 - 11:04

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.

#12 Haggis

Haggis

    Neowinian Senior

  • Tech Issues Solved: 8
  • Joined: 13-June 07
  • Location: Near Stirling, Scotland
  • OS: Debian 7
  • Phone: Samsung Galaxy S3 LTE (i9305)

Posted 18 January 2013 - 11:11

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"),,)

#13 Haggis

Haggis

    Neowinian Senior

  • Tech Issues Solved: 8
  • Joined: 13-June 07
  • Location: Near Stirling, Scotland
  • OS: Debian 7
  • Phone: Samsung Galaxy S3 LTE (i9305)

Posted 18 January 2013 - 11:29

are you using the newest office?

if so in confitional formatting there is a Duplicate bit

#14 OP +McCordRm

McCordRm

    http://richardmccord.com

  • Tech Issues Solved: 1
  • Joined: 06-July 06
  • Location: Dallas, TX

Posted 18 January 2013 - 11:41

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.



Click here to login or here to register to remove this ad, it's free!