Edited by author 16-11-2009 06:49 PM
Is it possible to print comments 'at end of sheet' ordered by column then row rather than what appears to be the default of row then column?

Gary,

It's in the post, thank you.

Dugs

/m2280
Dugs,

Don't know what the problem might be but I'd be happy to take a look at it if you want to send it to me. If you like you can send it to 19fortysix@att.net. If not, I understand.

Gary

Gary,

I'm afraid I don't know what a parm is (parameter?), but yes I did change that last bit of each bracketed equation (parm). Tried deleting the first 3 columns of sheet1 to see if the equations would pick up the other 3 rows as they moved to the left, no luck :-(, trouble is that it looks like it should work. Checked the formating of cols 4,5,6 and they are just tagged as general so.........

Dugs

/m2278
Dugs,

Did you change the last parm for each of the INDEX functions to the appropriate column number as below?

In cell E1 enter =IF(ISERROR(INDEX(Sheet1!$A$1:$C$37000,$A1,4)),"",INDEX(Sheet1!$E$1:$C$37000,$A1,4))

In cell F1 enter
=IF(ISERROR(INDEX(Sheet1!$A$1:$C$37000,$A1,5)),"",INDEX(Sheet1!$A$1:$C$37000,$A1,5))

In cell G1 enter
=IF(ISERROR(INDEX(Sheet1!$A$1:$C$37000,$A1,6)),"",INDEX(Sheet1!$A$1:$C$37000,$A1,6))

Gary

Edited by author 12-11-2009 10:43 PM
Gary,
Thanks, it seemed to work on the first 3 columns (pulling out line no, date and time) but not columns 4,5 & 6 (temp/humidity/dew point) which are just straight numbers, nothing appears at all-just blank columns. I've checked that the formula updates itself for the correct values as I've dragged the cells down and that it seems to do. Stumped but getting there! I'll keep on playing with it but have you any other ideas?
(for info, these are datalogger readings of a strawbale structure (shed) built in my garden)

Dugs

/m2276
Dugs,

Add a new worksheet. In cell A1 enter the number 10. In cell A2 enter =A1*2. In cell A3 enter =A2+$A$1. Copy cell A2 down to cell A3700.

In cell B1 enter =IF(ISERROR(INDEX(Sheet1!$A$1:$C$37000,$A1,1)),"",INDEX(Sheet1!$A$1:$C$37000,$A1,1))

In cell C1 enter
=IF(ISERROR(INDEX(Sheet1!$A$1:$C$37000,$A1,2)),"",INDEX(Sheet1!$A$1:$C$37000,$A1,2))

In cell D1 enter
=IF(ISERROR(INDEX(Sheet1!$A$1:$C$37000,$A1,3)),"",INDEX(Sheet1!$A$1:$C$37000,$A1,3))

Continue entering this formula in the cells in row 1 to equal the number of columns in your data changing the last number in the INDEX parameters to match the number of the column to be copied. Change Sheet1 to the name of the sheet where your data is stored. You can also name the range of cells containing you data and use the name in place of the sheet and cell references. Copy these down to row 3700.

The advantage of this method is that you can reduce the number of lines of data brought from the data sheet by increasing the number in cell A1 (i.e. if 20 is entered in A1 every 20th row of data will be displayed).

Hope this helps,
Gary

Edited by author 09-11-2009 08:16 PM
Sorry, I'm only now getting to use excel whilst doing my thesis. I have 37k lines of data (more to come)from each of 10 probes that were set to sample at 1 minute intervals. I now need to do graphs and find that excel can handle 32k lines.
How could I sift the data to use every 10th reading so that I can move it to a fresh worksheet for further manipulation? I've tried having a go manually but it's just not worth it!

nickdugs@hotmail.com

Ray
Sorry correction (out of practise). Assume this weeks cell ref = e1 and last weeks = d1, in this weeks cell Choose Format, Conditional formatting, then choose "Formula is" and type = D2<>E2 then in formatting choose patterns and a colour. You can copy conditional formatting using the format painter.I.e with cursor in formatted cell, Click on format painter then Click and drag over remaining cells to be formatted.

Ray
You can identify changes when updating your spreadsheet by using conditional formatting. If you have a column/row with last weeks figures in and a column with this weeks figures in. Then choose this weeks cell, choose Format, Conditional formatting, choose Cell value is, not equal to,type last weeks cell ref. and in Format choose patterns and choose a colour. Copy formatting to other cells. These cells will automatically colour when value is different. As for email, pass.

Holli
To clarify option 1. You need to create a calculation column otherwise you have a circular calc.
Eg Say B1,B2,B3 had values 4,5,and 6 respectively, andC1,C2&C3 had values 7,8,&9. Suppose you want your answers in column D. Create a column elsewhere, it can be anywhere but we will use column E. You have existing control values here. I.e. E1 is 23, E2 is 40, and E3 is 12. We want to get the product of B*C but only if it is less than our control value. Therefore in our result cell D1 we type =IF(B1*C1<E1,B1*C1,E1). (I always think it helps to think of the first comma as meaning “Enter” and the second comma as meaning “otherwise Enter”) Copy the formula down to other cells. If you don’t want to see the calculation column (E) select it and go to Format, Column, Hide. If you just want blank or zero values look at simpler options.

Holli. After the last comma in function,ie answer when false, type the existing cell reference.
Or if you wish to leave blank you can enter 0 and in options choose not to show zero values.
Or enter "" in function. This will format cell as text, however, and may cause you difficulty if you intend performing any further calcs on that cell.

within an IF function, how can i leave the cell contents as is when condition is false?

I have a spreadsheet to which values for equipment levels on all of our sites are added weekly. Is there a way in which all changes can be automatically spotted and uploaded to an email?
Sorry if this sounds a little vague. The guy who built the document has now left and I would like to make it easier to spot the changes.

Thanks.

in A2 type '=A1*1.15'


>
< replied-to message removed by QT >