Auto Select Cell Value

**lockye** · 11-17-2008, 06:35 PM

Thanks for all your help. Sorry about the double posting.

I have 3 columns, A1, A2 and A3

I would like A3 to show me the value of A1 unless there is a value in A2 then it would show the value of A2 instead.

I would also like to change the text color to reflect which cell the value comes from.

Any help would be appreciated.

**teylyn** · 11-17-2008, 07:13 PM

In A3 enter the formula

=IF(ISBLANK(A2),A1,A2)

For cell A3 use conditional formatting with IsFormula

=$A$3=$A$1

and make the conditional format blue text, for example.

A3 will then be blue if the value is from A1, otherwise it will be the standard format.

HTH

teylyn

**lockye** · 11-18-2008, 12:56 PM

What do i do if there is a formula in A1 or A2 to generate the quantities?

**lockye** · 11-18-2008, 01:02 PM

How do i use ISBLANK if there is a formula in the cell it is referencing?

I have 2 columns A1 and A2 they get there quantities from a formula.

I would like A3 to display A2 if it has a quantity and A1 if it does not,sometimes neither A1 or A2 will have quantities.

Thanks for the help

**Shijesh Kumar** · 11-18-2008, 01:21 PM

Hi,

Try this formula

Please Login or Register  to view this content.

**lockye** · 11-18-2008, 01:55 PM

I get no error message, but the cell has no value until i delete the formula out of A2

Formula A1 and A2 are summing information from a previous page.

A1 =SUMIF(CHR!$D$17:$D$5000,C10,CHR!$K$17:$K$5000)
A2 =SUMIF(CHR!$D$17:$D$5000,C10,CHR!$L$17:$L$5000)

C10 contains an item code

**ChemistB** · 11-18-2008, 03:18 PM

You can try IF(ISNUMBER(.... instead of ISBLANK, then reverse your arguments.

**teylyn** · 11-18-2008, 03:30 PM

Hi,

if A1 and A2 contain formulas, testing with ISBLANK() will only work if the formula returns a blank or empty string (""). If the formula returns a number, and the number is zero, ISBLANK() will not work, so you need to change the condition in your IF statement. Something like

=IF(A2=0,A1,A2)

should work

cheers

Teylyn

**lockye** · 11-18-2008, 04:41 PM

That does not seem to work if A1 is blank

**lockye** · 11-18-2008, 04:41 PM

i cant seem to get it to work

=IF(ISNUMBER(A1),A2,A1)

IF A1 is blank and there is a value in A2 i don't get anything

If A1 has a value and A2 has a value i get A2 which is right.

If A1 has a value but A2 does not, then again nothing

It seems that both A1 and A2 have to have numbers in their cells

**ChemistB** · 11-18-2008, 05:03 PM

Please Login or Register  to view this content.

Is that what you want?

**shg** · 11-18-2008, 05:03 PM

I would like A3 to display A2 if it has a quantity and A1 if it does not,sometimes neither A1 or A2 will have quantities.

=if(a2<>"", a2, if(a1<>"", a1, ""))

**teylyn** · 11-18-2008, 05:04 PM

it does what you specified in your first post.

I would like A3 to show me the value of A1 unless there is a value in A2 then it would show the value of A2 instead.

Maybe you need to restate your requirements.

**martindwilson** · 11-18-2008, 05:06 PM

since

A1 =SUMIF(CHR!$D$17:$D$5000,C10,CHR!$K$17:$K$5000) and
A2 =SUMIF(CHR!$D$17:$D$5000,C10,CHR!$L$17:$L$5000)
will return a 0 if nothing
then
=if( and( a1 =0, a2 = 0), 0, if( a2> 0, a2, a1))

**teylyn** · 11-18-2008, 05:07 PM

You've started two threads about the same problem. Several people are jumping up and down to help you. Your contributions are not very helpful. You seem to think that you have a right to our time and efforts.

I'm starting to get annoyed.

**martindwilson** · 11-18-2008, 05:11 PM

this is teylyn >>>>>>>>

and i agree

**teylyn** · 11-18-2008, 05:13 PM

I'm not willing to help you any further. You've started another thread for the same subject.

I'm not happy about being exploited.

**lockye** · 11-18-2008, 05:37 PM

I apologize, I started on thread yesterday and replied to it this morning.

I started another today because for some reason later in the day i could not post to the previous one again.

**shg** · 11-18-2008, 05:40 PM

Threads merged.

In fairness to lockye, the forum has had severe problems over the last two days.

**lockye** · 11-18-2008, 05:40 PM

I apologize, I started on thread yesterday and replied to it this morning.

I started another today because for some reason later in the day i could not post to the previous one again.