Different constants in destination cells

[Wag]

Here's one I've never encountered before.

All of the data in my spreadsheet is constant data. For example, in column A,

10
20
30
40

What I need to do is multiply these cells by another cell, D1, which contains 1.11. I know I can use paste special, but in this case, that is not ideal.

I'd rather enter =10*$D$1, then copy it so that each cell is now,

10*$D$1
20*$D$1
30*$D$1
40*$D$1

That's merely an example. The entire spreadsheet is a matrix of constant values which will all need to look just like this.

As always, your help is greatly appreciated.

--Wag--

 

[Luke M]

Rather than doing it that way, which buries the constant within a formula, making it hard to change, and also creating different formulas, why not use another column with something like:
=A2*$D$1

Now you have the same formula structure for easier auditing, and you can easiler change any constants.

 

[Wag]

Normally, we'd do it that way. In this case, we prefer to keep the constant in place, create the formula around it without having to do it one cell at a time.

EDIT: Changing the constants is not necessary in this case.

I'm almost convinced that it can't be easily done without VBA in which case, we may do it "the right way" as you've indicated.

--Wag--

 

[Luke M]

You assume correctly. Since each formula would end up being unique, there's no nice way to do it. In one shot. One final way.

1. Find a temporary helper column.
2. Make this formula:
="FAKE="&A2&"*$D$1"
3. Copy helper cell(s)
4. Paste as values over previous constants.
5. Do a find a replace on pasted cells, replacing "FAKE" with nothing.
6. Clear temp helper column

 

[Wag]

That's a good solution too.

Given the size of the workbook, it's likely that we're going to have to copy the entire worksheet to a different tab and run it that way. I just didn't want to double the size of the workbook as there are 120 tabs and each tab has about 60,000 cells of static data.

--Wag--