Continuing the cell references in formulas

[Kreller]

How du you make excel continue the cell references (the way I want) i
formulas when copying? I have the formula

=HVIS(ANTAL.BLANKE(B4:C4)<KOLONNER(B4:C4);"x";"")

and when I copy this cell the references change to (c4:d4) instead o
(d4:e4). I'm sure there is a simple way of going around this ... :

 

[Jan Karel Pieterse]

Hi Kreller,

=HVIS(ANTAL.BLANKE(B4:C4)<KOLONNER(B4:C4);"x";"")

and when I copy this cell the references change to (c4:d4) instead of
(d4:e4). I'm sure there is a simple way of going around this ...

Copy two columns right, then move back one column.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Frank Kabel]

Hi
try
=HVIS(ANTAL.BLANKE(OFFSET(B4:C4,0,COLUMN(A:A)-1))<KOLONNER(OFFSET(B4:C4
,0,COLUMN(A:A)-1));"x";"")

 

[Kreller]

Hi
try
=HVIS(ANTAL.BLANKE(OFFSET(B4:C4,0,COLUMN(A:A)-1))<KOLONNER(OFFSET(B4:C4
,0,COLUMN(A:A)-1));"x";"")

Excel returns an error (around the "(B4:C4,0,COLUMN(A:A)-1)", and a
I'm not sure how the offset command works, I have no idea of solving i

 

[Frank Kabel]

Hi
sorry, you are not using an english Excel version. You have to replace
OFFSET and COLUMN with your local names
OFFSET: FORSKYDNING
COLUMN: KOLONNE

So in total try
=HVIS(ANTAL.BLANKE(FORSKYDNING(B4:C4;0;KOLONNE(A:A)-1))<KOLONNER(FORSKY
DNING(B4:C4;0;KOLONNE(A:A)-1));"x";"")


--
Regards
Frank Kabel
Frankfurt, Germany

Hi
try
=HVIS(ANTAL.BLANKE(OFFSET(B4:C4,0,COLUMN(A:A)-1))<KOLONNER(OFFSET(B4:C4
,0,COLUMN(A:A)-1));"x";"")

Excel returns an error (around the "(B4:C4,0,COLUMN(A:A)-1)", and as
I'm not sure how the offset command works, I have no idea of solving
it -

 

[sokevin]

HVIS(ANTAL.BLANKE(B4:C4)<KOLONNER(B4:C4);"x";"")


what is hvis, antal.blanke and kolonner???

i wanna learn, i cant find them in the predefined functions
:rolleyes

 

[Frank Kabel]

Hi
HVIS: IF
ANATAL.BLANKE: COUNTBLANK
KOLONNER: COLUMNS

 

[Kreller]

Thank you for your help. I think I have simplified my problem, though
because your solution doesn't seem to solve my

The problem is that i am making a calender. And in this calender I'
making an overview sheet where I'm summing the activities up in week
instead of on a day-basis. Thus in the weekcalender, I want to inser
an "x" if there is activities in any one of the corrosponding days fro
the day-based calender. The formula below solves this.

=HVIS(ANTAL.BLANKE(Kalender!M5:S5)<KOLONNER(Kalender!M5:S5);"x";"")

to change into

=HVIS(ANTAL.BLANKE(Kalender!o5:u5)<KOLONNER(Kalender!o5:u5);"x";"")

when I drag/copy the cell to the rest of the weeks. When I copy now
excel returns this

=HVIS(ANTAL.BLANKE(Kalender!n5:t5)<KOLONNER(Kalender!n5:t5);"x";"")

I guess the problem is that I count weeks in one sheet and days in th
other. Is there a way to solve this?

*Hi
sorry, you are not using an english Excel version. You have t
replace
OFFSET and COLUMN with your local names
OFFSET: FORSKYDNING
COLUMN: KOLONNE

So in total try
=HVIS(ANTAL.BLANKE(FORSKYDNING(B4:C4;0;KOLONNE(A:A)-1))<KOLONNER(FORSKY
DNING(B4:C4;0;KOLONNE(A:A)-1));"x";"")


--
Regards
Frank Kabel
Frankfurt, Germany

Hi
try
=HVIS(ANTAL.BLANKE(OFFSET(B4:C4,0,COLUMN(A:A)-1))<KOLONNER(OFFSET(B4:C4
,0,COLUMN(A:A)-1));"x";"")

Excel returns an error (around the "(B4:C4,0,COLUMN(A:A)-1)", an as
I'm not sure how the offset command works, I have no idea o solving
it -

 

[Frank Kabel]

Hi
it would be helpful if you could explain in what cells you have
inserted the first formula and into which cell you want to copy the
formula 8the cell to the right?)

You may try
=HVIS(ANTAL.BLANKE(FORSKYDNING(Kalender!$M$5:$S$5;0;KOLONNE(A:A)-1))<KO
LONNER(FORSKY
DNING(Kalender!$M$5:$S$5;0;KOLONNE(A:A)-1));"x";"")


--
Regards
Frank Kabel
Frankfurt, Germany

Thank you for your help. I think I have simplified my problem, though,
because your solution doesn't seem to solve my

The problem is that i am making a calender. And in this calender I'm
making an overview sheet where I'm summing the activities up in weeks
instead of on a day-basis. Thus in the weekcalender, I want to insert
an "x" if there is activities in any one of the corrosponding days
from the day-based calender. The formula below solves this.

=HVIS(ANTAL.BLANKE(Kalender!M5:S5)<KOLONNER(Kalender!M5:S5);"x";"")

to change into

=HVIS(ANTAL.BLANKE(Kalender!o5:u5)<KOLONNER(Kalender!o5:u5);"x";"")

when I drag/copy the cell to the rest of the weeks. When I copy now,
excel returns this

=HVIS(ANTAL.BLANKE(Kalender!n5:t5)<KOLONNER(Kalender!n5:t5);"x";"")

I guess the problem is that I count weeks in one sheet and days in the
other. Is there a way to solve this?

*Hi
sorry, you are not using an english Excel version. You have to
replace
OFFSET and COLUMN with your local names
OFFSET: FORSKYDNING
COLUMN: KOLONNE

So in total try
=HVIS(ANTAL.BLANKE(FORSKYDNING(B4:C4;0;KOLONNE(A:A)-1))<KOLONNER(FORSKY
DNING(B4:C4;0;KOLONNE(A:A)-1));"x";"")


--
Regards
Frank Kabel
Frankfurt, Germany

Hi
try

=HVIS(ANTAL.BLANKE(OFFSET(B4:C4,0,COLUMN(A:A)-1))<KOLONNER(OFFSET(B4:C4