#### Inserting rows with Data, Formula's and Validation

```Hi all,

I posted this twice earlier today, but it hasn't appeared so if it triple

I have a spreadsheet that starting at row 18 contains the following
information
A18=M\$2
B18= Datavalidation indirect (\$M\$2)
C18=IF(B18="No More
Options",VLOOKUP(\$B18,INDIRECT(CONCATENATE(\$M\$2,"_info")),1,FALSE),IF(B19<2,VLOOKUP(\$B18,INDIRECT(CONCATENATE(\$M\$2,"_info")),1,FALSE),CONCATENATE(B19,"
x ",VLOOKUP(\$B18,INDIRECT(CONCATENATE(\$M\$2,"_info")),1,FALSE))))
E18=IF(ISERROR(VLOOKUP(\$B18,INDIRECT(CONCATENATE(\$M\$2,"_info")),2,FALSE)),"",VLOOKUP(\$B18,INDIRECT(CONCATENATE(\$M\$2,"_info")),2,FALSE)*B19)
G18=COUNTA(INDIRECT(A18))-1

Columns D and F are 0 Width because they contain information in other rows
that are not to be seen

What I want to do is if B18 does not equal specific text "No More Options"
it will insert two rows with the same formulas and datavalidation at row 20,
but set the value of B20 to "No More Options".  then start the whole process
all over again starting at row 20 and inserting at row 22 if B20 does not
equal "No More options" continuing until the maximum amount has been reached
(cell G18)

Doug Manning
```
 0
9/16/2005 11:26:42 PM
excel.misc 78881 articles. 5 followers.

0 Replies
927 Views

Similar Articles

[PageSpeed] 3

Similar Artilces:

How do I insert Roman Numerals?
New to Word 2007 would someone please explain to me how to insert Roman Numerlas in my document? "ckj" <ckj@discussions.microsoft.com> wrote in message news:6CF0C580-BA6E-463D-B4FD-E6A6FDD9E814@microsoft.com... > New to Word 2007 would someone please explain to me how to insert Roman > Numerlas in my document? Just type them... I II III IV V VI VII VIII IX X XI etc etc. Type a number e.g. 21, select it and run the following macro Dim oRng As Range Set oRng = Selection.Range oRng.Fields.Add oRng, 34, oRng.Text & " \*Roman"...

Linking a Column List to a Row List #2
Thanks for the help, it worked perfectly -- Mo ----------------------------------------------------------------------- Moe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1250 View this thread: http://www.excelforum.com/showthread.php?threadid=27428 ...

I'm a newbie who's got a five-column worksheet and about 900 rows, most of them already typed. Grateful if someone could advise how to shade all of the rows in an alternate fashion, i.e., light-gray, then dark-gray, etc. Thanks! AB Hi see: http://www.cpearson.com/excel/banding.htm -- Regards Frank Kabel Frankfurt, Germany Alex_Bachrach wrote: > I'm a newbie who's got a five-column worksheet and about 900 rows, > most of them already typed. Grateful if someone could advise how to > shade all of the rows in an alternate fashion, i.e., light-gray, then > dark-...

Excel
When using auto-filtering, how can I "number" how many records are displayed. Your question has fallen into an Access news group. Try an Excel news group for a better chance at getting a quick answer. -- Duane Hookom Microsoft Access MVP "Rachel" wrote: > When using auto-filtering, how can I "number" how many records are displayed. "Rachel" <Rachel@discussions.microsoft.com> 在郵件 news:DE18E8EF-5A6D-4661-A768-41D3B4D85D6C@microsoft.com 中撰寫... > When using auto-filtering, how can I "number" how many records are ...

Not plot a data series in a chart but still display in data table
MS Excel 2007 I've somehow managed to display only two data series on my line chart. They still display in the data table. How did this happen? More importantly, how do I reverse it. I don't want to recreate chart. I see the data series not plotted on chart in the Chart Elements and when I select the series, the source data is highlighted; it's in the data table. How do I make it visible? Thanks! Miriam HAve you perhaps simply formatted the data series as "no line, no marker"? If so, you can format series, add some color. -- Best Regards, Lu...

How To Copy Rows without Work Art
I have some Word Art in say Rows 9-23, I use some Macro code to copy cells 9-23 down to 10 grouped rows below 23, but everytime I run the Macro it also copies a 'New' Work Art over the existing Art below Row 15. My fear is that my file size will balloon. My code simply states Sheets("Starters").Select Rows("9:23").Select Selection.Copy Range("A25").Select ActiveSheet.Paste Range("A41").Select ActiveSheet.Paste How can I copy Rows 9-23 without the WorkArt also (which is 'floating' over Rows 14-18) Thanks Anot...

