Increment sheet reference formula in new tabs?

DataAnalyst

New Member
Joined
Mar 23, 2012
Messages
44
Hi everyone,

I'm in a crunch which I have to manually create about 40 one-page reports from a table of values. The reports are for the people I support, and I plan to make each report on a separate tab in the excel worksheet with the table of values.

The names of the report recipients are across the top with the value labels down the side, this is how the final report will look. The first report is for Name1, second Name2, etc.

So the report will have formulas to bring in the data values in each row like this:

Label Formula
Metric1 =Datasheet!FirstRowName1
Metric2 =Datasheet!2ndRowName1
etc.

My original idea was to make the reports and manually copy the values for each person. I like formulas better in case we use this again in the future, but I still have to manually insert them in each report.

Is there a simple way to increment these formulas as I create each new tab?

So then when I copy the tab with the code above it would be like this:

Metric1 = Datasheet!FirstRowName2
Metric2 = Datasheet!2ndRowName2

Is there a quick and simple way to do this? I've looked at the Offset and Indirect functions, but I've never used them before and don't have time to learn them now since this is a crunch. Our outside programmers were supposed to do this and didn't, and the reports have to be ready for management to review by Monday afternoon.

Thanks for any help you can provide! ;)
 
Last edited by a moderator:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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