Concatenate and place in specified length:

Bill,

Greetings. I have a very tricky issue. We are trying to set up an excel based journal entry upload file into our ERP system. We intend to use our customer & project code as the last 10 digits of the description field on the journal entry. However, those 10 digits have to begin at exactly 28th letter of the description. (That way we can run reports using the description field) The journal entry description therefore has to be a maximum of 26 characters / letters.

The concatenate function is what would work. Lets say, on Column A the description is entered and in Column B the 10 digit customer project code. We would like to concatenate Column A and B and use that for description for the Journal entry. The issue we are running into is that the description on column A could be from 2 to 50 letters. How do I cut off the description to a maximum of 26 and ensure that the customer / project code always begins on the 28th letter of the concatenated description? (even if column A has only 10 letters) How can we accomplish this in excel? Would really appreciate your thoughts and help with this.

Shrikant Sortur

Just as you can select to repeat rows at the top of a page as you print it, is there a way to repeat rows at the bottom of a page as you print it?

These rows would be in addition to the information you put in your footer.

thanks,

David

Using the "Find" feature in Excel is nice to locate items quickly, but is there a way to use this or another function that will expand the search to include text in any comments within the worksheet?

thanks,

David

How do you get the data fields in a PivotTable to format the same as the source data? If my source data is formatted as Accounting with two decimal places and $, I want the same field in the PivotTable formated the same way. Why do I have to manually format the field again in the PivotTable when I already did it in the source data?

Sir,
I am developing a compliance tracking model in Excel 2007 with Macros. This model has the list of reports, report due date, responsible person's email and report status (Pending or Submitted).If I open the file at anytime and refresh it and the model should check all the status and send an automatic email (outlook) to responsible person for all the status reading as "Pending" with a copy to the CFO.
What is the best and easy step of modelling this requirement?

In pivot table, how do I "suppress" or eliminate those cells where across all columns+rows in that particular line+colum the summation is zero. I don't want to see that line/column to appear in the pivot table for presentation purpose as it is not revelant to reduce size.

I am looking for a way to reference a row number for data located in one spreadsheet based on a "reference" number in a cell located in a "summary" spreadsheet. In my summary spreadsheet I have several cells that reference data from an another spreadsheet in the same workbook. The data table is updated weekly by date and I need to reference a new row number each week. I would like to insert a row number in a cell in the "Summary" spreadsheet and use that cell to reference the row number in the formula that references the data in the "Data" spreadsheet.

Can a single worksheet have the trust center enabled so it doesn't always block content. But not have it changed globally?

Earlier Excel versions you could allow a worksheet to accept links, etc. Now it appears to be all or nothing with out being asked all the time.

Thanks,

I use pivot tables for data analysis; great tool but I have a problem when my data range includes blank rows. When I select a field for values, the default is to "count" versus "sum". Easy enough to change but it would be great to not have to repeat this step for each column of the pivot table. Any suggestions???

FORMATTING REPORT BETWEEN OPERATING SYSTEMS:
I often do work on my personal computer which is a MAC using Excel 2008. I will then send the file to someone in my office using Excel on a Windows PC. The problem I am having is how the report formats. The main area is in text footnotes on the report that I originally had wrapping with in a wide cell. The footnote wrapped to 2 lines in the cell, but when it was opened on a PC it needed 3 lines and I did not have the row high enough so it now cutoff the last part of the footnote. How do I avoid this problem while I still work on a MAC in my home office?

Is there a way to change the default Zoom magnification under the View tab from 100% to 75%?

Thanks,
Tom

In an Excel sheet, where you have a column with repeating text values (e.g. state codes), a common problem appears when you want to assign each value a description or values that re-arrange your column in a new look fashion (for some special purposes for example).
This problem is, of course, resolved when you can read the assigned new values from other sources, by using VLOOKUP function for example. But if you are required to enter values manually, the only option I have found is to use IF functions, nesting one into another, to get the issue resolved.
In ACCESS we have SWITCH function available in SQL Query to tackle such a burden; is there a similar solution in hand to be used in EXCEL too?

Many thanks in advance.

Our accounting software combines columns when it exports to Excel. Is there any shortcut to take this combined data separated by a space in one column and split them into their three separate columns again?

How do I list file names along with path of my all excell files on the PC in a worksheet that automaticly open when I boot my PC ?

This is a follow up question for referencing a cell to create or change a header or footer. How do you format the header or footer using the macro? I've got one long title now, how can I get it to drop into additional lines?

thanks again.

Wayne

how do i link a cell to the header so I only need to change my cell reference once to change multiple worksheet headers?

Thank you!