Insert trigger
Looking for some advice on SQL 2005. I have a table that will usually be populated by an SSIS package. I want to set the "loaddate" column to the current time after a record is inserted. Should i do this via trigger or should i just build a step in the SSIS package to update the column after the file loads? If trigger is the way to go, what is the syntax to create the after insert trigger? Thanks in advance. You can create a default constraint on the table set to CURRENT_TIMESTAMP. That will handle the automatic date assignment without any need for coding. -...

Automatioc spill over data to a new Excel sheet from Xml source?
I have an XML source file with data for more than 256 columns. Since one sheet can contain a max of 256 cols, Is there a way to make Excel, create an additional sheet to keep the excess data? Preferrably using Xml map / template? ...

PROTECTING FORMULAS #2
I have a sheet where when opened, it runs an auto macro to move cell contents from one column to another and uses a formula to do some calculations. If I select the columns I want unlocked, then protect the sheet and try to run the macro, I get an "run time error 1004 unable to set the numberformat property of the range class" When I debug the following is highlightd "Selection.NumberFormat = ";;;" " I was a Lotus user and in Lotus I could select a range of cells or columns to protect, I'm having a hard time to get Excel to do the same. I appreciate th...

How do I get only the data I want to be shown?
I have approximately 2000 rows of data all with strange data in it. Each row starts with positions of stars in sexagesimal format. I want to filter through these so that I only have the ones I want (approx. 600), followed by the data in the row that corresponds to the position. I have the list of the ones I want in a seperate column (i.e. there positions). How do I get them? I hope this makes sense. Thanks! Two approaches come to mind. 1) Use an advanced filter. Click in the larger table and select Data > Filter > Advanced Filter. The 'database' should be the larger t...

How to retrieve data which contain "/" in it
Hi, I try to pull a data from access with contain a speacial character "/" in it but what i get is it not return a single data, so how do i wanna retrieve it? below is statement I use it but it return nothing. SELECT LDDATA.COR, LDDATA.CON, LDDATA.PCODE, LDDATA.QTY, LDDATA.BOXNO, LDDATA.CASENO, LDDATA.RSNOS, LDDATA.RSNOE, LDDATA.NOFRS FROM LDDATA WHERE (((LDDATA.COR)="A11/LC/000002/11/06")) ORDER BY LDDATA.CON, LDDATA.BOXNO; On Tue, 22 May 2007 21:53:02 -0700, osmanar <osmanar@discussions.microsoft.com> wrote: >Hi, >I try to pull a data from access with...

How do I prevent duplicate entry of a sku in a data table?
I want to keep folks from entering the same sku into a table (i.e. like data validation does for other parameters). Assume that you want to restrict the users from typing the duplicate values in A Column. Place the cursor in A1 cell and press Cntrl+Spacebar which will selecte the A Column. Keep in mind that the active cell should be A1 cell. (Active cell will have a white background after selection also) Data >>Validation>>Settings>>Validation Criteria>>Allow>>Custom>> and in Formula paste this =COUNTIF(A:A,A1)=1 Data>>Validatio...

Grouping Data
I have a subform that shows a usual set of data such as: Name1 Product1 Name1 Product2 Name1 Product3 Name2 Product4 The client wants me to be able to do the following: Name1 Product1 Product2 Product3 Name2 Product4 I.e. merging data cells where they are the same value Preferably with Name1 being centered across the products. Within a straight access form I would use a group via crosstab however this is an access adp on SQL Server. Can anyone help? In a report you can hide duplicates. -- KARL DEWEY Build a little - Test a little "BecksWatkinson@gmail.com...

empty rows at bottom of worksheet
My worksheet has about 66,000 empty rows at the bottom of the sheet and that affects my usage of the scrollbar on the right of the screen. Anyone know how i can get rid of those miscellaneous rows? Michael If you will turn on the page break preview (piece of paper with a magnifying glass) which is the icon on the on the Standard Toolbar to the right of the Printer icon it will show you exactly what is going to print. There are solid blue lines that the edge of the page, the dashed blue lines are soft page breaks. Find the solid blue line at the bottom of the spreadsheet and drag it ...

inserting hrs and minutes
I have a cell in my time card that displays total weekly time -ex- "40:15" is there a way to make it more like this...40hrs,15mins -- Message posted from http://www.ExcelForum.com Use a custom format hh"hrs",mm"mins" -- Regards, Peo Sjoblom "-Brian-H- >" <<Brian-H-.110wgs@excelforum-nospam.com> wrote in message news:Brian-H-.110wgs@excelforum-nospam.com... > I have a cell in my time card that displays total weekly time -ex- > "40:15" is there a way to make it more like this...40hrs,15mins ? > > > ...

Worksheet name in cell formula
I asked this question a while ago and got a prompt answer which I thought was what I wanted but alas its not... I want to be able to change my worksheet names.....ie: from Sheet 1, Sheet 2, etc etc.....to something more meaningful.....eg Sales, Expenses....etc etc.... and have these changes reflect on the worksheet. For example, I might want Sheet 1 Cell A1 to say.....This is the Sales worksheet (assuming I have renamed it to Sales). The answer I was given some time back worked.....but it changed the formula on every worksheet to reflect the name of the last changed sheet. By this I mean.....

