Results 1 to 15 of 15

Thread: Any Excel masters around?

  1. #1
    LUSE Galant's Avatar
    Join Date
    Oct 2003
    Location
    Gibraltar
    Posts
    3,212
    Thanks
    466
    Thanked
    542 times in 331 posts

    Any Excel masters around?

    This might not be too difficult but I can't crack it.

    I have two worksheets. One stores pricing data with various forumlae calculating prices at different percentages etc. The other is a summary sheet where I have the final prices showing for easy reference.

    At the moment I have to copy the values across using the special paste values option. However, the two sheets remain separate and must be updated separately.

    I would like to be able to connect the two so that when on the first sheet a price is updated and the values calculated, the updated final price appears on the second worksheet.

    Possible?

    Thanks.
    No trees were harmed in the creation of this message. However, many electrons were displaced and terribly inconvenienced.

  2. #2
    Not a good person scaryjim's Avatar
    Join Date
    Jan 2009
    Location
    Gateshead
    Posts
    15,196
    Thanks
    1,230
    Thanked
    2,291 times in 1,874 posts
    • scaryjim's system
      • Motherboard:
      • Dell Inspiron
      • CPU:
      • Core i5 8250U
      • Memory:
      • 2x 4GB DDR4 2666
      • Storage:
      • 128GB M.2 SSD + 1TB HDD
      • Graphics card(s):
      • Radeon R5 230
      • PSU:
      • Battery/Dell brick
      • Case:
      • Dell Inspiron 5570
      • Operating System:
      • Windows 10
      • Monitor(s):
      • 15" 1080p laptop panel

    Re: Any Excel masters around?

    You should just be able to reference the sheets in a formula?

    So if your calculation sheet is Sheet1 and your summary sheet is Sheet2, you put a formula in a cell on Sheet2 that looks something like =Sheet1!A1 and that will pull whatever the calculated value from Sheet1 is.

    I asume that's what you mean, anyway?

  3. Received thanks from:

    Galant (26-08-2011)

  4. #3
    Senior Member mcmiller's Avatar
    Join Date
    Apr 2006
    Posts
    1,404
    Thanks
    49
    Thanked
    50 times in 39 posts

    Re: Any Excel masters around?

    Pivot table?

  5. #4
    Member
    Join Date
    Aug 2009
    Posts
    182
    Thanks
    3
    Thanked
    23 times in 22 posts
    • barry2811's system
      • Motherboard:
      • Gigabyte GA-Z87-HD3
      • CPU:
      • Intel i5 4670K (cooled by Noctua NH-D14)
      • Memory:
      • 2 x 8GB Corsair
      • Storage:
      • Samsung Pro 128GB Boot Drive, 1 x 6TB Seagate, 1 x 3TB Seagate and 256GB Crucial SSD for games
      • Graphics card(s):
      • Asus GTX970 Strix
      • PSU:
      • EVGA SuperNova 650 G2
      • Case:
      • NZXT S340 Elite
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Viewsonic VG2439M-LED
      • Internet:
      • Sky Fibre

    Re: Any Excel masters around?

    If the data is the same list on both sheets then a simple =sheet2!A1 would suffice.

    If the lists are different (eg, the price detail has all items, and say a stock list only has items that are in stock), then VLOOKUP is your friend.

  6. Received thanks from:

    Galant (26-08-2011)

  7. #5
    Now with added Ruffus Dog Tattysnuc's Avatar
    Join Date
    Feb 2006
    Location
    Liverpool
    Posts
    1,373
    Thanks
    283
    Thanked
    207 times in 133 posts
    • Tattysnuc's system
      • Motherboard:
      • Asus 570X Strix F
      • CPU:
      • 3900X @ Stock
      • Memory:
      • 32Gb Rysen C18 Corsair
      • Storage:
      • 2x 1TB nvme
      • Graphics card(s):
      • 2080 ti under Bykski water
      • PSU:
      • eVGA 850W
      • Case:
      • TT Core 5
      • Operating System:
      • Win 10 Pro
      • Monitor(s):
      • Lg 43"
      • Internet:
      • Utility Warehouse

    Re: Any Excel masters around?

    Depends more on the table structure that you want to use in sheet 1.

    If sheet 1 is a fixed shape with formulae in defined cells you could use array formulae, vlookups or Pivot tables. If you just simply want to get the data that's in the spreadsheet sheet 2 into Sheet 1 you could use a text import. I don't think you've given enough information to be able to really answer you.

    The simplest is to simply link to the cell directly if both sheets have data sorted in the same order

    As barry2811 has said vlookups are certainly a nice and easy way to transpose the data, assuming that it's a 1:1 relationship (ie Product X in Sheet 1 needs to have details that are contained in Sheet2 pulled through).

    Pivot Tables are useful for summarising multiple lines of data, and consolidating into a nice single table

    Array formulae are probably a bit too complex, but can be used to add database like functionality into a spreadsheet. Useful when finding what a lookup of multiple items yields like a select Z-where WXY=?? function in SQL.
    Join the HEXUS Folding @ home team

  8. Received thanks from:

    Galant (26-08-2011)

  9. #6
    LUSE Galant's Avatar
    Join Date
    Oct 2003
    Location
    Gibraltar
    Posts
    3,212
    Thanks
    466
    Thanked
    542 times in 331 posts

    Re: Any Excel masters around?

    Sorry for the lack of detail.

    Here's what I have and want.

    On the first sheet I have a list of prices for various items with about 5 prices for each items due to different suppliers. The cells I want to copy contain a formula which converts the suppliers price into a selling price by calculating and adding duty and a profit percentage. So let's say the starting price is £10, the formula for the final price adds on 9% for duty and 20% profit = £12.90. The bare minimum I need is for that figure to appear on the second worksheet and to update automatically when I update supplier prices.

    After that, it would be great if that final figure could be rounded up to the nearest Pound since at the figures we actually use we don't use pennies. It would save me having to adjust the huge list manually if ever I need to send it.

    Thanks.
    No trees were harmed in the creation of this message. However, many electrons were displaced and terribly inconvenienced.

  10. #7
    jim
    jim is offline
    HEXUS.clueless jim's Avatar
    Join Date
    Sep 2008
    Location
    Location: Location:
    Posts
    11,435
    Thanks
    612
    Thanked
    1,639 times in 1,304 posts
    • jim's system
      • Motherboard:
      • Asus Maximus IV Gene-Z
      • CPU:
      • i5 2500K @ 4.5GHz
      • Memory:
      • 8GB Corsair Vengeance LP
      • Storage:
      • 1TB Sandisk SSD
      • Graphics card(s):
      • ASUS GTX 970
      • PSU:
      • Corsair AX650
      • Case:
      • Silverstone Fortress FT03
      • Operating System:
      • 8.1 Pro
      • Monitor(s):
      • Dell S2716DG
      • Internet:
      • 10 Mbps ADSL

    Re: Any Excel masters around?

    If the two sheets are in the same workbook then as previously stated you can just create a formula like Scaryjim suggested to take the number straight from the first sheet.

    If it isn't updated then hitting F9 (IIRC, could be wrong) will recalculate all formulae.

    As for the rounding, one of the round functions should do the trick.

  11. Received thanks from:

    Galant (26-08-2011)

  12. #8
    Now with added Ruffus Dog Tattysnuc's Avatar
    Join Date
    Feb 2006
    Location
    Liverpool
    Posts
    1,373
    Thanks
    283
    Thanked
    207 times in 133 posts
    • Tattysnuc's system
      • Motherboard:
      • Asus 570X Strix F
      • CPU:
      • 3900X @ Stock
      • Memory:
      • 32Gb Rysen C18 Corsair
      • Storage:
      • 2x 1TB nvme
      • Graphics card(s):
      • 2080 ti under Bykski water
      • PSU:
      • eVGA 850W
      • Case:
      • TT Core 5
      • Operating System:
      • Win 10 Pro
      • Monitor(s):
      • Lg 43"
      • Internet:
      • Utility Warehouse

    Re: Any Excel masters around?

    The command to round the price up to the nearest pound is ROUNDUP(Value,Number of Decimals)

    ie

    if the Value £1.1115 is entered in cell D1
    The to put this to the nearest pound would be Roundup(D1,0)
    To the nearest penny would be Roundup(D1,2)

    Other than that, follow what snootyjim is saying.
    Join the HEXUS Folding @ home team

  13. Received thanks from:

    Galant (26-08-2011)

  14. #9
    LUSE Galant's Avatar
    Join Date
    Oct 2003
    Location
    Gibraltar
    Posts
    3,212
    Thanks
    466
    Thanked
    542 times in 331 posts

    Re: Any Excel masters around?

    Okay, great. Thanks. Just one more thing. What would be the best way to do this for a table rather than cell by cell?
    No trees were harmed in the creation of this message. However, many electrons were displaced and terribly inconvenienced.

  15. #10
    jim
    jim is offline
    HEXUS.clueless jim's Avatar
    Join Date
    Sep 2008
    Location
    Location: Location:
    Posts
    11,435
    Thanks
    612
    Thanked
    1,639 times in 1,304 posts
    • jim's system
      • Motherboard:
      • Asus Maximus IV Gene-Z
      • CPU:
      • i5 2500K @ 4.5GHz
      • Memory:
      • 8GB Corsair Vengeance LP
      • Storage:
      • 1TB Sandisk SSD
      • Graphics card(s):
      • ASUS GTX 970
      • PSU:
      • Corsair AX650
      • Case:
      • Silverstone Fortress FT03
      • Operating System:
      • 8.1 Pro
      • Monitor(s):
      • Dell S2716DG
      • Internet:
      • 10 Mbps ADSL

    Re: Any Excel masters around?

    Not quite with you there - what do you mean specifically by a table?

  16. #11
    LUSE Galant's Avatar
    Join Date
    Oct 2003
    Location
    Gibraltar
    Posts
    3,212
    Thanks
    466
    Thanked
    542 times in 331 posts

    Re: Any Excel masters around?

    Nothing special - I just mean the whole range of cells. There are loads of rows and about seven columns. Any way to get them copied across without creating a formula and then copy/pasting it several hundred times.
    No trees were harmed in the creation of this message. However, many electrons were displaced and terribly inconvenienced.

  17. #12
    Member
    Join Date
    Aug 2009
    Posts
    182
    Thanks
    3
    Thanked
    23 times in 22 posts
    • barry2811's system
      • Motherboard:
      • Gigabyte GA-Z87-HD3
      • CPU:
      • Intel i5 4670K (cooled by Noctua NH-D14)
      • Memory:
      • 2 x 8GB Corsair
      • Storage:
      • Samsung Pro 128GB Boot Drive, 1 x 6TB Seagate, 1 x 3TB Seagate and 256GB Crucial SSD for games
      • Graphics card(s):
      • Asus GTX970 Strix
      • PSU:
      • EVGA SuperNova 650 G2
      • Case:
      • NZXT S340 Elite
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Viewsonic VG2439M-LED
      • Internet:
      • Sky Fibre

    Re: Any Excel masters around?

    Quote Originally Posted by Galant View Post
    Nothing special - I just mean the whole range of cells. There are loads of rows and about seven columns. Any way to get them copied across without creating a formula and then copy/pasting it several hundred times.
    Assuming you are happy with your formula just select the destination range and then paste. Excel will update the cell ranges for you as you go. If you need more control over the cell ranges you can use the $ sign to prevent Excel changing the row and/or column reference as you paste.

  18. Received thanks from:

    Galant (26-08-2011)

  19. #13
    LUSE Galant's Avatar
    Join Date
    Oct 2003
    Location
    Gibraltar
    Posts
    3,212
    Thanks
    466
    Thanked
    542 times in 331 posts

    Re: Any Excel masters around?

    How would I use the $ sign to do that?

    Also, how or where would I add ROUNDUP to an already existing formula?

    Thanks.
    No trees were harmed in the creation of this message. However, many electrons were displaced and terribly inconvenienced.

  20. #14
    jim
    jim is offline
    HEXUS.clueless jim's Avatar
    Join Date
    Sep 2008
    Location
    Location: Location:
    Posts
    11,435
    Thanks
    612
    Thanked
    1,639 times in 1,304 posts
    • jim's system
      • Motherboard:
      • Asus Maximus IV Gene-Z
      • CPU:
      • i5 2500K @ 4.5GHz
      • Memory:
      • 8GB Corsair Vengeance LP
      • Storage:
      • 1TB Sandisk SSD
      • Graphics card(s):
      • ASUS GTX 970
      • PSU:
      • Corsair AX650
      • Case:
      • Silverstone Fortress FT03
      • Operating System:
      • 8.1 Pro
      • Monitor(s):
      • Dell S2716DG
      • Internet:
      • 10 Mbps ADSL

    Re: Any Excel masters around?

    Surely you would just type the formula in on the second sheet and then drag it down?

    If the original formula is =sum(A1:B2) then the new formula would be =roundup(sum(A1:B2),1)

  21. Received thanks from:

    Galant (27-08-2011)

  22. #15
    HEXUS.timelord. Zak33's Avatar
    Join Date
    Jul 2003
    Location
    I'm a Jessie
    Posts
    35,157
    Thanks
    3,105
    Thanked
    3,138 times in 1,916 posts
    • Zak33's system
      • Storage:
      • Kingston HyperX SSD, Hitachi 1Tb
      • Graphics card(s):
      • Nvidia 1050
      • PSU:
      • Coolermaster 800w
      • Case:
      • Silverstone Fortress FT01
      • Operating System:
      • Win10
      • Internet:
      • Zen FTC uber speedy

    Re: Any Excel masters around?

    keep both of these excel documents in the same file. Keep their names the same.
    When first setting them, up, name and save each and CLOSE and RE OPEN them before trying to get the cell in the second one to join to the calculated cell in the first/donor excel doc. If you don't save close and open, you'll often find you simpyl CANNOT just type = in the box and then click the providing cell from the first doc... it just won't work.

    when you alter the one with the changeable data, make sure you click save if you want someone else to open the second one and see the result.

    And when someone does open the second one, make sure they click the Data tab at the top and Refresh All.

    It might be they'll need to accept the security question allowing the two files to link everytime too.

    Quote Originally Posted by Advice Trinity by Knoxville
    "The second you aren't paying attention to the tool you're using, it will take your fingers from you. It does not know sympathy." |
    "If you don't gaffer it, it will gaffer you" | "Belt and braces"

  23. Received thanks from:

    Galant (27-08-2011)

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Vlookup not working (sort of)
    By piggeh in forum Help! Quick Relief From Tech Headaches
    Replies: 9
    Last Post: 03-09-2010, 04:35 PM
  2. .NET Reading Multiple Excel Files
    By Shadow_101 in forum Software
    Replies: 5
    Last Post: 23-11-2007, 03:22 PM
  3. Excel + Database + Automated
    By Kezzer in forum Software
    Replies: 4
    Last Post: 29-03-2007, 04:54 PM
  4. Replies: 1
    Last Post: 18-11-2005, 06:43 PM
  5. M$ Excel masters..
    By XA04 in forum Help! Quick Relief From Tech Headaches
    Replies: 9
    Last Post: 19-09-2005, 10:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •