1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Simple Excel Problem.

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

Thread Status:
Not open for further replies.
Advertisement
  1. TimCutler

    TimCutler Thread 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

    peterh40

    Joined:
    Apr 15, 2007
    Messages:
    1,387
    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. cwwozniak

    cwwozniak Trusted Advisor

    Joined:
    Nov 28, 2005
    Messages:
    58,641
    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. TimCutler

    TimCutler Thread 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.
     

    Attached Files:

  5. cwwozniak

    cwwozniak Trusted Advisor

    Joined:
    Nov 28, 2005
    Messages:
    58,641
    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. TimCutler

    TimCutler Thread 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. cwwozniak

    cwwozniak Trusted Advisor

    Joined:
    Nov 28, 2005
    Messages:
    58,641
    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

    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. TimCutler

    TimCutler Thread 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.
     
  10. Sponsor

As Seen On
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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/840674

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice