Close

Results 1 to 16 of 16

Thread: excel formula

  1. #1
    DF Jedi bbshark's Avatar
    Join Date
    Jul 2001
    Location
    shh im hiding
    Posts
    3,906
    Thanks
    287
    Thanked:        296
    Karma Level
    541

    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

    [Only registered and activated users can see links. ]
    [Only registered and activated users can see links. ]

  2. #2
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    3,019
    Thanks
    1,152
    Thanked:        836
    Karma Level
    336

    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 PwNagE SiE's Avatar
    Join Date
    Jan 2001
    Location
    My comfy sofa
    Posts
    7,212
    Thanks
    196
    Thanked:        409
    Karma Level
    798

    Default Re: excel formula

    Sounds like you need to use a simple Vlookup

  4. #4
    DF Jedi bbshark's Avatar
    Join Date
    Jul 2001
    Location
    shh im hiding
    Posts
    3,906
    Thanks
    287
    Thanked:        296
    Karma Level
    541

    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

    [Only registered and activated users can see links. ]
    [Only registered and activated users can see links. ]

  5. #5
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    3,019
    Thanks
    1,152
    Thanked:        836
    Karma Level
    336

    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 Jedi bbshark's Avatar
    Join Date
    Jul 2001
    Location
    shh im hiding
    Posts
    3,906
    Thanks
    287
    Thanked:        296
    Karma Level
    541

    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?

    [Only registered and activated users can see links. ]
    [Only registered and activated users can see links. ]
    bbshark. The Member with the first ever DF Fine

    [Only registered and activated users can see links. ]
    [Only registered and activated users can see links. ]

  7. #7
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    3,019
    Thanks
    1,152
    Thanked:        836
    Karma Level
    336

    Default Re: excel formula

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

    [Only registered and activated users can see links. ]

    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 Jedi bbshark's Avatar
    Join Date
    Jul 2001
    Location
    shh im hiding
    Posts
    3,906
    Thanks
    287
    Thanked:        296
    Karma Level
    541

    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?

    [Only registered and activated users can see links. ]
    Last edited by bbshark; 17th April 2014 at 06:06 PM.
    bbshark. The Member with the first ever DF Fine

    [Only registered and activated users can see links. ]
    [Only registered and activated users can see links. ]

  9. #9
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    3,019
    Thanks
    1,152
    Thanked:        836
    Karma Level
    336

    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 Jedi bbshark's Avatar
    Join Date
    Jul 2001
    Location
    shh im hiding
    Posts
    3,906
    Thanks
    287
    Thanked:        296
    Karma Level
    541

    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

    [Only registered and activated users can see links. ]
    [Only registered and activated users can see links. ]

  11. #11
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    3,019
    Thanks
    1,152
    Thanked:        836
    Karma Level
    336

    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 Jedi bbshark's Avatar
    Join Date
    Jul 2001
    Location
    shh im hiding
    Posts
    3,906
    Thanks
    287
    Thanked:        296
    Karma Level
    541

    Default Re: excel formula

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

    [Only registered and activated users can see links. ]
    [Only registered and activated users can see links. ]

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

    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 Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    3,019
    Thanks
    1,152
    Thanked:        836
    Karma Level
    336

    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 Jedi flipper321's Avatar
    Join Date
    Feb 2003
    Location
    Essex
    Posts
    2,696
    Thanks
    11
    Thanked:        131
    Karma Level
    436

    Default Re: excel formula

    .NET is free!

  16. #16
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    3,019
    Thanks
    1,152
    Thanked:        836
    Karma Level
    336

    Default Re: excel formula

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

Similar Threads

  1. [HELP] Help with a sum/formula in excel 2007
    By Nuvoix in forum PC Software
    Replies: 2
    Last Post: 17th July 2011, 12:04 PM
  2. [HELP] Another Excel Formula problem...
    By DJ Overdose in forum PC Software
    Replies: 3
    Last Post: 9th March 2010, 01:00 PM
  3. excel formula help
    By tom999 in forum PC Software
    Replies: 2
    Last Post: 19th September 2007, 08:13 PM
  4. Excel question - formula
    By easy in forum The Dog and Duck
    Replies: 2
    Last Post: 22nd May 2007, 09:02 AM

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
  •