Advanced Excel techniques
Advanced Excel techniques
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Advanced Excel techniques

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am trying to create a pricing spreadsheet that when opened will first prompt the user to input the # of locations for that specific customer. Once entered the spreadsheet is then automatically updated to provide a row corresponding with the # of locations specified. (i.e. if the user inputs 6 locations, 6 rows would appear under the location portion of the spreadsheet)

    I have seen these before but have no idea what they are called. Can someone help me with the name and where I might be able to find an example of how to create them.

    Additionally, I have noticed that when creating =if(x=y,z) statements that I can only enter 8 total conditionals. If I want to expand this, is there a way to do this?

    Thanks,
    Brian

  2. #2
    New Member
    Join Date
    Feb 2002
    Location
    M&T Bank
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't understand what you mean when you say "6 rows will appear". I do know that I've created combo-boxes that generate numbers that can be used in H and V lookups. There is a drop-down list where the user can choose, in this case, the number of locations. Your formulas could be designed to refer to this number.

    Denise

  3. #3
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nested IF statements are limited on a cell level. You can use more than one cells IF nests in a master cell nested IF. This way your nested IF can be any size you want, as long as you use additional cells to hold criteria.

    Your first ?
    Makes no sence to me?
    JSW

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Unfortunately my first question makes little sense to me either which is perhaps the problem. If someone might instead direct me to a good book that could assist me in building more advanced spreadsheets. I am trying to do these formulas with no formal knowledge of Excel.

    Really all I want to do is create an easy to use spreadsheet that would allow my pre-sales engineers to quickly create proposals for our customers. My ultimate goal is to improve the professionalism and responsiveness of our team.

    Since I'm on a roll, I don't suppose their is any way to link the output of this type of spreadsheet to a Visio diagram?

    Thanks for your help,
    Brian


  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi --

    Sorry i do not really understand - and ask why your using Excel multi conditional ifs are complex you say no knowlwdge of Excel so this might be difficult..

    BUT....

    (excel) spreadsheet to a Visio diagram?
    YES this can be done and Visio to Excel as they are both Microsoft software your need to use version Visio Pro or Devoper or what ever its called Technical i think as thats when microsoft bought Visio,


    Sorry i can offer more...

    The question bit ie user lcation your need VBA and its all gets complex, this is hardly a question i would say project, might i suggest post question by question untill you get ALL the answers that way your question will be undrstood and the guys will answer and give the best possible solution. Like i say im sorry i cant help more .

    HTH
    Rdgs
    ===========
    Jack


  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Bd,

    That makes some sense to me. You can only do up to seven if's in one cell. But, you can nest many 'and' and 'or' statements in them refining the limited criteria. Also, if you do an if statement in one cell and the remainder in the next, you take the value of the cell that isn't blank.

    The row inserting sounds like the work of vba via the input box. Somthing like:

    sub prompter()
    iRows = Application.InputBox(prompt:="Please Enter # of Locations", Title:="Howdy", Type:=1)
    Range("A4").Select
    Application.ScreenUpdating = False
    If iRows <> False Then
    For x = 1 To iRows
    Selection.EntireRow.Insert
    Next
    End If
    Application.ScreenUpdating = True
    End Sub


    Change the ranges as appropriate. And do not change irows to rows, holy hannah (looping replacements like I've never seen...)

    Good Luck & Cheers,

    Nate

    [ This Message was edited by: NateO on 2002-02-20 16:58 ]

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Brian

    x = "10:" & 10 + InputBox("Please type the number of locations", "LOCATIONS")
    Range(x).Select
    Selection.Insert Shift:=xlDown
    Range("A10").Select
    End Sub

    The above snippet of code will open an input box asking the user to supply the number of locations. It will then insert that number of rows below row 9. Change both 10s on the first line to suite where you wish the insertion to take place.

    Hope this is of some help
    Derek

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Derek,

    Might want data validation on your input box, in case the end-user inputs text.

    i.e., type:=1

    Cheers,
    Nate

  9. #9
    New Member
    Join Date
    Feb 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks all.

    I thought I knew excel pretty well before this. Its apparent I have a ways to go. Thanks for all your help!

    Brian

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    You're welcome Brian. Good hunting.

    Cheers,

    Nate

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com