I am utilizing Pivot Tables to analyze some large amounts of data on purchases. Each row of my pivot table is the ID # of a product so I can clearly see what products are purchased the most frequently and for the most dollars. I also have 2 columns - "Max of Unit Price" and "Min of Unit Price." I would like to add a third column "Price Range" that is simply the difference of the two columns already in the Pivot Table. It appears the calculated field option will not work - every row ends up with a value of $0.

I have searched the internet to find a solution. Unfortunately, while I have found others with the same question I have yet to find a resolution. Any help would be greatly appreciated!

I created VBA Useform for data entry into a table in a worksheet. My intention is for users to be able to fill sales information in the table. However I realise that anytime I input a new data, the new data is always replacing the earlier data. In other words, all the input data always drops in one row. How do I make sure the new input data move to a new row (a new line.

I have created a workbook for multiple users which I various groupings in, but I have also protected each of the sheets, and as a result can not utilize the groupings.

Using Excel 2007 we would like to present two tables of information on the same worksheet one set of data in rows above the other. The catch is trying to have different column widths, or merge selections between the top table and the bottom table.

I am often trying to compare similar information in different spreadsheets using vLookup, however, I consistently run accross instances when I would like to have a multi-condition vLookup. I have read that Excel has a formula writer. How do you use the formula writer to create a multi-conditional vLookup (similar to a sumifs)? Thanks.

When prepping productivity analyses, I work with two databases that display employee names differently - one shows First Name Last Name (ie. John Smith) and the other shows Last Name, First Name, credential (ie. Smith, John RPT). It would be helpful if I could get one database to recognize the Last Name in the other database. Is there a faster way to do this than using text-to-columns on the name fields for both databases? Thank you!

I think it's pretty simple, but I am just not sure what the "trick" is to make it do what I need.

I have a column which has each day of the month in it (e.g. 1, 2, 3, 4, etc...).

All I want Excel to do is when I open this spreadsheet go to the cell that has today's day of the month in it.

For example, if today is Oct 1, 2010, I want Excel to go to the cell that has "1" in it for the 1st of the month.

Does that make sense?

This spreadsheet lists what is due on each day of the month by day (e.g. 1st).

thanks,


David

Every month I download an excel file from our financial reporting system that contains the P & Ls of various cost centers (over 100 tabs, one for each of the cost center). I have to generate an excel report summarizing the Revenue, Operating Income and EBITDA for each of the cost center. Currently, I manually select data and do a copy paste to a new sheet. Is there an easier way to summarize data from various tabs into a new sheet? (Each of the tabs are uniform in terms fo the columns and rows that it contains) [Our financial reporting engine cannot do this for us, and that is why we export to excel and do this manually].

I am trying to format a cell based on data from another cell in the same workbook. I've tried to use conditional formatting but I can't get it to work properly. Here is an example, Cell A2 is $1100.00 and cell C2 is blank. When cell C2 is populated with a Y (means yes, money has been swept)I would like cell A2's font to change color. Is this possible? Thank you, Ed

When I add a field to the values section of a pivot table, Excel will append "Sum of" of "Count of" to the beginning of the field name and format the numbers using a general layout. As a result, I have to go in and manually strip out the characters "Sum of" or "Count of" and reformat every field added to the value section. Is there some way to turn off the "Sum of" and to establish a standard formatting for all numbers added to the values field?

I would like to find a way to embed the rank number next to the data in a pivot table without having to have the data sorted. I have multiple pieces of data (in separate columns) associated with a company, each having a potentially different rank for that company. I want the rank numbers to change for each associated column as the pivot table changes by including/excluding/adding rows (companies). The company data is sorted in alphabetical order.

In using the NPV( ) function, the calculations always assume that beginning of the series is in the uppermost cell in a column or the left most cell in a row.

For example suppose I had the years 2011 to 2020 in Cells B 1 to K 1 and the income corresponding to each year in cells B2 to K2. If I use the NPV function [i.e. =NPV(7%,b2:k2)]to compute the present value it will provided the correct answer.

Suppose that the data is set up such that years 2020 to 2011 are in cells B1 to K1 and the corresponding income values are in B2 through K2. Because of the order in which the cells are set up I want the present value to go from K2 to B2. I again use the NPV function but I set it up as follows:
=NPV(7%,K2:B2). Note how I reversed the order in the NPV function because the order was changed in the spreadsheet. If I use this second function Excel will still do the calculation using B2 as the first year and will yield the same answer as it did in the first calculation.

The same is true if the series were set up in a vertical column.

How can I change the direction of the computation?

Thanks

Tips on how to heat map a spreadsheet so that values can be highlighted with different colors from best to worst.

Currently, we import MTD sales data into Access, then copy and paste into Excel 2003. I'd like to skip Access and import directly into Excel. I tried using Data > Import External Data > New Database Query > and it sort of works. After selecting the database and entering my password, I select Transaction_Date, Store_No, and Net_Sales. Then, I choose dates greater than or equal to 2/1/2011. Then, the spreadsheet has a row of sales data for each store-day. Is there a way to group all the dates together so that the import shows only sales data for each store? I can do this grouping in Access but I don't know how to do this in Excel.

I have a standard work sheet used to calculate draft invoice values for assignments done by the firm. the sheet contains a row for each client and once I enter the basic fee and disbursements the sheet has formulas that calculate taxes and gross invoice value. I then have create a draft invoice where i copuy and paste each of these values into a vertical format with the cleint name on top. i.e vertically with typically an excel sheet for each client. Is there any way in which i can get this done quicker without having to do it one client at a time.

I have a spreadsheet with rows of data to be imported. Certain records fail validation testing and generate an error log that can be exported to an excel file. I would like to use conditional formatting in the original data to highlight those records that have generated errors so that they can be easily found and corrected. For example, a period balance for a general ledger account does not import into the budget software because the gl account number doesn't exist in the budget database. I want to highlight this record in the original import file by matching it against the gl account number in the error log. Is there a formula that can be used in conditional formatting to accomplish this?

Seems to me, back in Excel 2000 (maybe 2003), there was an icon to email a single spreadsheet out of a workbook.

I am using Excel 2007 now and can't find it anywhere.

Besides copying the sheet to a new file, is there another way?

Thank you

I have two similar databases of customer data in separate spreadsheets. Each record consists of a row with the same fields in each worksheet. The first worksheet is an older file, while the second file is newer and contains the records in the first file, plus hundreds more. I have identified records in the first worksheet, which must be deleted in the second worksheet. What's the quickest and easiest way to do this without going through one by one to delete the records in the second sheet? (I don't want to delete the records in the first worksheet).

