hi,

when i  export  data from database to excel using DLL(for Excel2003)

there is no problem in creating excel file and we are able to successfully view the exported file.

recently we upgreaded our office 2003 to 2007 and after that when ever we try to open the exported file(2003) in 2007 it is throwing the POPUP as:

“Excel found un readable content and if you want to repair you may loose some content in the work book.”

when we press “YES” all the data is getting deleted and only numeric data is left.(all other format data is getting deleted)

can you repair the corrupted file or can you provide the reason ehy it is throwing error.

thanks in advance

Hi guys,

Really desperate for your help here. Can’t get my head around why this formula does not work. Im using excel 2002 and trying to used If with such condition:

=IF(VLOOKUP(A1;[Input.xls]Sheet1!$A$1:$B$5;2;0)=”OK”;”OK”;VLOOKUP(A1;[Input.xls]Sheet2!$A$1:$B$5;2;0))

It only retrieves the 1st vlookup (fist 5 entries), but does not retrieves the result of 2nd vlookup (if false). It gives the following results:

I’ve got two files, one output file and one input file with two sheets where the vlookups need to pick the data from.

Your help would be really appreciated.

/Andy.

I have someone who wants to calculate the total hours someone has worked in a week from an on-call rota.  The problem is that one cell is used for each day with the start and end time eg 8 – 6, 8.30 – 4.30 etc.  At the moment my only thought is that the whole structure of the spreadsheet would need to be changed – unless you have any ideas…

I would like to wish you all a peaceful and happy Christmas.

Thank you for using the site. Without your help this website would not exist.

Have a very merry Christmas and great new year.

Thank you.

Andrew

I am trying to write a question and answer spreadsheet that will give me a random list of questions 50 out of a hundred and be able to print of the student paper and answer paper seperate.

I had this many years ago for a dive club laptop and got stolen the person who wrote it has long gone.

Any one who can help I would be most greatful

 Where the x intersects, i wanted to get the row and column header and build up a list.

How to name a sheets in excel using macros.

I mean copying cell value from one sheet and pasting that value to tab(sheet) in other excel sheet using macros.

I want to set one worksheet cell range reference to another worksheet using aspose.cells.For example i have two worksheet(wrk1 and wr2). I have some data on wrk1 and wrk2 is balnk.Now on wrk2 i want cells refrence of wrk1(like want cell reference of wrk1 sheet A1:A10 on wrk2 with same style as on wrk1) .

Please suggest.

I have a spreadsheet containing 184 cells that I want to change on a weekly basis, when using the Replace function I lose the formatting of the text (all ends up same FontSize|Colour).

I want to keep the original formatting of this data.

Some of the text I need to keep a certain colour and size, with other data within the same cell a differing colour and size.

Is this possible, and if so how?

Thanks Tony

I am doing a budget planner where the user selects from a dropdown list the frequency of outgoing.  So say a gas needs to be paid every 2 months the user will select this option and the in the cell next to it, key in the amount.  How can I gather all this data and get say a weekly amount required based on all weekly, fortnight, monthly, yearly values

Hi Everyone, I am making a spreadsheet and need help with summing time. I have daily values that change, monthly totals(which need to reset at the end of the month) and yearly totals. I need the monthly to update every 24hours, depending on the daily value, and save the value. As well as updating the yearly total. Again dependent on the daily value. Any help would be greatly appreaciated.

Hello,

I hope you can help me with the following problem I am encountering.

I have around 2000 rows with a set of 8 different numbers in each row. For instance,

2-4-7-11-15-29-35-36
4-16-20-29-32-37-49-50 etc

Is there any formula that can tell me if any 2 numbers in each line add up to a 3rd number?

For instance, in

4-7-11-12-40-42-56-83, 4 and 7 add up to 11

So I would like to get the a “Yes” or “X” when this happens.

If the above is not possible, I don’t mind any other solution that would give me an indication that the above event exists in a line.

Just to be honest, I have posted a question to this problem in another forum but I have not gotten a reply.

Thank you in advance

Sans

I have a row of numbers. I can use the MIN formula to tell me which is the minimum number but how do I get the formula to tell me which column the minimum number is in?

I have set up a sheet and am having trouble. I have two drop down boxs which i set up using Data_Validation. When choosing from these i would like a 3rd figure to appear in the next row(from a table else where). eg When choosing a+b i get c. Any ideas

Just recently I was asked if it was possible to sum a range of cells by their cell colour, rather than counting the number of cells with a matching colour.  Well it is.   To sum a range of cells based upon their background colour requires a custom function to be written and the a small formula to be written on the worksheet.

I was asked was it possible to sum a range of cells based on two different cell colours.  Red and Yellow.  Red representing a negative figure and yellow a positive number.  To start with you need to add a custom function to a new module in Excel’s Visual Basic editor.  Once you have created a new module copy the below function and paste it in to the module.

Function SumIfByColour(InputRange As Range, ColorRange As Range) As Double

Dim clr As Range
Dim ColourSum As Long
Dim ColorIndex As Integer

PositiveColour = 6 'Yellow Background
NegativeColour = 3 'Red Background

ColourSum = 0
On Error Resume Next ' ignore cells without values

For Each clr In InputRange.Cells
If clr.Interior.ColorIndex = PositiveColour Or clr.Interior.ColorIndex = NegativeColour Then
ColourSum = ColourSum + clr.Value
End If
Next clr

On Error GoTo 0

Set clr = Nothing
SumIfByColour = ColourSum

End Function

Once you have done this go back to your worksheet.  Now let’s assume you have a list of numbers, positive and negative in column A ranging from A1 to A20.  At the bottom of your list, or in cell B1 type the following formula:

=SumIfByColour(A1:A20,A1:A20)

Once typed hit enter and you will have your total!  :-)

You may be asking, what if I have different colour backgrounds, or only one colour?

To sum different colours than those in this example you simply need to get the ColorIndex (which is a number) for the colours you are using.  Getting the colour index is fairly easy and there is a guide here.  Or you could simply look up the ColorIndex value using this chart I have prepared earlier!

I only have one colour I hear you say….  Well just modify the code slightly as shown below.

NegativeColour = 3 'Red Background – REMOVE THIS LINE

CHANGE THIS LINE
If clr.Interior.ColorIndex = PositiveColour Or clr.Interior.ColorIndex = NegativeColour Then
TO
If clr.Interior.ColorIndex = PositiveColour Then

Hey presto!

I hope you found this post useful!?