Excel Question I need help....

By macki60 ·
I submit my report in excel and I am having a hard time using the mixed referrence function...

This is an example
1. I have data A1, A2, A3....down a column.
2. I have one value on C2 ( constant value)
3. the formula is a very simple multiplication formula. I want to multiply every single value of column A to C2. But it needs to be in across a row that starts in D4. So,(D4, E4, F4)
4. So when I input =$A1*$C$2 in D4 it calculates it fine but when i try to drag the formula across the row so [E4=$A2*$C$2, F4=$A3*$C$2...] it repeates the same fomula as D4 and does not adjust for the increase in column number.

How can I fix it so when I drag the formula across a row it can still account for the mixed reference?

Please help and thank you.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

It is not going to happen with dragging..........

by ThumbsUp2 In reply to Excel Question I need hel ...

If you drag across, it will copy across, not down. It doesn't know any better. You will have to change the cell references manually.

Collapse -

Use the indirect and cell functions

by victor.gutzler In reply to Excel Question I need hel ...

Try using the following formula in cell D4: =$C$2*INDIRECT("A" & CELL("col",D4)-3,TRUE). This formula references the current cell's column number, offsets it by 3, and gets the value from the contrived cell address. The formula also adjusts itself to cells it is copied to.

Collapse -

Good Tip!

by ThumbsUp2 In reply to Use the indirect and cell ...

... And it works perfectly for this example.

Related Discussions

Related Forums