# Software

## Question

Locked

### Excel Question I need help....

By ·
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?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

Collapse -

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

by 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 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 In reply to Use the indirect and cell ...

... And it works perfectly for this example.

## Related Discussions

• 8

#### what is the best chatbot software for small business?

nathanlohey ·

• 2

Buchiiii ·

• 2

#### Which open-source time tracking software is the best?

riedelrichard21 ·

• 2

#### Best email marketing software

aswanthsingampalli ·

• 1

#### Should I replace my LCD with a newer model?

tiroxsteels ·