# Simple Excel Problem.

Discussion in 'Business Applications' started by TimCutler, Jul 5, 2009.

Not open for further replies.
1. ### TimCutlerThread Starter

Joined:
Mar 3, 2000
Messages:
224
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.

2. ### peterh40

Joined:
Apr 15, 2007
Messages:
1,383
First Name:
Peter
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.

3. ### cwwozniakTrusted Advisor

Joined:
Nov 28, 2005
Messages:
57,789
First Name:
Chuck
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.

4. ### TimCutlerThread Starter

Joined:
Mar 3, 2000
Messages:
224
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.

File size:
24.5 KB
Views:
55
5. ### cwwozniakTrusted Advisor

Joined:
Nov 28, 2005
Messages:
57,789
First Name:
Chuck
Assuming D1 is still the master cell, put in the formula =\$D\$1 in cell B1, then copy it down the column.

6. ### TimCutlerThread Starter

Joined:
Mar 3, 2000
Messages:
224
Brilliant... easy when you know how, isn't it?!?!?

Many thanks for your help & advice in this matter.

Best wishes,

Tim.

7. ### cwwozniakTrusted Advisor

Joined:
Nov 28, 2005
Messages:
57,789
First Name:
Chuck
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.

8. ### Rollin_Again

Joined:
Sep 4, 2003
Messages:
4,912
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

9. ### TimCutlerThread Starter

Joined:
Mar 3, 2000
Messages:
224
Hello Rollin_Again,

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

Best wishes.

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!