How Can I Change Columns Based on User Input?

[bman100]

I have a worksheet that includes user input regarding the number of months required for the cash flow. I want the cash flow spreadsheet to automatically adjust (contract/expand) the number of columns pertaining to the number of months in the cash flow (the months are listed on the top of the speadsheet). I have some calculations on the right side of the spreadsheet that need to adjust as the cash flow columns adjust. I know I can use the hide feature to hide unused cash flow columns, but I would like something a bit more sophisticated. I cannot find out how to do this, and I have been trying!!!

 

[lenze]

Hi and welcome to the board!!! Probably doable with code, but you haven't given us enough information. You should explain the sheet layout, where and how the number of months needed is chosen, which months to hide, formulas involved in the Calculations, etc. A screenshot would also be helpful!

lenze

 

[bman100]

<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600">

</v:shapetype>
<v:shapetype stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600">Hopefully you can read this. I told a pic of the spreadsheet and linked it to photobucket. Might be an easier way, but don't know it. Here is the link.</v:shapetype>
<v:shapetype stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"></v:shapetype>
<v:shapetype stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600">http://i587.photobucket.com/albums/ss313/brianbauer_2009/ExcelScreenshot.jpg</v:shapetype>
<v:shapetype stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"></v:shapetype>
<v:shapetype stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600">I basically want to expand the cash flow columns to expand/contract to the time period it takes to sell the homes. </v:shapetype>
<v:shapetype stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"></v:shapetype>
<v:shapetype stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600">Thanks,
Brian I</v:shapetype>

 

[bman100]

http://i587.photobucket.com/albums/ss313/brianbauer_2009/ExcelScreenshot2.jpg

Hopefully this is a bit better. Sorry, but I'm a neophyte to this message board. I even tried to download HtmlMaker 2.42, but couldn't find a website to download it.

 

[bman100]

Lenze -

The number of months is calculated from the number of months it takes to sell the homes (based on the absorption or sales rate from the input worksheet). Therefore, I want the cash flow columns to adjust to the number of months it will take to sell the homes (with each month representing a column in the cash flow). We have cash flows ranging from 6 months up to 10 years (which is I would like to have the spreadsheet automatically adjust). The calculations are very simple, but we do have an IRR at the bottom of the spreadsheet this is the only "complicated" formula right now.

Thanks, Brian

 

[lenze]

Try Col's HTML maker at the bottom of the Index page, or PM Richard Schollar with your email and he can send you a beta veraion of the new one or try Excel Jeanie!
http://www.excel-jeanie-html.de/index.php?f=1

lenze

 

[Ralph Gregory]

I have a worksheet that includes user input regarding the number of months required for the cash flow. I want the cash flow spreadsheet to automatically adjust (contract/expand) the number of columns pertaining to the number of months in the cash flow (the months are listed on the top of the speadsheet). I have some calculations on the right side of the spreadsheet that need to adjust as the cash flow columns adjust. I know I can use the hide feature to hide unused cash flow columns, but I would like something a bit more sophisticated. I cannot find out how to do this, and I have been trying!!!

try with vba
this code is assuming columns A and B are for showing your data and all the other columns are jan to december for as many times as you want (until you run out of columns)
you can run this code from a button on the front sheet and this bit goes into the vba for the sheet you are working on

Public Sub HideColumn()
Dim Answer, c As Integer
Dim col As Range
'Hide all columns except A and B (Assuming your first two columns have other data in them that you want to see)
Columns("C:IV").Hidden = True
'Ask how many months to show
Answer = InputBox("How Many Months?", , "12")
If Answer = "0" Then Exit Sub
c = 2 'this is the first 2 columns

For a = 0 To Answer
Columns(c).Hidden = False
c = c + 1
Next a
End Sub

 

[bman100]

Thanks Ralph. I'm going to play with this later today. I ran it and it looks real good, but I need to tweek it since the cash flow is on a separate worksheet (right behind the input sheet). The cash flow columns are set up on the second worksheet, so I will need to generate monthly columns from code. My only issue is how to generate an irr that fluctuates with the expanding/contracting cash flow.

 

[Ralph Gregory]

well have fun with it and with this message board ..everything I've learned about excel comes from here (though it takes a bit of finding)... btw what does irr mean?

 

[bman100]

Internal rate of return. This is probably the most used criteria for judging the financial viability of real property transactions. Technically speaking, it's the discount rate that makes the net present value of all cash flows equal to zero. The higher a project's IRR, the more desirable the project. I know, too much info.