Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Manipulating certain rows only
I am trying to set up a running total of expenses etc, where my first sheet
is a combined running totals, from 4 different accounts, so the rows are colour coded accordingly. I then have monthly sheets, where I want to break down of the running totals according to the account. PROBLEM - Is there anyway of copying only certain rows, such as, all the blue text rows, or the red text rows? I have tried to select the relevant rows, by pressing control and the relevant coloured text for each bank account, but when it comes to copying, it will not allow my to copy multiple rows. Copying each individual row is WAY too tedious, there must be a quicker way! I even tried hiding the colours I didn't want, but when I press copy, it copies the whole lot across, not just the visible colour! Does this make sense, or am I trying to make my life way too complicated?????? My aim is to have a running total on the one sheet, and then I can also check on the individual movements of account transactions and see the balance, if that makes sense?? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Manipulating certain rows only
Other than the colored rows there must be another method of defining the 4
accounts. Have you tried DataFilterAutofilter on account names or account numbers? You can then copy the visible rows to another sheet. Gord Dibben MS Excel MVP On Sun, 17 Feb 2008 15:52:00 -0800, Shaz wrote: I am trying to set up a running total of expenses etc, where my first sheet is a combined running totals, from 4 different accounts, so the rows are colour coded accordingly. I then have monthly sheets, where I want to break down of the running totals according to the account. PROBLEM - Is there anyway of copying only certain rows, such as, all the blue text rows, or the red text rows? I have tried to select the relevant rows, by pressing control and the relevant coloured text for each bank account, but when it comes to copying, it will not allow my to copy multiple rows. Copying each individual row is WAY too tedious, there must be a quicker way! I even tried hiding the colours I didn't want, but when I press copy, it copies the whole lot across, not just the visible colour! Does this make sense, or am I trying to make my life way too complicated?????? My aim is to have a running total on the one sheet, and then I can also check on the individual movements of account transactions and see the balance, if that makes sense?? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Manipulating certain rows only
Good thought, but that isn't going to work, unless you are able to filter
according to colours! "Gord Dibben" wrote: Other than the colored rows there must be another method of defining the 4 accounts. Have you tried DataFilterAutofilter on account names or account numbers? You can then copy the visible rows to another sheet. Gord Dibben MS Excel MVP On Sun, 17 Feb 2008 15:52:00 -0800, Shaz wrote: I am trying to set up a running total of expenses etc, where my first sheet is a combined running totals, from 4 different accounts, so the rows are colour coded accordingly. I then have monthly sheets, where I want to break down of the running totals according to the account. PROBLEM - Is there anyway of copying only certain rows, such as, all the blue text rows, or the red text rows? I have tried to select the relevant rows, by pressing control and the relevant coloured text for each bank account, but when it comes to copying, it will not allow my to copy multiple rows. Copying each individual row is WAY too tedious, there must be a quicker way! I even tried hiding the colours I didn't want, but when I press copy, it copies the whole lot across, not just the visible colour! Does this make sense, or am I trying to make my life way too complicated?????? My aim is to have a running total on the one sheet, and then I can also check on the individual movements of account transactions and see the balance, if that makes sense?? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Manipulating certain rows only
From your response I take it there is no other method of defining each account.
Unfortunate. You will have to use VBA to enter the index number of of each colored row. Then you could filter on that column with the numbers. See Chip Pearson's site for functions to return the index number of a color. http://www.cpearson.com/excel/colors.htm You would place his Cellcolorindex function into a mofule in your workbook. Then in a helper column enter =Cellcolorindex(cellref) Filter on that column. Gord On Sun, 17 Feb 2008 19:33:01 -0800, Shaz wrote: Good thought, but that isn't going to work, unless you are able to filter according to colours! "Gord Dibben" wrote: Other than the colored rows there must be another method of defining the 4 accounts. Have you tried DataFilterAutofilter on account names or account numbers? You can then copy the visible rows to another sheet. Gord Dibben MS Excel MVP On Sun, 17 Feb 2008 15:52:00 -0800, Shaz wrote: I am trying to set up a running total of expenses etc, where my first sheet is a combined running totals, from 4 different accounts, so the rows are colour coded accordingly. I then have monthly sheets, where I want to break down of the running totals according to the account. PROBLEM - Is there anyway of copying only certain rows, such as, all the blue text rows, or the red text rows? I have tried to select the relevant rows, by pressing control and the relevant coloured text for each bank account, but when it comes to copying, it will not allow my to copy multiple rows. Copying each individual row is WAY too tedious, there must be a quicker way! I even tried hiding the colours I didn't want, but when I press copy, it copies the whole lot across, not just the visible colour! Does this make sense, or am I trying to make my life way too complicated?????? My aim is to have a running total on the one sheet, and then I can also check on the individual movements of account transactions and see the balance, if that makes sense?? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Manipulating certain rows only
Thanks Gord.....I have had a look at that website and it looks way too
complicated for me :-( How else would you suggest I define each account? I have only changed the text colour as that was the easiest way at the time, but don't know how else I can do it..... Filters don't seem to work though, because I added an add-in and was able to filter between the colours beautifully.....which is PERFECT.....but then am back to square one when I try and copy the data across.....it copies the entire range, not just the filtered section......so if there was a solution to only copying/pasting the filtered section......then my problem would be solved..... "Gord Dibben" wrote: From your response I take it there is no other method of defining each account. Unfortunate. You will have to use VBA to enter the index number of of each colored row. Then you could filter on that column with the numbers. See Chip Pearson's site for functions to return the index number of a color. http://www.cpearson.com/excel/colors.htm You would place his Cellcolorindex function into a mofule in your workbook. Then in a helper column enter =Cellcolorindex(cellref) Filter on that column. Gord On Sun, 17 Feb 2008 19:33:01 -0800, Shaz wrote: Good thought, but that isn't going to work, unless you are able to filter according to colours! "Gord Dibben" wrote: Other than the colored rows there must be another method of defining the 4 accounts. Have you tried DataFilterAutofilter on account names or account numbers? You can then copy the visible rows to another sheet. Gord Dibben MS Excel MVP On Sun, 17 Feb 2008 15:52:00 -0800, Shaz wrote: I am trying to set up a running total of expenses etc, where my first sheet is a combined running totals, from 4 different accounts, so the rows are colour coded accordingly. I then have monthly sheets, where I want to break down of the running totals according to the account. PROBLEM - Is there anyway of copying only certain rows, such as, all the blue text rows, or the red text rows? I have tried to select the relevant rows, by pressing control and the relevant coloured text for each bank account, but when it comes to copying, it will not allow my to copy multiple rows. Copying each individual row is WAY too tedious, there must be a quicker way! I even tried hiding the colours I didn't want, but when I press copy, it copies the whole lot across, not just the visible colour! Does this make sense, or am I trying to make my life way too complicated?????? My aim is to have a running total on the one sheet, and then I can also check on the individual movements of account transactions and see the balance, if that makes sense?? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Manipulating certain rows only
Well....I don't know WHAT I was going wrong last time....but suddenly when I
filter and copy and paste, it is now only pasting the contents I have filtered.....have no idea why it wasn't working earlier......strange! "Gord Dibben" wrote: From your response I take it there is no other method of defining each account. Unfortunate. You will have to use VBA to enter the index number of of each colored row. Then you could filter on that column with the numbers. See Chip Pearson's site for functions to return the index number of a color. http://www.cpearson.com/excel/colors.htm You would place his Cellcolorindex function into a mofule in your workbook. Then in a helper column enter =Cellcolorindex(cellref) Filter on that column. Gord On Sun, 17 Feb 2008 19:33:01 -0800, Shaz wrote: Good thought, but that isn't going to work, unless you are able to filter according to colours! "Gord Dibben" wrote: Other than the colored rows there must be another method of defining the 4 accounts. Have you tried DataFilterAutofilter on account names or account numbers? You can then copy the visible rows to another sheet. Gord Dibben MS Excel MVP On Sun, 17 Feb 2008 15:52:00 -0800, Shaz wrote: I am trying to set up a running total of expenses etc, where my first sheet is a combined running totals, from 4 different accounts, so the rows are colour coded accordingly. I then have monthly sheets, where I want to break down of the running totals according to the account. PROBLEM - Is there anyway of copying only certain rows, such as, all the blue text rows, or the red text rows? I have tried to select the relevant rows, by pressing control and the relevant coloured text for each bank account, but when it comes to copying, it will not allow my to copy multiple rows. Copying each individual row is WAY too tedious, there must be a quicker way! I even tried hiding the colours I didn't want, but when I press copy, it copies the whole lot across, not just the visible colour! Does this make sense, or am I trying to make my life way too complicated?????? My aim is to have a running total on the one sheet, and then I can also check on the individual movements of account transactions and see the balance, if that makes sense?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manipulating Arrays | Excel Worksheet Functions | |||
Manipulating dates | Excel Discussion (Misc queries) | |||
manipulating dates | Excel Worksheet Functions | |||
manipulating columns | Charts and Charting in Excel | |||
Manipulating shapes | Excel Discussion (Misc queries) |