Close

Results 1 to 16 of 16

Thread: excel formula

  1. #1
    DF VIP Member
    bbshark's Avatar
    Join Date
    Jul 2001
    Location
    shh im hiding
    Posts
    3,911
    Thanks
    288
    Thanked:        301
    Karma Level
    555

    Default excel formula

    I want to set up a formula where i input a word into a cell and that determines a value in another cell for a calculation. is that possible? could be a differnent value depending on the preset words.
    bbshark. The Member with the first ever DF Fine

    http://digital-forums.com/showthread.php?t=451657

  2. #2
    DF VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    369

    Default Re: excel formula

    Need a bit more information of what you mean but I think you could use a Vlookup to look up a word in a list and return the value related to that word.

  3. #3
    DF VIP Member SiE's Avatar
    Join Date
    Jan 2001
    Location
    My comfy sofa
    Posts
    7,211
    Thanks
    196
    Thanked:        407
    Karma Level
    786

    Default Re: excel formula

    Sounds like you need to use a simple Vlookup

  4. #4
    DF VIP Member
    bbshark's Avatar
    Join Date
    Jul 2001
    Location
    shh im hiding
    Posts
    3,911
    Thanks
    288
    Thanked:        301
    Karma Level
    555

    Default Re: excel formula

    It's a calc to work out the weight and volume of packs when given a square metre of a roof.

    Different profiles have different weights and package quantitys so will take up different space when flat packed to go on a truck.
    I could make the calc repeated over various ones but I want to make it so the user doesn't need to search for the correct profile and instead can just type the profile in and it will work it's magic.
    bbshark. The Member with the first ever DF Fine

    http://digital-forums.com/showthread.php?t=451657

  5. #5
    DF VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    369

    Default Re: excel formula

    Drop me a excel file with what you want it to do and the different data and I will sort it for ya ;-)

    3 Thanks given to akimba

    bbshark (17th April 2014),  jaguar982 (17th April 2014),  Soulassassin (17th April 2014)  


  6. #6
    DF VIP Member
    bbshark's Avatar
    Join Date
    Jul 2001
    Location
    shh im hiding
    Posts
    3,911
    Thanks
    288
    Thanked:        301
    Karma Level
    555

    Default Re: excel formula

    id like to learn how to do it myself to be honest but i can send it to you and if you explain what you have done that would be great.

    basicly the table in the pdf is the information for each profile, the numbers on the right is the sheets per pack.
    the excel file has the information i put in now on the left and the calculations i need on the right.

    i would like to just put in the profile name ie 65/305 and the thickness in with maximum sheet length and it auto fill in the mass per m2 and liner mass.

    does that make sense?

    https://drive.google.com/file/d/0Bz2...it?usp=sharing
    https://drive.google.com/file/d/0Bz2...it?usp=sharing
    bbshark. The Member with the first ever DF Fine

    http://digital-forums.com/showthread.php?t=451657

  7. #7
    DF VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    369

    Default Re: excel formula

    Still as clear as mud but I have done what I thik you want.

    http://www.kimba.me.uk/excel/kalzip.xlsx

    I have created a subset of your information in a table G4:K10
    Created drop down list for the data entry for Product and Width using Data Validation

    Used SUMPRODUCT to populate the Meter Square, Mass Meter Square, Liner Mass and then your calculations still calculate the total weight, total pack weight and total packs.

    I didn't see where the sheet lengths in meter and sheets per pack were on your PDF so I will leave that to you as you want to learn ;-)

    Thanks to akimba

    bbshark (17th April 2014)  


  8. #8
    DF VIP Member
    bbshark's Avatar
    Join Date
    Jul 2001
    Location
    shh im hiding
    Posts
    3,911
    Thanks
    288
    Thanked:        301
    Karma Level
    555

    Default Re: excel formula

    ok that just what i wanted. made a couple of alterations and added more profiles. put in a sumproduct for the pack sizes and generally just completed for the rest of the details.

    also locked the formula cells so that they cannot be altered.

    thanks very much

    what do you think?

    https://drive.google.com/file/d/0Bz2E35WtzrFXenFCYk9MN0toTjA/edit?usp=sharing
    Last edited by bbshark; 17th April 2014 at 06:06 PM.
    bbshark. The Member with the first ever DF Fine

    http://digital-forums.com/showthread.php?t=451657

  9. #9
    DF VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    369

    Default Re: excel formula

    wow what a great guess from me but I also hope you learnt a little too ;-)

    When I lock a sheet I lock it all down except the editable fields which I change the background to green so people know they can edit these.

    click the 4 cells (holding ctrl) right click and format cell>Protection, untick locked. they when you protect just tick the select unprotected cells.

    Thanks to akimba

    bbshark (22nd April 2014)  


  10. #10
    DF VIP Member
    bbshark's Avatar
    Join Date
    Jul 2001
    Location
    shh im hiding
    Posts
    3,911
    Thanks
    288
    Thanked:        301
    Karma Level
    555

    Default Re: excel formula

    yeah i learnt about drop down boxes and that sumproduct. hopefully next time i need to use them ill remember how to. if not i have that sheet to refer to.

    Thanks
    bbshark. The Member with the first ever DF Fine

    http://digital-forums.com/showthread.php?t=451657

  11. #11
    DF VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    369

    Default Re: excel formula

    Theres a whole lot more you can do with Excel than just dropdowns and Sumproduct ;-)

    VBA now that's where you want to be at ;-)

  12. #12
    DF VIP Member
    bbshark's Avatar
    Join Date
    Jul 2001
    Location
    shh im hiding
    Posts
    3,911
    Thanks
    288
    Thanked:        301
    Karma Level
    555

    Default Re: excel formula

    VBA?
    bbshark. The Member with the first ever DF Fine

    http://digital-forums.com/showthread.php?t=451657

  13. #13
    DF VIP Member flipper321's Avatar
    Join Date
    Feb 2003
    Location
    Essex
    Posts
    2,696
    Thanks
    11
    Thanked:        131
    Karma Level
    474

    Default Re: excel formula

    Quote Originally Posted by akimba View Post
    Theres a whole lot more you can do with Excel than just dropdowns and Sumproduct ;-)

    VBA now that's where you want to be at ;-)
    VBA is far too slow. .NET integration is where it's at!

  14. #14
    DF VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    369

    Default Re: excel formula

    "Visual Basic for Applications" MS Office's programmable language (Alt+F11) but I was just saying that as the famous phrase is "90% of users use 10% of the Application and 10% use 90% the Application"

    Yeah can argue .Net would be faster but for simple programming start with the tools given to you for free ;-)

  15. #15
    DF VIP Member flipper321's Avatar
    Join Date
    Feb 2003
    Location
    Essex
    Posts
    2,696
    Thanks
    11
    Thanked:        131
    Karma Level
    474

    Default Re: excel formula

    .NET is free!

  16. #16
    DF VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    369

    Default Re: excel formula

    ok rather than the word "free" how about the preinstalled tools that come with Excel?

Similar Threads

  1. excel for fee
    By bobbobb in forum Programming
    Replies: 8
    Last Post: 7th March 2005, 11:22 PM
  2. Excel graph problem
    By marktb in forum PC Problems
    Replies: 2
    Last Post: 9th October 2002, 04:42 PM
  3. Formula 1 Is Boring
    By Digi Program in forum General Sports
    Replies: 10
    Last Post: 1st October 2002, 04:16 PM
  4. Help building a Formula 1 car body (sort of)
    By bozza in forum Home Audio/Video, Electronic Toys & Gadgets
    Replies: 2
    Last Post: 16th September 2002, 04:05 PM
  5. Formula farce
    By 4me2 in forum General Sports
    Replies: 17
    Last Post: 31st August 2002, 03:31 PM

Social Networking Bookmarks

Posting Permissions

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