This task joins a string together based on a number of characters per cell in the range.
I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
Got a formula that goes like this: =IF((L1=1),"Product 1",IF((L2=2),"Product 2",IF((L2=3),"Product 3",IF((L2=4),"Product 4".... and so on for a further 100 or so products. Obviously, this formula is too long for one cell.
I need a macro where I can compress the lines in the report below. By other words will I end up with totals on lines with the same text information. This report can have different number of lines, so it is an advantage if the macro is dynamic.
i'm using the following code to compress a worksheet, but i get an error message that i would like to surpress...
'zip On Error Resume Next Dim FileNameZip, FolderName Dim strDate As String, DefPath As String Dim oApp As Object DefPath = "c: empsalesmanMDBed" If Right(DefPath, 1) <> "" Then DefPath = DefPath & "" End If FolderName = "c: empsalesmanMDBed" '<< Change..................
I need to write a formula but excel shows an error message "Formula Too Long." There are two long strings of the formula that I repeat several times. Is there any way to put these strings in other cells and reference them within the formula? Each of the strings looks very similar to this:
I want to do is copy a formula from one workbook to another workbook that I have open - have tried just copy and paste (only get the result, no formula is copied) and have tried paste special, but there doesn't seem to be anything relevant on the list. I'm sure there is a simple way of doing it.
I have a formula which is currently too long and which I'm sure could be done more efficiently. That plus Excel is saying the formula is too long. I have pasted the formula below and broken it up for clarity:
I'm looking for is ComplyXL and that costs $300 to buy. So, as a poor grad student, I'm looking for an alternative solution - either a freeware program or a hassle-free VBA code.
So here's what I need the code or program to do: When I ask excel to open a pre-existing file, I'd like it to automatically rename (& compress?) the last used version of the file & save that to a filename-specific archive somewhere (hidden?), before opening the file as requested. Alternatively, I suppose that a mirror copy of each 'just used' file could be created & saved to an archive folder whenever the used file is saved & closed.
--Note: to avoid making copies of EVERY workbook I ever use, I suppose there should also be a button in the toolbar or a file-associated setting that lets me identify which files I want this task to apply to...
Basically I'm looking to have excel automatically save versions of certain key files (like Word's 'Versioning' feature) without having to remember to 'Save As', without the versions taking up much space, and without having to store the older file versions in the same place as the most recent one.
And yes, I know that a similar problem has already been posted & solved here, but they didn't include a way to choose which files used versioning nor (if it's possible) a way to compress the archived versions to save space (maybe by adding it into a WinRAR archive somewhere?).
I am trying to make a spreadsheet for a customer and would like to include a feature where the list of parts can be expanded or compressed with buttons.
Each SO# has a set of PO#'s associated with it, and it would be nice if the viewer can choose to see the PO#'s or not see them at will (this will make a lot more sense if you see the spreadsheet example in my link.)
Ideally, there would be a button next to each "SO#" which would expand/compress accordingly. I don't want just a universal button that would expand/compress all of the SO# at once (although having this option on the side would be nice).
I have the data as below. And to submit this to the third party is needs to be compressed (a total for each Full time ANNUAL salary).
RULES
All total monthly earnings must start from 0 when a "Full time ANNUAL Salary" is added or endedIf the Annual amount misses a month I need to assume the salary has ended
I hope this makes sense.
Below is test data for one person. And the result i am hoping to get with some comments.
Employee Full time ANNUAL salary Monthly earnings Pay date from Pay Date to
[Code]....
The result of the above would be:
Employee Full time ANNUAL salary Monthly earnings Pay date from Pay Date to Comments
[Code]...
15k started 01/06/2013 so the total for the 10k starts again, alongside total for 15k
Chris £15,000.00 £450.00 01/06/2013 31/08/2013
[Code]...
15k started 01/12/2013 so the total for the 10k starts again, alongside total for 15k
Chris £15,000.00 £150.00 01/12/2013 31/12/2013
[Code]...
10k ended, 20k started 01/01/2014 so total for 15k starts again, alongside total for 20k
This is the formula: =IFERROR(VLOOKUP(H3;'[VATCompanies.xlsx]1'!$A:$B;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$D:$E;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$G:$H;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$J:$K;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$M:$N;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$Q:$R;2;0);I7))))))
Is there a work around to pull data from cells in excel to another tab with standard language? For example the template language will stay the same except in example A's case the last sentence "0.0" hrs will vary based what's sitting in B34.
It's stating the formula is too long. Is there too much text?
Sometimes the link would be in the middle of the text as shown in example B below with dates 00/00/00.
EXAMPLE A For any leave of absence that is granted for more than 10 days, time off is governed under the Disability Bank Policy. Under the Disability Bank Policy there is a 10 day elimination period which must be satisfied prior to gaining access to your disability bank. During this 10-day elimination period, you are required to use your sick time for the first 6 days (48.00 hours) and vacation time for the remaining 4 days (32.00 hours). After this elimination period, we use all the time accrued in your disability bank. Currently, you have a disability bank balance of "00" hours.
EXAMPLE B After this disability bank is exhausted, you will use all remaining and future paid time off balances from your vacation/CME/holiday banks. Because your leave begins in 00/00/00 and ends in 00/00/00 we will advance you future paid time off balance thru 00/00/00. Once those balances are exhausted the remainder of the leave will be unpaid. Per our policy, should you not return to work, you agree to pay back any time advanced to you within 30 days of your separation from the company.
I'm receiving this message when I attempt to use the Find, Replace function. Formula is too long". I have a column of cells containing text only. ( about 2-3 paragraphs worth) I'm trying to replace a name with another name, which works fine where the cell contains a single or few sentences, but fails to replace when the cell contains too much information.
Here is something I have noticed with using Compress Images in 2010.
When I run Compress Images from the save as dialog box in 2007, the file size is slightly larger than if I run it in 2010. However, if I then send the resulting excel file out to people, if they are viewing it on an iPad, then they cannot see the images at all from the 2010 version but can see them fine if the excel file is from the 2007 version (with exactly the same data and images in there).
I cannot figure out why this may be. The options on both are set as "Email (96ppi)" and with "Delete Cropped areas of pictures" ticked.
Have two tables of data the first is shown below (the first line is headers)
DogCatReptileMonkey SmallBigNosyFluffy SmellyNosy
The second is (again first line is headings)
SmallSmellyBigNosyFluffy AAGFF BGHGE CTUBQ
Need to create code which reads the first table, finds the categories of the first table in the second table and puts these with the original headings into a new table so in this example the end table would look like -
DogCatReptileMonkey AGFF BHGE CUBQ AF GG TB
My original has about 150 headings in table A and B hence why I have given an example
I manage to use formula find for 1 text in 1 statement, but in confuse how to get another text found if i have "8 text" to find. Generally, there will only be "1 text" for each statement.
Maybe in 1 statement there have only "IC" or "Staff Tagging" or Name" or "Product" or etc till 8.
I am working on using an excel workbook as part of a roll playing game and I am trying to record a macro and then put that in a command button which uses the randbetween, multiple if statements and multiple vlookup functions. I have successfully done similar things by just recording a macro and then pasting that into the VB editor. However this time I get a unable to record macro after putting the formula in the selected cell. I have tried copy and paste and just typing the formula with the same results.
Upon executing the formula I then want it to copy and paste special- values the result.
I'm assuming I have to put the VB code in manually but when I tried to do the formula I got a syntax error.
The worksheet I am working on is named new and I've included the formula in the a text box. I would like the result to show in J12 and be triggered by pressing the cmnbutton in k12.
I am using the below formula to distinctly count the number of customers that match the criteria that I have in Cells C7 and B10. The data is in a separate worksheet, that I am showing Named Detail of which will be changing on a monthly basis, so a pivot table does not want to be used. The detail data ranges from row 7-40,000, and the file is currently 8610KB's, and can potentially grow.
This formula works but takes an excessive amount of time for one caluclation, and I need this for multiple column and row critera. So, can this calculation be changed in order to get the same result with faster calculation time? I am using Excel 2003.
I have a formula I've been using for a long time which uses VLOOKUP to find results based on various reference cells, and then adds them up. To avoid errors caused by VLOOKUP not finding anything for one of the references I have also used ISERROR. The formula returns a blank if the calculation returns a zero.
I now need to bring further references in to the calculation but, using the format I have been, the formula is now too long. Here is my extensive formula:
I need a formula that will search the range D8-D100 to confirm that all cells within that range are either 11 characters in length or blank. I will use it inside of an error message that will look something like this:
=IF(****formula that checks to make sure all of the cells in that range are blank or 11 characters****=TRUE, "", "Please make sure that all cells are 11 digits or blank")
I have a large spreadsheet, within which i am trying to remove commas from all cells. I get the error 'formula is too long' when I carry out the search. Some of the cells are >1024 characters in length and contain dates, text etc.
I have worked on this really difficult long formula, but the problem is, I have been working from my Mac. And my office, which this formula was made for, is all PC. Now generally this isn't a problem, but apparently PC's have a limit on how long the formula can be. Is there anyway to overcome this? I really need this formula to work..
