Adding to the formulas of many cells at once

[homie]

Hi,

I have a long list of rates (hourly wages) in my spreadsheet. These are hardcopy numbers (values), not formulas. I would like to add an escalation to the rates by referencing a cell where I can enter an escalation factor.
For example:
In cell A1 I write an escalation value. For example 102%. So if I multiply the hourly rate of 345 by this factor, the rate is increased by 2%.
A1: 102%

Colomn B:
345
786
554
325
986
...

Instead, I would like the contents of colomn B to read like this:
Colomn B:
=345*$A$1
=786*$A$1
=554*$A$1
=325*$A$1
=986*$A$1

So, I would like a way to add *$A$1 to all cells in Colomn B without manually typing it in each cell. Is there a way to do that without using Macros/VBA?

Yes, I know I can just use Colomn C and write in cell C2=B2*$A$1 and I would have the values I want in that colomn. This does not fit with the format of my spreadsheet which is used in my company, and is not an option. Yes, I could also just temporarily get the values from colomn C as discussed above and copy-paste values into colomn B and then delete colomn C again. Again, that is not what I want to do. I want colomn B do be dynamic and have references to A1 (escalation factor).

Thank you so much for your help in advance There are always a lot of smart Excel users on this forum.

P.S. If this has already been answered, please post a link. I could not find someone posting this problem.

 

[homie]

If i understand that you want to multiply the $A$1 value to All cell B:B
Then copy $A$1 and select column B:B and paste special and select Multiply in operation section.

Thanks for the suggestion. How exactly do yo mean? Because I cannot get that to work. I tried three ways:
1. Write the text "$A$1" and mark the text and copy it, then try to paste special. It then asks me whether I want unicode text or just regular text. Either way it just replaces the cells I am pasting into with the text "$A$1".
2. Write "$A$1" in a cell and copy the cell and try to Paste Special and multiply. In that case nothing happens. Presumably because it is just multiplying a number with text, which makes no sense.
3. Copying the cell A1 and pasting special. This just multiplies the cells I want with the escalation factor (A1), but all I am left with is another value number, i.e. not a formula, not a dynamic number that changes if I change the value in cell A1 (escalation factor). This gives me the end numbers I want for that one instance, but does not leave me with dynamic cells.

 

[dchaney]

homie, you could set the VBA code I gave you up to go off each time the workbook is opened or the sheet is activated... Then it will ALWAYS add the proper formula to the entire Column B contents each time someone opens the workbook or selects the sheet... There is no need to add a button or fire the macro each time... no one (even you) would see anything happen....

If you would like to do this option let me know I can tell you exactly how...

 

[homie]

Try this

In the adjescent column, say column C
Put =$A$1 and fill down to fill the whole column next to B
Copy Column C
Highlight column B
Right Click - Paste Special - Formulas - Multiply - OK.

Great idea! This actually works, and requires little effort and no VBA/Macros. Thanks :D

Peter_SSs, thanks for your 2nd option as well. It probably would have worked, but I didn't try it because Jonmo1's suggestion worked and required little effort. Thanks though

P.S. Jonmo1, you don't even need to use a whole colomn (e.g. col C) and copy down =$A$1. You can just write $A$1 in one cell anywhere in the sheet and copy it and paste special over colomn B cells.

 

[Jonmo1]

You're welcome.

Didn't know it would work with the =$A$1 in just one cell. Cool.

 

[homie]

homie, you could set the VBA code I gave you up to go off each time the workbook is opened or the sheet is activated... Then it will ALWAYS add the proper formula to the entire Column B contents each time someone opens the workbook or selects the sheet... There is no need to add a button or fire the macro each time... no one (even you) would see anything happen....

If you would like to do this option let me know I can tell you exactly how...

Thanks for that dchaney For now I will stick with solutions not requiring VBA. Very rarely in my current job do I do anything complicated enough that warrants VBA use. So I prefer to keep it "clean" as other users might use my spreadsheets. This one thing probably would have been fine, but I have seen macro-heavy workbooks created by guys who longer work at the company. Then something goes amiss, and nobody knows how to fix it because they didn't write the code and don't know Visual Basic. Also, things tend to crash more and be less malleable.