Advanced Excel techniques

[bdgray]

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

 

[Denise]

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

 

[Joe Was]

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

 

[bdgray]

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

 

[Jack in the UK]

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

 

[NateO]

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

 

[Derek]

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

 

[NateO]

Derek,

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

i.e., type:=1

Cheers,
Nate

 

[bdgray]

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

 

[NateO]

You're welcome Brian. Good hunting.

Cheers,

Nate