• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Inserting rows using IF function

D

djayv8

New Member
Hi excELITES,

Good day. Please help with this doubt. Can we insert a row after a particular row using IF function for a particular value in a cell.


Eg: IF(d11=1, if true insert 3 rows after A20, else any false condn)


Or else for the same IF function can we paste the particular block(rows x columns) from another place

Eg: IF(d11=1,if true insert or paste (r x c) from another place in the sheet, if false insert or paste from another place )


I am new in PLAYING around with excel. Kindly please help.

Regards

DJV
 
Djayv8

You can't directly do what you are suggesting


You can however arrange your worksheet so that it appears that rows have been inserted, where in fact all you have done is shift the data down the sheet using formulas.


It could be done using vba,


Can you explain more fully what you are trying to do and why?
 
Hi Hui,

I am just taking a frame (its a signal, consisting of hexadecimal characters) and then each hexa decimal character means some information about the particular signal. its like decoding the signal. we have reference documents for them. i assigned the equivalent values for hexa decimal characters using if and lookup function. but in certain cases eg if the length of the frame is entered as (0f)15 in d11 then there should be 15 rows coming after d11 in a pre defined standard format. so like wise whn length is 0c there should be a 12 rows coming in standard format. it ll be good if i am able to insert a pre defined set of rows and colums for a particular lenght from a different place. i mean i do a pre defined set of r x c in the same worksheet and then jus attach them after that length row.

i think i didn confuse u too much. if i am able to load the file or chat over i believe i can be able to explain u clearly.kindly please help. thanks
 
my data is a series of hexa decimal characters. the no of hexa decimal characters varies.

so there is one hexa decimal character which tells how many hexa decimal characters are going to be there in a single data. so i planned to insert rows acc to the no of cell. more over i have to insert rows only at a fixed posn i.e after a particular hexa character.


FRAME IN HEX FRAME IN BINARY DETAILS OF FRAME CHARACTERS

10 00010000 START CHARACTER OF FRAME WITH FIXED LENGTH

0c 00001100 LENGTH OF THE FRAME IS 12

0f 00001111 LENGTH OF THE FRAME IS 15

10 00010000 START CHARACTER OF FRAME WITH FIXED LENGTH

8 00001000 CONTROL CHARACTER BIT 8: RESERVED; BIT 7:

2E 00101110 CASDU1 IS 46 STATION ADDRESS IS 8750

22 00100010 CASDU1 IS 34

22 00100010 TYPE ID 34 MEASURED VALUE_NORMALISED_56TIME

1 00000001 VARIABLE STRUCTURE QUALIFIER - ZERO SEQUENCE

1 00000001 COT PERIODIC,CYCLIC

2E 00101110 CASDU1 IS 46 STATION ADDRESS IS 302

1 00000001 CASDU1 IS 1

AD 10101101 INFORMATION OBJECT 1 IOA1=173

0 00000000 INFORMATION OBJECT 1 IOA1=0

2 00000010 INFORMATION OBJECT 1 IOA1=2

1 00000001 INFORMATION ELEMENT WITH QUALITY DESCRIPTOR;

D1 11010001 TIME OCTET 1

3 00000011 TIME OCTET 2

0C 00001100 TIME OCTET 3

FC 11111100 CHECK SUM

16 00010110 STOP CHARACTER OF FRAME


its something like this according to the third row i.e length =22 i have to insert 7 rows after time octer 3, and if length is 29 then i have to insert 14 rows. the 7 rows i mentioned here are going to contain the same formulas and type as from cells IOA1 to time octet 3. so it will be ok if the same set of 7 rows are inserted acc to the length of the cell.


here the the hexa characters are in one column and then the binary nos and then the texts.. so 3 columns in total

thanks and regards

DJV
 
HI I got an idea for inserting the set of row and columns.. i am using the group command. it looks much better.. the thing is u should need to know a bit about the signals then its easy. but now i got another challenge. when i protect the sheet. the group is not working... i mean i am not able to collapse and shrink the group. it says to lift the protection. any option to use this function while in locked mode.

PLS help..
 
hi hui.. the full set of rules i dono how to post over here.. any ideas on how i can explain you.. may be i can share u the excel file...
 
You will need to know what set of rules you want to apply


Have a look here for sites you can post to:

http://chandoo.org/forums/topic/posting-a-sample-workbook


Make sure your data is anonymous if necessary
 
hui please check, i have added the file. there are ssome grouped rows in the first worksheet. those are the rows i need to add w.r.t the lenght row. and also now i have grouped i m not able to collapse the group after protection. id there any way to allow collapsing even when locked. pls help

http://rapidshare.com/files/414777878/101FRAMEANALYSER.xls
 
There doesn't appear to be any info which specifies how many information blocks there will be? Which is ok as there is an end block


Are you trying to Make a File or Analyse an existing File ?

Can you upload an existing Data File?

What do you ultimately wanty to achieve with the data?
 
ultimately the end user will have the frame in hexa column only. as the user enters the frame in hexa column the other columns will be calculated from the formulas entered in their respective columns. the block on the top of the sheet is the OK one. i am making an excel file to analyse the data which is got from another software. the data here refers to the hexa characters in the frame in hex column i.e column D.

thnx
 
Djayv8

If you are making a new file,

Populating columns E & F can be done using formulas or a lookup

You will probably need to use some VBA to add rows based on entries in Column D

You will need a set of rules as to how many Information Object blocks are required and what variances are allowed in those blocks

What happens if you have different Data Unit Identifiers etc


If you are analysing a file, then I don't understand your approach, as the file will already have a predefined set of Information Objects ?
 
the data unit identifier is standard only the same. there wont be diff data identifiers. it wont change other than what is written in the file. And yes u got my point there will be multiple information objects(IO). thats the ONLY varying property. The no of IOs are purely based on only D5 or D6.FYI the value for both the cells will always be the same.


And also u r very right that If i am analysing a file there will be predefined IOs. but each file can have different IOs. So i think it will be nice if the user, who wants to find the meaning of the file i.e column E&F, gets the no of IOs inserted according to D5 or D6. for one IO length is 15 so for two IOs the lenght will be 15+7 = 22 in hex its 16. for three IOs the length will be 22+7= 29. the length here means from D8 till before the check sum row(D55). since i have done the IO blocks below i just need to insert them . so acc to the length the user will automatically get the no of IOs so he doesnt need to get confused. just enter the numbers.


But i dono VB, i thought it will be nicer if there is any problems.


I think u got my explanation above

Thanks
 
Good day sonia salgotra


It is not good manners to post your question in some one else's post. Also by burying your post in another other forums user will not see it as they may not open this post, there are only two members talking in this post Hui and djayv8 which means others may not in a position to help.Also it is a very old post, how do you know if the posters are still active in the forum. Start a new post with a good description of your problem
 
In addition to what BobHC has said, If you read the comments here you will see that you cannot Insert Rows using formulas, except to trigger some VBA Code which will do it for you

What you can do is to retrieve data and have it appear as if the data has been re-arranged
 
You must log in or register to reply here.
Back
Top