Excel Help

[trishan9390]

Does anyone know how to use the value of a cell from one sheet on another sheet? For example I have a whole lot of data on one sheet and on my second sheet I want to use a figure from the first sheet.

Or is excel full of sheet and you can't do this?

 

[lusk]

Yeah can be done put ="sheetname"then a "!" then "cell reference"

eg. =sheet2!C8

or just put a "=" sign in the formula bar and go into the sheet and highlight the data and it will reference it automatically.

 

[r m]

You can, in the destination cell type "=" then click on the sheet containing your source data, then click on the source data cell, then hit enter.
You could name the source cell, click on the source cell, then click on the section in the toolbar which shows the cell you are in, something like a10, then in that box type a name for that cell, e.g. "total cost". In the destination cell on the different sheet you then type in "=" then press the F3 button and up pops a box with the names of any cells you have named. Select the named cell you want in the new sheet.
You can use V or Hlookup.

 

[trishan9390]

Thanks guys, much appreciated.

 

[Yeti]

I am having exactly the same problem as is described here:

http://malektips.com/microsoft_excel_2003_function_0002.html

Formula to Sum Up All Values in Column

SUMMARY: Quickly calculate the summed value of all cells in a column of a Microsoft Excel 2003 spreadsheet.

Normally, to sum up the value of a particular column, you could use the SUM formula. For example, if you have values in column B between rows 11 and 33, you could use this SUM formula to get the total value:

=sum(b11:b33).

However, what if you keep adding values to column B? For example, if you placed a value in row 34, you would have to modify the above SUM formula to the following:

=sum(b11:b34).

This can get quite annoying. Instead, use the following shortcut to place to sum of an entire column in one cell:

=sum(COLUMN:COLUMN)

Which, in this example, would be:

=sum(b:b).

Note that you cannot place this formula in column B, else Excel 2003 shows a circular reference error. Instead, you can place this formula in any other column besides the one you are summing up.

This is a partial solution to my problem, but maybe not the most elegant one?

How can I get the bottom cell of a column to always show the total of the above cells, even after adding or deleting rows?

 

[cutz]

Hi,

When I download price history from my broker into excel which I can only seem to do by copy/paste function I end up with all the daily data figures separated by a comma in one column.

Is there an easy way of separating this data into separate columns?

Thanks in advance,

Cutz.

 

[dhukka]

Re: Excel Help.

Hi,

When I download price history from my broker into excel which I can only seem to do by copy/paste function I end up with all the daily data figures separated by a comma in one column.

Is there an easy way of separating this data into separate columns?

Thanks in advance,

Cutz.

Yep, in excel go to the 'Data' menu and select 'text to columns' then select 'delimited' and then choose your delimiter, in your case 'Comma'

 

[Whiskers]

Re: Excel Help.

I have the same problem sometimes.

The best I've been able to do is take a column on a page at a time and paste that into excel.

 

[skyQuake]

Re: Excel Help.

Select the column with all the data, then
Data>Text to Column>select 'Delimited'>and use Comma as a delimiter.
That should put everything into columns.

 

[cutz]

Re: Excel Help.

Thanks guys,

It did the trick.

cutz.

 

[cutz]

Re: Excel Help.

Hi guys,

Another query on excel, this time relating to charts.

I have downloaded the data and created the chart that I require but I can't seem to be able to add the dates along the y axis labelling along the bottom of the chart

Any suggestions?

cutz.

 

[skyQuake]

When u are selecting the data to graph, start selection with the time/date column first, then drag across.
If this doesnt work, in Step2 of chart wizard, go to the series tab, and set the Category (x) axis label manually.

 

[cutz]

Thanks skyQuake,

It did the job.

cutz

 

[Gringotts Bank]

Some help with Excel please

I'm trying to edit some intraday data. I want to delete all rows that have a certain character in the first column (column A). Help pls!

 

[skyQuake]

Re: Some help with Excel please

I'm trying to edit some intraday data. I want to delete all rows that have a certain character in the first column (column A). Help pls!

You'll need a macro to do that i think,

an alternate solution is to filter out the rows to blank if it has a that certain character.
Need a bit more info about how its set out, eg Date,O,H,L,C
and what character/where is the character you want to filter out?

 

[Gringotts Bank]

Hi sky, thanks.

screenshot unable to upload, but basic intraday data with lots of extra bits like type of trade.

Column A has mostly 'T'. There are some rows that start with 'C' that I need deleted.

I tried data filter which finds all the 'C' rows but in the process deletes all the 'T' rows.

 

[skyQuake]

Hi sky, thanks.

screenshot unable to upload, but basic intraday data with lots of extra bits like type of trade.

Column A has mostly 'T'. There are some rows that start with 'C' that I need deleted.

I tried data filter which finds all the 'C' rows but in the process deletes all the 'T' rows.

So for example Column A has "C" or "T" and Column B is data

Just have in C2 =if(A2="C"," ",B2)
Which basically checks if its a "C", then returns a blank, otherwise return data from B2
Its not ideal - wills how up as blanks in column c

 

[Gringotts Bank]

"So for example Column A has "C" or "T" and Column B is data"

Yes, but the entire row (consisting columns B-E) needs to be deleted when "C" appears in column A. Not sure if I mentioned that.

 

[skyQuake]

"So for example Column A has "C" or "T" and Column B is data"

Yes, but the entire row (consisting columns B-E) needs to be deleted when "C" appears in column A. Not sure if I mentioned that.

Yeah just rinse and repeat for B~E (ie =if(A2="C"," ",E2) etc
So you'd get blanks in ur data
Getting rid of blanks is a whole different kettle of fish though
Link for that is here: http://www.ehow.com/how_4512014_delete-blank-rows-excel.html

 

[Gringotts Bank]

thank you!