I NEED A FLEXIBLE FORMULA

[QC Coug]

I have a workbook that has 20 or so different worksheets, one per department.
Each worksheet contains the department budgets. I need to consolidate the
department budgets onto one worksheet so I can upload it into our ledger.
For example lets say there are 5 accounts listed on each worksheet. When I
consolidate the data onto one worksheet I would then have 100 rows (5
accounts/worksheet x 20 departments) of data.

Is there a formula that will allow me to change one cell, which would have a
worksheet name in it, and it will then reference that departments data?

Is there a function that will facilitate this process?

 

[Miguel Zapico]

Check the help of the INDIRECT() formula, it may be what you are looking for.

 

[Niek Otten]

Look in HELP for the INDIRECT() function.

But maybe you should consider redesigning the structure of your workbook and have just one sheet, with the department as an
identifier. 20 Sheets with identical layouts look like a maintenance nightmare to me...

--
Kind regards,

Niek Otten

|I have a workbook that has 20 or so different worksheets, one per department.
| Each worksheet contains the department budgets. I need to consolidate the
| department budgets onto one worksheet so I can upload it into our ledger.
| For example lets say there are 5 accounts listed on each worksheet. When I
| consolidate the data onto one worksheet I would then have 100 rows (5
| accounts/worksheet x 20 departments) of data.
|
| Is there a formula that will allow me to change one cell, which would have a
| worksheet name in it, and it will then reference that departments data?
|
| Is there a function that will facilitate this process?

 

[QC Coug]

I agree it is a nightmare. I have adopted this and need to get through the
next couple of months until I can redeign our budget tool. I will be doing
exactly what you mentioned.

Thanks for the tip.

 

[QC Coug]

I can't figure out how to make this work for what I am trying to do. In the
example I'm playing with I have 3 worksheets, Sheet1, Sheet2, and Sheet3.
Each of these sheets has a different value in cell A1. On a 4th worksheet I
am inputing the name of one of the worksheets (Sheet1, Sheet2, or Sheet3) and
then when I want to be able to create a formula that will pull the data out
of cell A1 from the worksheet I specify.

 

[Cutter]

Try this:

=INDIRECT(B5&"!A1")

The B5 is the cell containing the sheet name you specify

So if you type Sheet2 in B5 you will get the value from Sheet2!A

 

[Melissa]

Hi,

Can you please elaborate more on what you suggested below. How do you
design one sheet with the department as an identifier? Is there an example I
can see? Thanks.

 

[Cutter]

To make it simpler for your users (or yourself) use this:

=INDIRECT("Sheet"&B5&"!A1")

if you haven't given the sheets custom names

With this formula you just need to type in the sheet number in cell B5