Can it be done?

[Dark Horse]

Since I have had some success in getting answers on here, my final few
problems should be easy meat for someone surely?

I have columns from A - AB which stretch across the page farther than the
eye can see and which it is getting to be a pain scrolling across and
filling down as new data is entered in columns A - Q, the remaining columns
are only used for calculation but still have to be filled down. I know I can
hide columns R - AB by moving them across to cover each other, but is there
any way of getting them to fill down automatically once Column Q is filled?
Finally, thinking ahead - because I want to hide them.
Is there a method of asking an IF question in column AB that will change the
colour of the entry in Column Q to RED if a condition is met?
Many Thanks in Advance

 

[Robert Rosenberg]

If you have Excel 2002 and beyond and every column through to AB is used,
Excel can automatically extend the formats and formulas if you have the
feature turned on via Tools-->Options-->Edit Tab-->"Extend list formats and
formulas" (2002)/"Extend data range formats and formulas (2003).

As for your font color turning red, use the Format-->Conditional Formatting
command. In your example:

1. Highlight the cells you want to apply the font color to

2. Click Format-->Conditional Formatting

3. Switch the first dropdown to "Formula is"

4. Type your condition on the formula line. Ex: =$Q1<0

5. Click the Format button and on the FOnt tab, set the font color to red

6. Click OK twice

 

[Max]

a. How about pre-filling the formulas
in cols R - AB with something like:
(formulas copied down the cols)

In R2: =IF(ISBLANK(Q2),"",<Formula for Column R>)
In S2: =IF(ISBLANK(Q2),"",<Formula for Column AB>)
....and so on
In AB2: =IF(ISBLANK(Q2),"",<Formula for Column AB>)
(The above makes the formulas dependent on Q2 being filled)

b. Try conditional formatting
(but from the "converse" perspective)

(think formulas cannot change the format in another cell(s))

For example, suppose you want
entries in col Q to appear red if the
calculated values in col AB are >=10

Select col Q

Click Format > Conditional Formatting

Settings:
For Condition 1
Formula Is | =$AB1>=10
Click Format > Font tab > Select red color > OK
Click OK

 

[Max]

coorection for typo, apologies...

line

In S2: =IF(ISBLANK(Q2),"",<Formula for Column AB>)

should read

In S2: =IF(ISBLANK(Q2),"",<Formula for Column S>)

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
--------------------------------------------------

 

[Dark Horse]

Wouldn't you know it? Mine is 2000.

If you have Excel 2002 and beyond and every column through to AB is used,
Excel can automatically extend the formats and formulas if you have the
feature turned on via Tools-->Options-->Edit Tab-->"Extend list formats and
formulas" (2002)/"Extend data range formats and formulas (2003).

As for your font color turning red, use the Format-->Conditional Formatting
command. In your example:

1. Highlight the cells you want to apply the font color to

2. Click Format-->Conditional Formatting

3. Switch the first dropdown to "Formula is"

4. Type your condition on the formula line. Ex: =$Q1<0

5. Click the Format button and on the FOnt tab, set the font color to red

6. Click OK twice

--
_______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel

 

[Dark Horse]

Thanks Max, I'll try that

coorection for typo, apologies...

line


should read


--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email

 

[Dark Horse]

Max, I seem to end up with a whole heap of circular reference problems that
way - as the first column mentioned Q2 is reliant upon the other columns for
making calculations.

Thanks Max, I'll try that

 

[Max]

I have columns from A - AB which stretch across the page farther than the

eye can see and which it is getting to be a pain scrolling across and
filling down as new data is entered in columns A - Q, the remaining columns
are only used for calculation but still have to be filled down. I know I can
hide columns R - AB by moving them across to cover each other, but is there
any way of getting them to fill down automatically once Column Q is

filled?

Let's recollect a little..

In your post (extract above) you did imply that cols A - Q will contain
*data* entered,
with cols R - AB used for calculation, and basically you wanted the formulas
in cols R - AB to "activate" once col Q is filled ?

Since you hit circular references, try with this setting:

Click Tools > Options > Calculation tab > Check "Iteration" > OK

Does the above help ?

Maybe you could paste the formulas you have in cols R - AB, say, those for
row 2?

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
--------------------------------------------------

 

[Dark Horse]

I did as you suggested, and left the number at 100 as i am not aware what I
am doing here at all.
I tried with just column R to start with and filled down.
I placed an entry of data in the next row down from the last and all i got
was 'iter 1' in the bottom left hand corner of the screen and 'Calculate'
would not go away.
The row below did not fill down either, so I'de guess that another solution
bites the dust.

To recollect:
Columns a- p have data entered
Column q has a formula that uses calculations from columns r-ab which in
turn use the data from a-p
In full the spreadsheet will not fit in a window, so I hide columns r-y
inclusive by dragging column z across to leave columns a -q and z, aa, ab
showing.
So, every time I make new data entries I have to uncover the 8 hidden
columns and manually fill down.
I was hoping that I would be able to fill down automatically once columns
a-p were filled
it seems it is not possible

 

[Max]

Columns a- p have data entered

Column q has a formula that uses calculations from columns r-ab which in
turn use the data from a-p

Since you have now clarified on the above
(which differs from your original description)

i.e. with a *formula* in Q2, and not *data* entered in Q2
the earlier suggestion using IF(ISBLANK(Q2)...) as a check / trigger
to activate the formulas in cols R - AB is out...

Since col Q is now dependent on cols R - AB
(instead of the other way round)
col Q cannot act as a trigger anymore
to activate the formulas in cols R - AB

Perhaps you would like to re-check your computational / logical sequence
in cols R - AB and see which is / are now the critical col(s)
(from amongst cols A - P and R - AB)
to act in place of col Q

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
--------------------------------------------------

 

[Dark Horse]

That's beyond me to call, they all play a part - and I'm giving up on the
idea anyway.

 

[Max]

Thanks for feedback.

cheers
Max