insert an interactive excel file into word web page
I'm trying to insert a excel file into a word document with text, and then save it was as a web page, but I want to keep the excel part interactive. Any ideas? ...

Insert | File > Attachmnet-Button Drop Down ;What is the difference between Insert and Insert As Attachmnet
re: "Outlook2003, File-Insert-Options" On making new-email with Attachment-File(s), ** File Menu | Insert | File >>> (Brows and select File to insert ) then we can see the button "Insert", and write side Drop Down Arrow lower-right side of Dialog Box; If it clicked, we can see three options as follows: ** Insert Insert as text Insert as Attachment I can not recognize/understand the difference between "Insert" and "Insert as Attachment" *** What is the difference between Insert and Insert As Attachment ? I would appreciate y...

Empty rows
When I do Shift+Page Down+End+Arrow Down from the first selected row, it goes to row 65,536. How do I get it to go to the end of the report (the last typed row)? Connie Hi clear your used range first: http://www.contextures.com/xlfaqApp.html#Unused -- Regards Frank Kabel Frankfurt, Germany "Connie Martin" <Connie Martin@discussions.microsoft.com> schrieb im Newsbeitrag news:61708D1A-8D45-4213-A35A-54953A8C90E5@microsoft.com... > When I do Shift+Page Down+End+Arrow Down from the first selected row, it goes > to row 65,536. How do I get it to go to the end of the re...

Inserting Hyperlinks in a Protected Sheet
Hi I run Excel 2000 and I have a protected worksheet that I share wit users in my organisation. I want to allow the insertion of a hyperlin to a specific file type within a specified directory on our server. 3 Questions: 1.Protection on disables the insert hyperlink command. Can this b overcome with worksheet activate code? 2.Can I limit the types of files (preferably by requiring the file t meet a mask format eg "z-*.xls")? 3.Can I limit the directory that can be linked, by referring to pathname stored in a cell on the active sheet? Would appreciate your suggestions. Thanks S...

Colour numbers within my formulae
I have been playing around with the '&' function, allowing me to mix text with calculations and cell references. I have the following at the moment: ="Of the "&TEXT(J954,"�#,0.00")&" made this year, I have managed to save "&TEXT(100/J954*J955, "#,#0.0")&"%" Which gives me a cell displaying the following: Of the �0.00 made this year, I have managed to save 0.0% What I would like to do is show the numbers (�0.00; 0.0%) in the colour blue. I don't want to add conditional formatting, just apply a colour so t...

Calculation of weather data help
Hi, I have a data file (at http://www.weatherweb.net/cliall.dat) which contains daily weather data from 1881 in the format: dd,mm,yy,max temp,min temp, rainfall, sunshine, wind speed, wind gust, pressure What I would like to do is to calculate means for max, min, wind speed, wind gust and pressure, and also total rainfall and sunshine for each month. I would like the output in the format: mm,yy,mean meax, mean min, total rain, total sunshine, mean wind, mean wind gust, mean pressure Can anyone give any assistance as to how I might do this in excel to create a sngle file contain all th...

showing data values on chart
in excel when mouse stand on chart,excel is showing the data values of that point.how can to do this action in access? Exactly the same. When you hover the mouse over a data point for a little amount of time, a yellow bow should come up describing what's in your data point. -- Regards, Steve "afshin" wrote: > in excel when mouse stand on chart,excel is showing the data values of that > point.how can to do this action in access? Where you insert chart?i insert chart on report,and i can't to see data value on data point. "Steve"...

How to copy specific columns until end of data
I'm trying to import data from 2 workbooks into a summary (combination) workbook. The trouble I'm having, is I want to import only certain columns and have excel know to stop at the last row with data. For example, from Workbook1 I want to import columns A, B, C, F, and G (from Sheet2). I want it to copy up to the last row containing any data--right now that's row 1046, but next month it could be row 1267. In workbook2 I'm fine with it copying the CurrentRegion--all columns are important. BUT, it doesn't include the title/category heads in the first row (Salesperson, ...

Insert with a where condition
Hi, sql 2005 I have an insert statement that is ignoring the where condition. That is, I want to insert records when they do not already exist in the destination table. INSERT INTO dbo.tblmnuGroupPerm ( gId ,mtfID ,... ) SELECT @gID ,mtfID ,... FROM dbo.locmnuTabFunction AS ltf WHERE ltf.mtfID NOT IN ( SELECT gp.mtfID FROM dbo.tblmnuGroupPerm AS gp WHERE gp.gId=@gID AND gp.Deleted=0 ) Any ideas or recommendations appreciated :-) Many thanks, Jonathan It's OK... <oops "redFace">I did not correctly se...