Question on Formulas and Dragging

khchang74

New Member
Joined
Mar 29, 2009
Messages
8
I have a question on formulas and dragging formulas to automatically populate cells with the same formula.

I'm using Excel 2003 on a Windows XP system.

I have a formula in one worksheet(tab) that references a cell in another worksheet. I would like to drag the formula down in the first worksheet so that it automatically populates the cells below or next to it with the same formula. Traditionally, Excel will just increase all the references cells by one cell. So, for instance if your formula is "=100+A1", when you drag it down the formula will become "=100+A2". What happens if you don't want it to always increase by "1"? What if you want it to skip cells or rows, so that your formulas become "=100+A1", "=100+A4", "100+A7"? In this example, the formulas increase by 3 each time.

And an even more advanced question is, what if you wan the formulas to swich sheets? For example, "=100+'Shee1"!A1", "=100+'Sheet2'!A2", and so on?

Is there a way to do this?

Thanks!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
You gave examples, what is your real situation? There is a solution to both your questions but would need to know the specific columns, rows, and sheet names.
 
Upvote 0
Ok, here is my real situation:

I have a number of sheets in my workbook, the sheets are named accordingly:

'001' through '135': these sheets are all identical in format/size/positioning/cells/etc. The only things that are different are the contents of the cells.

'Summary Sheet': this is the very last sheet in the entire workbook and summarizes items in all the other sheets.

I've entered formulas into Row5 of 'Summary Sheet' and the formulas are as follows:

F5: ='003'!F30, G5: ='003'!H30, H5: ='003'!K30 ....

and so on. I will only have about 10 or 15 columns or so running across, so these formulas were all hand entered. As you can see, the formula is not sequential, I have to skip a few columns each time. But since there are only between 10 to 15 columns, I've hand entered it and am fine with that.

The trouble is, the subsequent rows (6, 7, 8...) each reference a different, sequential sheet. So row's 6, 7, 8, etc., will have formulas like this:

F6: ='004'!F30, G6: ='004'!H30, H6: ='004'!K30 ....
F7: ='005'!F30, G7: ='005'!H30, H7: ='005'!K30 ....
F8: ='006'!F30, G8: ='006'!H30, H8: ='006'!K30 ...

...and so on.

Please note that because each of the 1-135 previous sheets are all set up the same, the cell being referenced never changes from sheet to sheet, only the sheet itself changes.

My question is, if I start at Row5 in the 'Summary Sheet' and drag down to do an autofill on the formula, It will simply increase the row number from the same targe sheet. I want to know, who can I do an autofill but have the sheet name ('003', '004', '005', etc.) increase sequentially?


Thanks!
 
Upvote 0
Take a look at the INDIRECT() function. It allows some pretty flexible concats of text a values.

For example, in my sample, I have five Sheets with the default names:
Sheet1
Sheet2
Sheet3
etc

In cell A1 of each succeeding sheet, I put an increment of 10
Sheet1, A1: 10
Sheet2, A1: 20
Sheet3, A1: 30
and so on.

In sheet1, cell D1, I entered the following formula:
=INDIRECT("Sheet"&ROW()&"!A1")

I copied that down 5 rows and got all five values, one from each sheet.
 
Upvote 0
I use the indirect all the time. I have found it is best to add a row at the top.lets say row 1 and column to left,say column A. The Column the row houses the first text for the formula and column houses the row number. These column and row can be easily hidden. This allows you some additional flexibility as well. First, it makes building and copying the indirect very simple. Second, Column A shows the row number and makes it easy to see what row the data is pulling from. Also if use the ROW() formula in column A and link it to one of the spreadsheets if anyone inserted a row across all the sheets in the workbook then your Row() will auto update and thus your indirect. In Row 1 and maybe 2,,3, etc you can build the first part of the indirect. for example row one could house "sheet003!", Row 2 could house the column and row 3 could combine them. I usually break them out this for a couple of reasons. First it makes it simpler for anyone new to indirect to understand how the formula is built and second I usually end up using a vlookup or match against a table to choose either the sheet name and/or the column as I prefer to have that data organized in a table on a seperate tab. So as not to keep rambling here forever lets just say Indirect+consitent data set on tabs = endless reporting flexabilty.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top