| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Text columns to pull into subtotal lines
I have an extensive spreadsheet that has both text and number columns. I
need some of the text columns to "sub-total" on the line I do sub-totals on. EX: GPS GPS Jim Offshore 100.00 GPS GPS Jim Offshore 300.00 Jim Total 400.00 When I do the subtotal by name I would also like the "GPS", "GPS" and "Offshore" wording to pull into the subtotal line. How can I do this? -- Denise |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Text columns to pull into subtotal lines
Hi,
Are you using the Data, Subtotal command? If so there is no way to do this unless your resort to VBA or manually entering a formula. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Denise" wrote: I have an extensive spreadsheet that has both text and number columns. I need some of the text columns to "sub-total" on the line I do sub-totals on. EX: GPS GPS Jim Offshore 100.00 GPS GPS Jim Offshore 300.00 Jim Total 400.00 When I do the subtotal by name I would also like the "GPS", "GPS" and "Offshore" wording to pull into the subtotal line. How can I do this? -- Denise |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Text columns to pull into subtotal lines
Hi,
Continuing with the previous, suppose your data looks like this and extends from A1 to E100: Other Thing Name Location Value GPS GPS Jim Offshore 100 GPS GPS Jim Offshore 300 Total GPS-Offshore 400 Where you want to display the GPS and Offshore in cell C4. 1. Select column C down as far as the data extends 2. Press F5, Special, Blanks, OK 3. Type the following formula and then press Ctrl+Enter =IF(E4<"",A3&"-"&D3,"") -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Denise" wrote: I have an extensive spreadsheet that has both text and number columns. I need some of the text columns to "sub-total" on the line I do sub-totals on. EX: GPS GPS Jim Offshore 100.00 GPS GPS Jim Offshore 300.00 Jim Total 400.00 When I do the subtotal by name I would also like the "GPS", "GPS" and "Offshore" wording to pull into the subtotal line. How can I do this? -- Denise |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Text columns to pull into subtotal lines
Hi,
You can try something like this. Highlight the range and then hit F5 Special Blanks. This will highlight all blank cells. Now just do = up + arrow key and Control Enter. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Denise" wrote in message ... I have an extensive spreadsheet that has both text and number columns. I need some of the text columns to "sub-total" on the line I do sub-totals on. EX: GPS GPS Jim Offshore 100.00 GPS GPS Jim Offshore 300.00 Jim Total 400.00 When I do the subtotal by name I would also like the "GPS", "GPS" and "Offshore" wording to pull into the subtotal line. How can I do this? -- Denise |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Text columns to pull into subtotal lines
I was doing fine til I got to the part where you state now just do - am I
typing in the = sign, then the word "up", then the plus sign, which arrow key and then cntl enter? -- Denise "Ashish Mathur" wrote: Hi, You can try something like this. Highlight the range and then hit F5 Special Blanks. This will highlight all blank cells. Now just do = up + arrow key and Control Enter. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Denise" wrote in message ... I have an extensive spreadsheet that has both text and number columns. I need some of the text columns to "sub-total" on the line I do sub-totals on. EX: GPS GPS Jim Offshore 100.00 GPS GPS Jim Offshore 300.00 Jim Total 400.00 When I do the subtotal by name I would also like the "GPS", "GPS" and "Offshore" wording to pull into the subtotal line. How can I do this? -- Denise |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Text columns to pull into subtotal lines
I just got it to work after playing with it. This is going to be a time
saver- thank you so much. -- Denise "Ashish Mathur" wrote: Hi, You can try something like this. Highlight the range and then hit F5 Special Blanks. This will highlight all blank cells. Now just do = up + arrow key and Control Enter. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Denise" wrote in message ... I have an extensive spreadsheet that has both text and number columns. I need some of the text columns to "sub-total" on the line I do sub-totals on. EX: GPS GPS Jim Offshore 100.00 GPS GPS Jim Offshore 300.00 Jim Total 400.00 When I do the subtotal by name I would also like the "GPS", "GPS" and "Offshore" wording to pull into the subtotal line. How can I do this? -- Denise |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Text columns to pull into subtotal lines
I appreciate your response. I followed the response by Ashish which is
similiar to yours and it worked. -- Denise "Shane Devenshire" wrote: Hi, Continuing with the previous, suppose your data looks like this and extends from A1 to E100: Other Thing Name Location Value GPS GPS Jim Offshore 100 GPS GPS Jim Offshore 300 Total GPS-Offshore 400 Where you want to display the GPS and Offshore in cell C4. 1. Select column C down as far as the data extends 2. Press F5, Special, Blanks, OK 3. Type the following formula and then press Ctrl+Enter =IF(E4<"",A3&"-"&D3,"") -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Denise" wrote: I have an extensive spreadsheet that has both text and number columns. I need some of the text columns to "sub-total" on the line I do sub-totals on. EX: GPS GPS Jim Offshore 100.00 GPS GPS Jim Offshore 300.00 Jim Total 400.00 When I do the subtotal by name I would also like the "GPS", "GPS" and "Offshore" wording to pull into the subtotal line. How can I do this? -- Denise |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Text columns to pull into subtotal lines
You are welcome.
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Denise" wrote in message ... I just got it to work after playing with it. This is going to be a time saver- thank you so much. -- Denise "Ashish Mathur" wrote: Hi, You can try something like this. Highlight the range and then hit F5 Special Blanks. This will highlight all blank cells. Now just do = up + arrow key and Control Enter. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Denise" wrote in message ... I have an extensive spreadsheet that has both text and number columns. I need some of the text columns to "sub-total" on the line I do sub-totals on. EX: GPS GPS Jim Offshore 100.00 GPS GPS Jim Offshore 300.00 Jim Total 400.00 When I do the subtotal by name I would also like the "GPS", "GPS" and "Offshore" wording to pull into the subtotal line. How can I do this? -- Denise |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Add new subtotal lines | Excel Discussion (Misc queries) | |||
| how do I remove the automatic break lines in text to columns? | Excel Discussion (Misc queries) | |||
| pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
| Bolding the subtotal lines automaticlly When using the Subtotal fu | New Users to Excel | |||
| "how to combine multiple lines into a pull down option?" | Excel Worksheet Functions | |||
Linear Mode
