Microsoft Office excel 2007

Hi, I have to make 300 rows of formulas for 9 columns, each with a formula that has a specific number it goes

100

101

102

103but I need to do this 300 times on 9 different rows and it is very time consuming to do this, so I was wondering if there was a easier way to automatically number the formulas instead of having to manually type it.

sorry for the slightly confusing post, so here's a picture to help explain my situation

In F197 you have ='190'!$K$7 which will return the value from K7 on a sheet named 190. Another way to return the same value in F197 is to use this:

=INDIRECT("'" & ROW() - 7 & "'!$K$7")

It may be hard to see but "'" is a double-quote apostrophe double-quote and "'! is a double-quote apostrophe exclamation point.

What this will do is build the text string '190'!$K$7) which the INDIRECT function will use to return the value.

As you drag this down, the ROW() function will increment but everything else will stay the same.

Granted, this formula will not

looklike what you asked for, but it will return the same values and meets your criteria of not "having to manually type it".The only other way that I can think of is to use VBA to write the formula in the cells. A fairly simple macro can do that for you but we'd need to know the exact starting and ending cells and specifically what the formulas should look like.

Hi, One way to create a series of formulas that need to increment, but Excel won't increment automatically is to use a couple of columns:

Column A contains the number series.

For example A1 contains the number 190

put 191 in A2 and select both A1 and A2 and drag them down.

A3 will contain 192 and so onIn cell B1 enter: ="ZZZ'"&A1&"'!$K$7"

Drag this down alongside the numbers in column ANow select column B

Do a Copy and Paste Special ... valuesColumn B will appear unchanged but will contain:

B1: "ZZZ'190'!$K$7"

B2: "ZZZ'191'!$K$7"Now select column B

Use Find /Replace

and enter Find "ZZZ"

and Replace "="

(Don't use the quotes.)Column B will now contain:

B1: ='190'!$K$7

B2: ='191'!$K$7Hope this helps.

Regards

Ahhh Thank you both for your excellent replies! I was able to get it to work with Humars method. Sorry for the late reply, I've been really busy and I haven't had to do these logs until today. Thanks again!

You're welcome. Regards

Humar

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History