Paste

[niting]

Hi Forum,


I have some data in sheet 1 in cells A31:AA40. The same data is repeated for another month in cell E31:E40. I work with this data in sheet 2.


Now, the months are consecutive in sheet 2 and because of this I can't copy the formula used in col A to Col B in sheet 2.


Could you suggest a way out, since I have to retype the long formulaes everytime the months change.


Thanks in advance

Nitin

 

[Hui]

Can you post the file or formula that you are having problems with ?

 

[Luke M]

I'm guessing you'll be able to use the INDEX/MATCH functions somehow, but as Hui implied, it would really help if we knew more about how the data is Sheet 1 is arranged, and what the formula is that you are trying to copy from col A to col B.

 

[niting]

Hey Luke & Hui,


The link to the sheet is http://rapidshare.com/files/442003054/Copy_of_aNALYSIS.xls


I have to compile data based on sheeet WCA in VC_2 and there I face the problem of not able to copy across the formula and retyping the formula.


Appreciate the help.


Thanks

Nitin

 

[niting]

Further, as you would notice for sheet 1 and VC components, the data which i m using in formula in VC components is in columns of Sheet 1, but my work range is in rows in , so i have to manually edit the formula for each component, Is there a way to copy the formula across the row???

 

[Luke M]

Niting,

I'm afraid I don't have access to filesharing here at work, maybe Hui (or someone) can see it later. Until then, I'll take my best guess.


One trick to use when copying formulas the opposite direction the the reference data goes is to use OFFSET. (See Chandoo's guide, or XL help for syntax). In your case, you could do something like:

=OFFSET(Sheet1!$A$2,COLUMN(A1)-1,ROW(A1)-1)


When you copy this formula across, the 2nd argument evaluates to 1, so the overall formula will look at 1 cell below A2 (thus looking 1 cell lower even though formula was copied across!)

 

[Hui]

Niting

I have looked at your spreadsheet and am unsure what your after


Which cells are you having trouble with as vc_...(2) appears complete to me ?

 

[niting]

Hui,


sorry fr the delayed reply.


As for the problem, i am facing, as you wld notice the data is in sheet 1 month wise and according to units.


In the VC_Components sheet, my columns are component wise and the months are in rows. The problem that i face is that i insert a formula in month of november for RM, then i can't copy it across for other components

and secondly, i have to rewrite the formula for the december month, instead of being able to copy the formula below.


The similar problem I face, in reference to the sheets, WCA and VC_Components(2).


Hui and Forum, a small query, what is the shortcut to go back to a cell referred to in formula, eg, if in cell A2, i use A350+A351, then how do i go straight to A350 and A351 rather than scrolling.


Thanks

 

[Luke M]

niting,


In answer to the small query, Ctrl+[ will select direct precedents, Ctrl+Shift+[ will select all precedents (if cell A350 in turn refrenced another cell, it would also select that one)

 

[niting]

Luke M,


Thanks for the suggestion.


The shortcut refers back to the precedent without evaluating the formula, but I wld want to simultaneoulsy evaluate the formula in the active cell and move beetween the cells referred to in the formula, without having to scroll manually through the worksheet.


Thanks for the patience and help.


NItin

 

[Luke M]

I don't believe that possible, as XL would be selecting other cells, and no longer looking at the cell you want to evaluate. You can highlight sections of your formula and press F9 to evaluate them (note that this DOES change your formula, so hit escape to leave w/o saving changes), or you can use the Formula Auditing toolbar to step through a formula (preferred). Neither of these will allow you to simultaneously evaluate the formula and go to that section of the worksheet.