Excel Query

hellarda

hellarda

hellarda

Soldato
Joined
4 Mar 2008
Posts
2,567
Location
Guildford
Hi all,

I am trying to multiply a fixed value in one cell, for example the value in sheet 1 A1 by the values in Sheet 2 A1, C1, E1, G1, I1, K1 etc, and then store the value in Sheet 1 A2, B2, C2, D2, E2, F2 etc.

I have managed to fix the formula to use A1 with the $ sign, however when I drag the formula of Sheet1$A1*Sheet2A1 across it changes the formula to Sheet1$A1*Sheet2B1 rather than Sheet1$A1*Sheet2C1 which is what I want.

Is there a way of doing this?

Thanks
 
Type it out manually twice, select both then drag them out. If you've got 2 with the fixed cell number excel will realise you want that cell the same in every formula you drag out.
 
Cyanide said:
Type it out manually twice, select both then drag them out. If you've got 2 with the fixed cell number excel will realise you want that cell the same in every formula you drag out.
Click to expand...

I've tried that and it doesn't seem to work

If I select the formula containing D and F for example and drag them out it repeats F and then does H, so I get DFFH, whereas it should go DFHJ
 
Last edited:
You can use the indirect and column formulas to skip every other value.

I'll have a look in a bit

try this in sheet 1 B1:
=$A$1*INDIRECT("sheet2!R"&ROW(A1)&"C"&(COLUMN(A1)*2),FALSE)

it'll skip every other column in sheet 2 but go down with the rows [so it wont skip every other row] and can be dragged to infinity.

what it's doing is building up a cell adress of "sheet2!R1C2" kind of thing, using the ROW and COLUMN formulas to get a number for the row and column and in the case of the column multiplying it by 2 to skip every other value

i use a formula similar to this for producing graphs and reporting results based on the name of the tab, i have one standard 'template' graph and i just copy out as many as i like and rename each tab to the batch number and it'll return it on the main sheet.
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom