Creating A Worksheet With Condtional Number Of Columns/rows
Sep 28, 2008
I have searched the forum and the 12 pages of results that had the words conditional clumns, but could not find the answer. I am new here so I don't want to ask a question that has been asnwered before. If I missed it I apologize.
I would like to create a spreadsheet that would "automatically" create the proper number of coumns or rows to satisfy a certain set of data. This may be better explained by way of example.
I do financial analysis of real estate. This real estate is say 100 lots today and these lots are going to sell out evenly over a 10 quarter period, or 10 sales per quarter. Simple enough to set up and show the proper cash flow. But lets say I wanted to chnage the sell out period to 8 quarters. Today I would have to manually delete the last two columns. Further let's say I want to run an analysis that has a sell out over 13 quarters. Then I need to add back in the proper number of columns and make sure I correctly copy over the formulas etc.
I'd like a way to have the spreadsheet automatically determine the proper number of columns (or rows) based on a formula that is part of the input data.
I would like to search across a number of rows in a worksheet and then match against two columns.
e.g.
Column B Column C
test 10
I would like the function to match against all rows that match against "test" in Column B and then work out which cell in Column C has the highest value
I'm looking to use excel to track a number of expense items accross 15 worksheets with up to 500 rows accross 30 + columns per worksheet. Many of the learned people in this forum have helped me get this far, now I need some more assistance - please.
In my speadsheet I have a vlookup formula that returns a value from another worksheet. Here's an example.=IF(ISERROR(VLOOKUP($D3,Room_Configs!$A$1:$BO$3006,MATCH(M$1,Room_Configs!$1:$1,0),FALSE)),"",VLOOKUP($D3,Room_Configs!$A$1:$BO$3006,MATCH(M$1,Room_Configs!$1:$1,0),FALSE))
This works brilliantly. Now here comes the tricky part. What I'd like to do is append that formula with another one to do a vlookup on a second worksheet.
If both lookups return a value then I'd like the value of the 1st vlookup returned in the cell. If the value of the 1st vlookup is "0", then I'd like the value of the 2nd vlookup returned, and if the 1st and 2nd vlookup values are blank then a "0" is returned.
The name of the 2nd work sheet is "Non_Network_Equip"
Finally, it would be really great if the font colour for values returned from the 2nd vlookup forumla was blue.
I have 800 rows of patient information, I need to sample 70 patients. I used the Random.org randomizer to get my 70 patients. That randomizer only gives me the line numbers such as 1, 8, 23, 45, 70, etc. I then need to go to Excel and just pick out the patients on those lines I was given above. Is there an easy way in Excel to have my spreadsheet of the 800 rows and create a (simple) calculation that will pick out the line numbers that I need all at once, so I do not have to go thru manually and choose the data from row 1, 8, 23, 45 etc to make my new spreadsheet with only my sample patients on it. OR is there a easy way to pick 70 random lines from a spreasheet..
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
I work on a daily basis with spreadsheets in excel. The number of columns is the same, but every single spreadsheet has a different number of rows. I recorded this macro in a table with 1196 rows and I would like to use this macro also in other tables with a different number of rows.
I have got an excel worksheet containing about 5000 records. However my excel file shows rows numbered up to 13000. All rows from 5001 onwards are blank.
The problem that i am having is that the scroll bar becomes smaller and it becomes difficult to navigate to a certain point within the sheet.
Is there a way i can delete or limit the number of rows that appear in excel. I have tried to select the rows from 5001 onwards and delte them but it doesnt work and i still have over 13000 rows.
I have a worksheet with a series of data in rows. Every 40th row is a sum of the previous forty rows.
I have a second worksheet that in a series of columns uses the 40th row sums from the other sheet. So column 1 uses row 40, column 2 uses row 80 data, etc.
Every time I update this, I create the new column using a copy and insert copied cells and then have to go to each field on the new column that uses this other worksheet to correctly update the formula for the new row. As when I do it this way, if Column 1 cell a1 formula said "worksheet2!A40, then the new column 2 cell b1 says "worksheet2!B40, where I want to make it be worksheet2!A80. I have tried highlighting a series of these corrected columns and dragging the formula over but still get a change in columns vs row from the other sheet.
I am looking to see if there is a way to do something like (in layman's expression):
I got a worksheet here. I'd like to lock all the cell height and width using protected sheets function. I realized from time to time I have the need to hide them. How do I enable hiding sheets while maintaining cell integrity?
i have data in 1 worksheet say sheet1 which i copy to another sheet say sheet2. i do some processing like using some formulas on the data from columns A to D and getting an output in column E in the same sheet. Now the problem is, before i start the calculations in sheet2, the file size is 400 kB... and after the calculations are done, it becomes 20 mB... i dont know why this happens... there are some things which i think might be a reason but not very sure...
1. i calculate the last row in column A using lastrow = Range("A65536").End(xlUp).row
i tried Range("A500") and it seemed fine... Now u may tell me that i can just use 500 and make things simpler but its just a workaround and not a solution...
2. i use macros.. this is just an info...
3. Another thing is, if i use 500, the number of active rows is 500... i dont know whether the right word is active rows but wat im trying to say is, the scroll bar for the rows movement reaches row 500 when it reaches the bottom.
Calculate the number of rows used in sheet. for exa: i have A1:A100 i want that B1=100 and if i delete some of rows the number of rows in B1 auto-change. and can let B2 display the number of rows deleted.
i have 2 sheets now, can the Cell C1 in sheet 2 display the number of rows in the sheets, B1 in sheet1 + B1 in sheet 2
I have this worksheet that I did not create and notice it has entire rows and columns completely gone / deleted. For example, the only columns it has ranges from A to AI and then no more columns exist in the worksheet. For the rows, it has rows 1 through 77 but then it shows a gray background.
How did they do this? What is this called? How can I do this to Worksheets?
I used the code in the link for "Create Worksheet Index" you referenced and it works great. Is there a way to have the Index and the "back to Index" links appear in separate stationary windows on the left side of the spreadsheets?
In my Macro , i add a worksheet and depending on certain conditions i decide whether to populate it or not.
At a later point of time i am checking if the newly created worksheet is empty or not using the following check :
If ActiveWorkbook.Worksheets(ISheet).UsedRange.Rows.count <> 0 Then
Now what i have noticed is that even in cases where the "ISheet" is empty the above condition evaluates to true for the first attempt. I cannot understand why ?
let me know if the query is ambiguous and i will post the code snippet
I have a spreadsheet which contains data needing to be re-formatted into a suitable format for my database. So far I have been doing this by hand, but want to make it quicker as I have over 2000 lines to sort out. I am trying to write a macro that determines whether there is data in the column next to the active one (D2), and if there is counts how many columns there is data in. Once it has done this it needs to insert the same number of rows as there are columns, then copy the column data and transpose it into the empty rows. My macro seems to work at first, but it exits after around the 4th row, but I can't work out why.
Sub split_For_Database() Dim No_Of_Cells As Integer Dim Start_Cell As Range Dim Cell As Range Dim LastRow As Range Dim i As Integer Dim lRows As Long Set LastRow = Range("C65536").End(xlUp) i = 0 Set Start_Cell = Range("C2") Set Cell = Start_Cell Do While Start_Cell.Address <= LastRow.Address Do While Cell.Offset(0, 1) > 0 i = i + 1 Set Cell = Cell.Offset(0, 1) Loop...................................
in another tab, I want to be able to manually input a value, in this case account, (1111 for example) and have it return all the values in account(1) (in this case b5, a2, c5, e7). Once I have that, I can do a vlookup to get the "value" which is what i need, but I just can't quite seem to get it to do the first part - I input account and it returns all account(1) values.
I've got a column with numbers. some of the numbers have the following signs/symbols proceeding the value in the same cell: ',ft,#,ea,each. I want to have these removed by a formula so that the numerical values are alone.
I have no experience with VBA programming. I am currently trying to create a function that sums the cells that have a specified background color applied by conditional formatting.(ie red) I have tried working off an example that was displayed below
What I am trying to do is to look at specific columns, then copy the data in that column from specific rows from sheet 1 (named TIA) to sheet Macro1. I think uploading a sample of the spreadsheet would be useful.
Unfortunately the spreadsheet is a living document and continues to grow in both column and rows.. The data extracted at this point is from row 7, 23-60 and copied into the new worksheet starting at A1.
Requirements:
1) Row 5 states the macro the column will be associated with. There can be more that one macro associated to a column.
2) When column is found, data from row 7 column (x) will be copied to sheet macro1 EX. If Cell G5 = macro1 then copy data from G7 to sheet macro1 at A1
3) When column is found, data from row 23 column (x) will be copied to sheet macro1
I have requirement to extact data into a spreadsheet. This data is extracted from CSV file which is huge normally over 7MB. I have found a macro on the internet which I have included with this post.
Sub split() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double 'Ask User for File's Name FileName = InputBox("Please enter the Text File's name, e.g. test.txt") 'Check for no entry If FileName = "" Then End 'Get Next Available File Handle Number FileNum = FreeFile() 'Open Text File For Input Open FileName For Input As #FileNum .................
I have also included reult data that I get after running the macro. I have cut down on data due to attachement restrictions. But usually there are multiple spreadsheets named "Sheet1, Sheet2, Sheet3 and so on". I have numerous requirements. First of all I want to be able to spreate data into columns. For now how I do that is by going to data -> text to columns and then selcting delmited and then selecting comma as my delimiter. Second thing I want to do is is only extract range of data from this output into a new worksheet. I am only interest in the name like for example ALBANY-Serial0/0/0 and data that is in the range of 8:00 AM to 6:00PM. So the new sheet should only have name and for that name data in the range of 8:00AM to 6:00PM for all the sheets "sheet1, sheet2 etc" until all data has been extracted.
I would love to be able to limit the number of rows in a specific sheet so that I can quickly carry formulas to the bottom of a worksheet without carrying them to row 1,048,576. I'm aware of options to hide rows and/or to limit the scrolling with the "view code" option on the worksheet tab; however, I'm mainly worried about worksheet performance. When I carry a formula down, I don't want to see a spinning blue circle run for minutes at a time, and I'm hoping to avoid the same spinning circle when I undo my formula. Is there any way to actually limit the number of rows, or is there any other limitation I could put in that would increase worksheet performance?
