Excel formula numbering

Microsoft Office excel 2007
August 13, 2010 at 15:47:48
Specs: Windows XP
Hi, I have to make 300 rows of formulas for 9 columns, each with a formula that has a specific number

it goes


but 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


See More: Excel formula numbering

Report •

August 13, 2010 at 16:43:19
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 look like 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.

Report •

August 13, 2010 at 19:30:58

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 on

In cell B1 enter: ="ZZZ'"&A1&"'!$K$7"
Drag this down alongside the numbers in column A

Now select column B
Do a Copy and Paste Special ... values

Column 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$7

Hope this helps.


Report •

September 3, 2010 at 13:23:22
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!

Report •

Related Solutions

September 3, 2010 at 14:14:58
You're welcome.



Report •

Ask Question