I have a worksheet where I track year to date sales invoice detail. I add new data to the spreadsheet each month. I then run various pivot tables against the data which include filters. The first time I created the pivot table, the drop down filter selections sorted in ascending order. As I have added records to the worksheet, the data from the new records do not sort in ascending order in the drop down filter. Instead, they are added to the bottom of the filter list. Is there a way to have all data in the drop down sorted as I add information to the worksheet? Thanks.

Is there a way to have a high and a low number disregarded (or thrown out) when averaging a set of numbers using a formula?

Is there a way to have a field in a pivot table without having a function performed on that field? For example, if my data for my pivot table already contains the fields for Revenue Gross Profit$ and Gross Profit %, how can I include the Gross Profit % field without having the pivot table sum up, count, etc. the existing GP% included in my data? Do I have to create a formula within the pivot table itself or is there some other way?

Bill,

To better explain, (Conditional Format a Chart topic), I had already selected solid fill yet only 1 color selection becomes available (not two as in your tip answer).

I tried deselecting and selecting again, yet still only 1 color option becomes available.

Is there a setting I need to change (using Excel 2007)?

John

Bill,

Thank you for you excellent help on "Conditional Format a Chart" that I asked assistance on earlier. However when I follow the steps and arrive at the Format Data Series dialog box the "Vary colors by point" DOES NOT appear. I can not select anything. I am using Excel 2007 and went to the help, asked the same and same response to select "Vary colors...." box, yet not there??

Is there a setting some place I have to change, initiate, click, etc.???

Sincerely,

John

I've got multiple worksheets that I have headers where each month I need to change the date. Is there a way to make this easier to automatically look to one cell and update the date on all headers accordingly?

Thank you,

Wayne M. Rowley
Excel User

I frequently develop Excel templates with a number of sheets. However I protect the sheets, one by one. I want to find out if there is any fast method to protect multiple sheets at the same time.
Ebrima, The Gambia

Whenever I do pivot tables I have a preferred way of showing the data. For example on the value field settings I almost always want to see "sum" rather than "count of total". Likewise, the defaults on the pivot options are not my preferred options. Is there a way I can change these defaults so that my preferences automatically are used rather than having to change the settings each time.

Is there any way to Conditional Format a Chart Range? For example I have a series of numbers in a Balance Sheet Model (Cash Line) that are currently negative, thus when I chart I make the bars red. If I put in Additional Paid in Capital certain months on the Cash line may turn positive and thus I would want those bars representing the positive cash months to be green -

