Search results

Hi Kamarlon, Yes I do and I beleieve that this is an excellent approach to take. An Access database is the best place to store and enrich large sets of data. An Excel spreadsheet is one of the most flexible and efficient tools for presenting data. Combining the two gives you the best of both...

Hi, Another option would be to use Excel's built in Track Changes function. http://office.microsoft.com/en-us/excel-help/track-changes-in-a-shared-workbook-HP010197381.aspx (NB Tracking changes makes the workbook shared. This does have some implications for some functionality so may not be...

Hi, Another option would be to use Excel's built in Trach Changes function, see link below: http://office.microsoft.com/en-us/excel-help/track-changes-in-a-shared-workbook-HP010197381.aspx (NB Tracking changes makes the workbook shared. This does have someimplications for some functionality...

Hi, Another option would be to use Excel's built in Trach Changes function, see link below: http://office.microsoft.com/en-us/excel-help/track-changes-in-a-shared-workbook-HP010197381.aspx (NB Tracking changes makes the workbook shared. This does have someimplications for some functionality...

Hi, Another option would be to use Excel's built in Trach Changes function, see link below: http://office.microsoft.com/en-us/excel-help/track-changes-in-a-shared-workbook-HP010197381.aspx (NB Tracking changes makes the workbook shared. This does have someimplications for some functionality...

Hi, Another option is to turn on track changes, see link below: http://office.microsoft.com/en-us/excel-help/track-changes-in-a-shared-workbook-HP010197381.aspx (nb sharing a workbook does restrict some functionality so it may not be suitable for all situations). There are also software...

Chandoo, I didn't know you were such a good singer... http://www.youtube.com/watch?v=B0PoLpxrvr0

You can use SUMPRODUCT() to avoid the helper column: =SUMPRODUCT((A24:A27>=500)*(A24:A27<600),B24:B27)

I would suggest adding in a helper column containing: =if(and(A1>=500,A1<600),"Y","N") Then your SUMIF() would be (assuming helper column is D): =sumif(D24:D27,"Y",A24:A27)

I think any other solution would have to involve VBA (date stamping a helper cell via a cell change event would probably be the best VBA solution)

Hi, One possible solution would be to use Excel's built in track changes function. In Excel 2007 this is on the review tab on the Ribbon. HTH

Another option may be MATCH() =Match(Lookup Value, Lookup array, match type) Eg: =Match(8,range,0)

Hi, It might be worth seeing if the camera tool gives you what you want. Here is a post by chandoo: http://chandoo.org/wp/2008/12/02/excel-camera-tool-help/

To elabourate slightly, if you use a helper column countaining the ISERROR() formula (which returns TRUE if the cell contains an error) you can then use the COUNTIF() formula to count the cells containing TRUE.

Hi, To count based on a single condition in Excel use the COUNTIF()formula. To count based on multiple conditions use COUNTIFS() (Excel 2007 onwards). NB The formula will not count based on the fact that the cells have conditional formatting applied but based on the values within the cells...

Hi bartman, I would use vlookup to compare the two lists. N/A# showing that the part is not in the other sheet. One way to compare the list and the quantities is to use CONCATENATE() and create a column containing the part numbers and the quantities. eg: =CONCATENATE(A6,"-",B6,"-",C6)...

Hi, Please could you explain what you mean by "not working". ie does it come up with the wrong answer or just stay as the formula being displayed etc?

Hi, You could use the camera tool as explained in this post from Chandoo: http://chandoo.org/wp/2008/11/05/select-show-one-chart-from-many/ Hope this helps, Myles

I'm guessing you've got a main data table and then formulae to the right enriching it? Is there any of the functionality that can be done outside the spreadsheet? For example can you put the data and some of the calculations into another spreadsheet or even better in Access? This can then...

You would need to use VBA on a change event to acheive what you need. Alternatively in Excel 2007 you can use the Icon sets to create a simular look.

Great piece of functionality Hui. I beleive to make it work you need to right click the chart and assign the macro to the chart. I have posted a simple example here: http://cid-4702efbc51549018.office.live.com/view.aspx/Chandoo%20Forum/Toggle%20Chart.xlsm

Have a look at this post from Chandoo. The comments section covers duplicates in this way. In summary add a fraction (eg row number/1000) to the value): http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/

My school teachers always told me to read the question. Sorry, just thought you didn't want a macro. Interesting one...

You could use the UDF below (from http://www.vbaexpress.com/kb/getarticle.php?kb_id=62 ) using HasFormula as mentioned by VaraK: Function FTEXT(f As Range) If f.HasFormula Then FTEXT = f.Formula Else: FTEXT = f End If End Function Assuming all of your formulae start with "=" you could then...

In your sumproduct formula if your refer to the unique voucher number you will be able to use the same formula for each row. Have a look at this post from Chandoo: http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/