How to loop in excel without VBA or macros?

Posted by Jeff on Stack Overflow See other posts from Stack Overflow or by Jeff
Published on 2011-02-08T23:02:43Z Indexed on 2011/02/08 23:25 UTC
Read the original article Hit count: 216

Filed under:

excel

|

excel-formula

Is there a better way for me to write this formula? Did some googling on "excel loops," but no luck. I don't have VBA or macros installed (it's not an option to install them, unfortunately).

=IF('testsheet'!$C$1 <= 99,'testsheet'!$A$1,"") &
IF('testsheet'!$C$2 <= 99, 'testsheet'!$A$2,"") &
IF('testsheet'!$C$3 <= 99, 'testsheet'!$A$3,"") &
... and so on through !$C$40, !$A$40 ...

As it is, I'll have to repeat the above code 40 times in each cell and I have over 200 cells which need the code. sniff

I'm pretty good with PHP/SQL, but just learning Excel.

© Stack Overflow or respective owner

Related posts about excel

Excel error "This workbook contains Excel 4.0 macros or Excel 5.0 modules"

as seen on Super User - Search for 'Super User'
I have a workbook that was protected via the Protect Workbook feature. It was sent to someone else to modify. When they sent it back, it was unprotected and when I try to reprotect it I get this error, "This workbook contains Excel 4.0 macros or Excel 5.0 modules. If you would like to… >>> More
Open excel 2007 excel files and save as 97-2003 formats in VBA

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a weird situation where I have a set of excel files, all having the extension .xls., in a directory where I can open all of them just fine in Excel 2007. The odd thing is that I cannot open them in Excel 2003, on the same machine, without opening the file first in 2007 and going and saving… >>> More
Creating Excel or Excel compatible Spreadsheets on the server side in C#

as seen on Stack Overflow - Search for 'Stack Overflow'
I'd like to make server-side excel compatible spreadsheets that maybe use OpenXML or a structured data format. I've used Office Interop before to generate Excel spreadsheets, but those apps run on a PC that has office installed. For this web project I'm building, the server doesn't have office installed… >>> More
vba access values from excel workbook from another excel workbook

as seen on Stack Overflow - Search for 'Stack Overflow'
Any ideas why this code will not work? Workbooks.Open Filename:="C:\a.xls" Workbooks("a.xls").Activate Worksheets("b").Select Dim OutputArray(10, 10) OutputArray(1, 1) = Workbooks("c.xls").Worksheets("worksheet_otherthan_default").Select.Range(A1).Value I'm trying to run a macro in one file (unnamed… >>> More
Excel workbook event order and usage when closing Excel

as seen on Stack Overflow - Search for 'Stack Overflow'
Given the following workbook events: BeforeClose BeforeSave Please tell me: - The firing order in the case of multiple workbooks alreay opened (wb1, wb2 and wb3 are opened in this order) and the user closes Excel. You can assume all 3 needs saving. - What happen if user cancels one of the saving… >>> More

Related posts about excel-formula

excel formula problem

as seen on Stack Overflow - Search for 'Stack Overflow'
Kindly check the below written formula, if the formula is not correct then please provide me the correct one that is related to this. IF(A1:AV7000=F1,vlookup(f2,B1:C7000,2,0),0) As I have tried above written formulas, but it's not working. >>> More
excel formula to subtract number of days from a date

as seen on Stack Overflow - Search for 'Stack Overflow'
is there a way in Excel to have a formula that does something like this: = 12/20/2010 - 180 which would take a certain date (12/20/2010 in this case) and subtract 180 days . . >>> More
How to make an excel formula which totals several agecent rows based on cell values

as seen on Super User - Search for 'Super User'
I have an excel sheet with three columns: date, person and percentage. I would like to put in a data validation that flags cells if the total for a given data/person combination do not equal 100%. Is that possible? In other words, in the custom formula of a data validation, I would like to make the… >>> More
Excel formula for variable fields

as seen on Stack Overflow - Search for 'Stack Overflow'
I am looking for a simple formula to do the calculation on two fields that are variable, for e.g., c1 has 100 and c3 has 150 and I want to calculate an increase/decrease percentage, but the trick is the cell values change every month. How do I put the formula to cater for such variation. Appreciate… >>> More
Excel formula to show linked cell ID

as seen on Stack Overflow - Search for 'Stack Overflow'
In an excel cell, I've placed a simple formula =C4 The cell typically displays the value of cell C4, but instead I want to see the linked cell ID instead, which in this case is "C4". Is there a formula to show me this? like: =SHOWCELL(C4) >>> More