I have a spreadsheet I use when dispersing payroll for employees to initial when they received their check. When I get new employees or someone terminates I have to redo the columns so they remain alphabetical (I have 2 columns of names and spaces for them to initial). Is there a way to add or remove just 1 or 2 cells in each of the 2 rows (name and space to initial) without it removing or adding in both employee name lines?

Should Excel be considered as a tool for reporting from major ERP systems like SAP & Oracle. What other components are needed to make Excel secure, auditable and efficient if used like this?

What's the most efficient method for consolidating data from multiple tabs of a worksheet that all share the identical format?

Thank you for your consideration.

If you have a list of employee names in one column showing last name, first name (Smith, John), how do you convert the list to show first name last name (John Smith)?

Is there a way to use conditional formatting to format cells which cells are inputs and which cells are formulas? Also, is there a way to use conditional formatting to highlight where a cell contains a combination of formulas and inputs?
e.g., =sum(a1:a30)+1

In the good old days, I used to be able to send financial statement reports from my proprietary accounting software to excel and then combine them into one workbook with multiple worksheets. In Excel 2010, I now get an error message regarding the size of the host spreadsheet being smaller. Is there a way around this? I can move the smaller to the larger but this seems an unnecessary hassle.

Is there any way in Excel 2007 to have a pivot table always default to sum the amounts rather than count them. Also can the number format in a pivot table be changed from the standard Excel format. Nothing I've tried works.

Many thanks
Eric

I've tried using the custom feature in formatting cells but I can't seem to get it to work.

How do I get punctuation out of SS #'s?

Submitting csv. files to the state with employee ss#'s they want it with out the punctuation, my accounting software includes it. How can I easily get it removed? I've been adding columns and doing the concantenate formula but that leaves out the zeros when they are in the wrong place.

Any good ideas to help would be appreciated.

Thank you.

I know how to toggle between workbooks and open windows using the alt-tab and ctrl-tab keys. My question is whether there is a way to reorder your open windows so you don't have to toggle through so many to get to the one you want. Thanks.

How do you add up advanced filtered rows with out all the stuff in the middle being added to the total as well?

I could really use this option but I always get the whole not just the filtered items in the total.

Thank you,

How do you display numerical values in thousands while retaining the numerical values? For presentation purposes you may want to display the dollars in thousands (or in millions). Thanks.

Suppose, I have data in this format

Target Achievement %Achieved
30538 16423 54%
40245 19015 47%
41110 21080 51%
12039 9263 77%
7530 3454 46%
14507 9585 66%

I want to draw a column chart showing Target vs. Achievement and then want to add line which shows % achievement. How it can be done using Excel 2007 ?

An often overlooked function within Excel is the 'Indirect' formula, which significantly expands the power of VLOOKUP and SUMIF formulas, among other things -- I think a columnn on this would be quite useful.

How can I determine if a cell is being referenced in another worksheet? When I use the Trace Dependents or Trace Precedents buttons on the Auditing toolbar, it shows an arrow pointing toward a worksheet icon. Where I'm stuck is that I can not determine which worksheet or cell is being referenced.

How can you toggle between worksheet tabs that are in the same Excel file?

How can I pull data from multiple data spreadsheets into one Pivot Table

I use a workbook (created years ago by someone who is long gone) that includes push buttons that navigate to different sheets in the workbook. How do I move these buttons around on a sheet or remove them all together?

I need to code or map the current year's Excel trial balance for tax purposes to come out with a sort of tax return Profit and Loss (P&L) / Income Statement.

Last year's trial balance was coded for tax purposes. I plan on using vlookup formulas to match the current year's account numbers to the prior year's account numbers and have the prior year's tax codes posted to the current year's trial balance spreadsheet. After the tax codes are posted I turn the vlookup formulas into values by using copy then paste value. Any new accounts I code manually.

After this I plan to use =sumif and / or =dsum formulas to accumulate tax amounts for the tax "P&L" workpaper. Will the above work and is there a better way of getting the figures?

I found a nice link that talks about how to protect a sheet. http://www.techonthenet.com/excel/cells/protect2007.php

It shows how to protect a single sheet by going through and Unlocking, Protecting Formulas, Locking and Adding a p/w to the sheet. If I have 15 sheets and I want to use the same p/w for each tab, how can I select all tabs and do the process once instead of 15 times?

Suggestion:

How to use Conditional Formatting in Data Tables (sensitivity analysis) to automatically color the current input and output values in the model, and the max and min output values.

I would love to see an article about how to export excel to word and powerpoint effectively for Board Presentations and other external communications to eliminate recreating the data in word/powerpoint.

Often I am presented with a download of information in a PDF or Adobe format. This "data dump" needs to be converted into either excel or access so as to be analyzed more easily. Can you provide best practices to accomplish this?

Thanks!