[TimCutler]

I trying to make an Excel spreadsheet - a bit like a multiplication table.

Column A has 1, 2, 3, 4, 5, etc. in A1, A2, A3, A4, A5, etc. Cell C1 has the formula that whatever is in A1 is multiplied by whatever is in B1. C2 has the formula that whatever is in A2 is

multiplied by whatever is in B2, etc.

My problem is that rather than having to labouriously enter the same number in B1, B2, B3, B4, B5, etc., I would like to be able to enter a figure in for example what I call a "Master Cell",

say Cell D1 & this figure would then automatically be entered into B1, B2, B3, B4, B5, etc.

Your help in this matter would be appreciated - thank you.

Best wishes,

Tim.

[peterh40]

You are in luck, it is a case of using the copy and paste features in Excel. Fill in columns A and B as normal, then fill in the first cell with the formula you need. Select that cell, and click on copy (or Ctrl+C) and then select all the remaining cells in column C and paste the copied cell into those. Excel will detect that its a formula and will auto adjust the formula for you so you get =A2*B2, =A3*B3, = A4*B4 etc.

[cwwozniak]

Originally Posted by TimCutler

rather than having to labouriously enter the same number in B1, B2, B3, B4, B5, etc., I would like to be able to enter a figure in for example what I call a "Master Cell", ... say Cell D1 & this figure would then automatically be entered into B1, B2, B3, B4, B5, etc.

Unless you want to see the numbers in column B, there is no need to have them to calculate the numbers in column C. You could have a formula in cell C1 of =A1*$D$1. You can then use peterh40's method of copying the formula in cell C1 to the other cells in column C. The dollar signs in the D1 cell reference indicate an absolute reference to that cell will not change as the formula is copied to other cells.

[TimCutler]

Thank you peterh40 & cwwozniak for your replies - very much appreciated.

I have now left colum B blank & just entered a figure in the Master Cell & all works well. However, if I did need to see the figure in that is entered in the Master Cell displayed in the various cells in column B, how would I achieve that, please?

Thank you both once again for your swift help.

Best wishes,

Tim.

[cwwozniak]

Originally Posted by TimCutler

However, if I did need to see the figure in that is entered in the Master Cell displayed in the various cells in column B, how would I achieve that, please?

Assuming D1 is still the master cell, put in the formula =$D$1 in cell B1, then copy it down the column.

[TimCutler]

Brilliant... easy when you know how, isn't it?!?!?

Many thanks for your help & advice in this matter.

Best wishes,

Tim.

[cwwozniak]

You're welcome, Tim. Glad that we could help you out.

FWiW, I have been using version of Excel for over 10 years and I am pretty sure I don't know how to use 90% of the features.

[Rollin_Again]

Take a look at the INDIRECT function. It may or may not be applicable to your situation but is a very handy function to know to create formulas on the fly.

http://www.cpearson.com/excel/indirect.htm

Regards,
Rollin

[TimCutler]

Hello Rollin_Again,

Thank you for your post - I'll take a look whenI get a chance.

Best wishes.