Excel Magic Tricks # 1: Formulas & Cell References

Appreciate if you could let me know, how can I download Workbooks of Excel Magic Tricks # 23 to 89 which was available earlier on the following site:

http://flightline.highline.edu/mgirvin/excelisfun.htm


Thanks and regards
Jitendra

http://www.iil.com/iil_shortdescr.asp?sku=PT248

Massive macro that took 4 minutes in Excel 2003 takes 1hr+ in Excel 2007,

This section takes 10 minutes:
Trying to take the sum of a range of rows (different for each item on the sheet) from columns F to S.

Looking for a way to get these 14 lines into one line.

Note, this is actually within a loop that occurs about 500 cycles, so code needs to be modified to be complete in under a second/cycle.

VB:

Range("f" & x).Formula = WorksheetFunction.Subtotal(9, Range("f" & topdemand, "f" & bottomdemand)) 
Range("g" & x).Formula = WorksheetFunction.Subtotal(9, Range("g" & topdemand, "g" & bottomdemand)) 
Range("h" & x).Formula = WorksheetFunction.Subtotal(9, Range("h" & topdemand, "h" & bottomdemand)) 
Range("i" & x).Formula = WorksheetFunction.Subtotal(9, Range("i" & topdemand, "i" & bottomdemand)) 
Range("j" & x).Formula = WorksheetFunction.Subtotal(9, Range("j" & topdemand, "j" & bottomdemand)) 
Range("k" & x).Formula = WorksheetFunction.Subtotal(9, Range("k" & topdemand, "k" & bottomdemand)) 
Range("l" & x).Formula = WorksheetFunction.Subtotal(9, Range("l" & topdemand, "l" & bottomdemand)) 
Range("m" & x).Formula = WorksheetFunction.Subtotal(9, Range("m" & topdemand, "m" & bottomdemand)) 
Range("n" & x).Formula = WorksheetFunction.Subtotal(9, Range("n" & topdemand, "n" & bottomdemand)) 
Range("o" & x).Formula = WorksheetFunction.Subtotal(9, Range("o" & topdemand, "o" & bottomdemand)) 
Range("p" & x).Formula = WorksheetFunction.Subtotal(9, Range("p" & topdemand, "p" & bottomdemand)) 
Range("q" & x).Formula = WorksheetFunction.Subtotal(9, Range("q" & topdemand, "q" & bottomdemand)) 
Range("r" & x).Formula = WorksheetFunction.Subtotal(9, Range("r" & topdemand, "r" & bottomdemand)) 
Range("s" & x).Formula = WorksheetFunction.Subtotal(9, Range("s" & topdemand, "s" & bottomdemand)) 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

I tried to do that but it will not work in the actual cell reference.
I'm stumped.



humejap Wrote:
> Have you tried the formula
>
> =Concatenate("$J$",H1)
>
>
> --
> humejap
> ------------------------------------------------------------------------
> humejap's Profile:
> http://www.excelforum.com/member.php...fo&userid=5506
> View this thread:
> http://www.excelforum.com/showthread...hreadid=395098


--
jhockstr

Hello All,

I need to use a number that was calculated from a formula in a cell
reference. Here is the situation: I have a large spreadsheet that
changes periodically. I need to count the number of items in that
spreadsheet(i will use COUNTA - already works) and then take that
number and use it as a row number in a cell reference like this:

In cell H1: =COUNTA(A2:A9999)

Then take that number in cell H1 and insert that into say $J$XXXX where
XXXX represents the number in cell H1.

I'm looking to increase the calculation speed of the workbook by only
calculating cells that I need to.

Thanks for the help,
Jason


--
jhockstr

Goto is great for highlighting numerical constants and text. But for formulas I can only highlight the $B$24*(1-C7) kind, what if I'd like to just find numbers only formulas like "23566+45643+6767678"?

I'd love to find an elegant way to do something.

I've got weekly information appended below each other in one sheet, and I'm
pulling data from it in another by using VLOOKUP. Right now I get the
beginning and ending rows for the lookup range, but have to manually input
them into the formula -- is there a way to build the array automatically for
the formula? I tried building and assembling text to represent the wanted
rows, but got errors. The trick is that the number of rows is not constant
from one week to the next.

Here's what I'm using now (manually setting rows in the third calc based on
values returned from the first two calcs):

beginning row=+VLOOKUP(W$36,'0740'!$A4:$D750,4,FALSE)
(look for the start date of the week, say 372 in this case)

ending row=+VLOOKUP(W$36+7,'0740'!$A4:$D750,4,FALSE)
(look for the start date of the next week, say 396 in this case)

the value lookup=+VLOOKUP($A40,'0740'!$A$372:$D$396,3,0)
(look for the value within the row range)

Any help to not have to manually update rows for future weeks will be
appreciated!

Hi, I tried to use the search fucntion but it kept giving me memory errors... So I oppologise if this has been answered..

I have a spreedsheet with neumorous formuli over 4 sheets, all of which use cell ranges off each other and a data sheet.

A large amount of data is pasted into the data sheet and most formuli get there cell ranges from here.

The problem Im having is that some times the formuli loose their cell references and I have to manually recreate the formula's.

EG: the formula should be SUMPRODUCT(--('friday data'!$P$2:'friday data'!$P$65000))

However it changes to SUMPRODUCT(--('friday data'!#REF!:'friday data'!#REF!))

I have tried locking the cells with the formuli and protecting the worksheet, however it stills happens. another note would be to add that this does not happen all the time...

could it be something to do with the data being pasted into the 'friday data' sheet?

Hi - I have a problem that I thought would be solved by using absolute references, but it's not. I have a spreadsheet that looks like this:
__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 Brooks Alberts =IF(NOT(A3=B3),"different","")
4 Brooks Brooks =IF(NOT(A4=B4),"different","")
As you would expect, C3 displays "different", all other cells in column C are blank. So far so good. However, what I want to do, now that I've identified the "different" row, is to move A3 down one row, so things look like this:
__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 ______ Alberts =IF(NOT(A3=B3),"different","")
4 Brooks Brooks =IF(NOT(A4=B4),"different","")
5 Brooks etc...........
Please note that I'm doing this because there is other information in each row, not because my OCD is out of control. Also, in my real formula I'm handling cells that are blank. Anyway, my problem is that I don't get the above. Rather, Excel adjust the values in my formula so I get:
__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 ______ Alberts =IF(NOT(A4=B3),"different","")
4 Brooks Brooks =IF(NOT(A5=B4),"different","")
5 Brooks etc...........
This is NOT what I want. I want the formula in C3 to continue to reference A3, not A4. As I understand it, absolute references are only for when you want to copy the cell that they are used in, which I'm not doing. Anyway, I tried saying $A$3, but it didn't help.
How do I stop this behavior?
Thanks!

I have a formula that references data on another spreadsheet with multiple worksheets. I have a new worksheet for each month (Jan, Feb, Mar, ...).

How can I copy formulas in series that keep the cell references the same, but change to the appropriate month. So for the forumula below, I want to fill a series down in a column to represent each month.

Right now I have to go in and edit it to change the month to the next month (i.e. change Jan to Feb.)

An example of the formula is below. In this case, I would want to fill the series down a column and have the months update in series but keep the cell reference the same.


=SUM('[New 2008 sales report linked.xls]Jan'!$M$19,'[New 2008 sales report linked.xls]Jan'!$N$19,'[New 2008 sales report linked.xls]Jan'!$Q$19,'[New 2008 sales report linked.xls]Jan'!$L$47,'[New 2008 sales report linked.xls]Jan'!$K$47,'[New 2008 sales report linked.xls]Jan'!$S$47)