Is there a way to relocate content?!?!

[Jamie Wagler]

Hello again,

Is there a way for me to relocate the value within a cell?

For example if the code contained "2" and that populated cell A6 and I change the code to a 3 which will cause A7 to populate instead?

 

[Hui]

As a comment, Excel Pulls data it doesn't Push data at all.
So you need to have formula where you want the results that will Pull the data from other sources.

In A5
=IF(ROW()=ROW(OFFSET($A$4,$C$3,0)),$C$2,"")
C3 Contains the value 2 or 3 your offset value
C2 Contains the content you want to go into A6, A7 etc

Copy A5 down to suit

see attached:

 

[Jamie Wagler]

Thanks!!!

One question. I can get the content to move vertically; how would I get them to move horizontally?

 

[SirJB7]

Hi, Jamie Wagler!

Invert the 2nd and 3rd parameters in the OFFSET function:
OFFSET($A$4,$C$3,0) for vertical movement, since you change the row offset
OFFSET($A$4,0,$C$3) for horizontal movement, since you change the column offset

Just as a tip, remember that if you enter in an empty cell "=XXX(" (unquoted) and press the "fx" button at the left of the edition bar text box, you'll be prompted with the function wizard which shows every argument, gives a brief description of the function and by the bottom left link lets you access the built-in or online help. It works for any function.

The same from the VBA editor, you can place the cursor (keyboard, so click, don't hover with mouse) on any VBA reserved word and press F1 to access the same type of help.

Regards!

 

[Jamie Wagler]

@ Hui & SirJB7

I have tried to relocate on the horizontal but I am running into an issue. Please see the attached.

 

[Somendra Misra]

@Jamie Wagler
Hi,

Use below formula in B4 and copy across the 4th row to get desired result.
=IF(COLUMN()=COLUMN(OFFSET($A$4,0,$C$3)),$C$2,"")

Please have a look of attached file. Just advise if any issue.

Regards!

 

[Jamie Wagler]

Thanks!

I was able to get it to work... one other thing... if I want the value to move left I assume that I would require a negitive value? Or is there a way to write it without a negative?

 

[Somendra Misra]

@Jamie Wagler

Suppose you want to move left from column P4; use below formula in A4 and copy across row 4.
=IF(COLUMN()=COLUMN(OFFSET($P$4,0,-$C$3)),$C$2,"")

Regards,

 

[Jamie Wagler]

@Somendra Misra @Hui @SirJB7

Thank you all for your help!

I think I approached the problem backwards. See the attached file. What I am trying to do is offset the data in row 31 from row 30 by the value within cell J17. The values will be required to move Left. IE the 500 in I30 will populate H31 when J17 is 1, and G31 when 2.

 

[SirJB7]

OFFSET($A$4,$C$3,0) for vertical movement, since you change the row offset
OFFSET($A$4,0,$C$3) for horizontal movement, since you change the column offset

Hi, Jamie Wagler!
Had understood Hui's formula for vertical movement and my parameters swap for horizontal movement you've reached this unique formula:
OFFSET(<value>,<rows shift>,<columns_shift>)
Do you think you can manage to handle the proper replacements for accomplishing your last post issue? IE, +1 row always and -1 or -2 columns depending on value of J17.
Regards!

 

[Jamie Wagler]

@SirJB7

I understand the -1 part to have the values move left but I am still a little fuzzy with offset as a whole. I will just need to play with it some more.

 

[SirJB7]

Hi, Jamie Wagler!
It'd be something like... if(j17=1,<col.offset=-1>,if(j17=2,<col.offset=-2>,""))
Regards!