Excel - Refer to a different column/range each week (variable) 1

[jpcurrie]

Hi guys/gals.

Can anyone help?

I need to pull value through from one workshhet to another but the range will change from week to week.

What I have done to get the inital value is use the following formula and smartfill, but obviously it will need to be variable somehow.:

='Current week'!FF6

This will need to become next week:

='Current week'!FG6

And so on, my inital thought was to pick up the variable [FF] etc from another cell which would be user definable, but htaty's where i'm stuck??

Any ideas ?


John

 

[xlbo]

What you could really do with is a week incrementer

If week 1 is column FF then FF is your base point

If you have a week number in a cell on your target sheet - lets say in a named range called "wk_Incr" then you can use

So for week 1, wk_incr should = 1
for week 2, wk_incr should = 2

=offset('Current week'!FF6,,wk_Incr-1,1,1)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jpcurrie]

Geoff, You are a star!!

Thank you that worked perfect.

John

 

[xlbo]

no probs

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jpcurrie]

Reviving this thread...

How can I make the Original cell reference variable?

i.e. To have a cell where you set the start cell reference

Say Y1 = 'Current week'!FF6

Then adjust this formula to use the Y1 cell as the starting point?

So you could adjust it to 'Current week'!YY6 and not have to adjust formulas..

Thanks.

 

[SkipVought]

hi,

First date in date range by weeks...
[tt]
=int((today()-2)/7)*7+2
[/tt]
as a start.

Work with it and see if its what you need.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpcurrie]

Sorry I should have been clearer:

This is what I have

=OFFSET('Current week'!AY7,,wk_Incr-1,1,1)

I want to make the "'Current week'!AY7" bit variable and set from a value in another cell.

Say H9 = 'Current week'!AY7

I want to be able to change that if needed without reworking the formula (for a end user)

So they could set H9 = 'Current week'!BK and be done with it.

 

[SkipVought]

Please explain using a concrete example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}