Tony68

Hello BEAR833,

I think you will need indirect referencing, a tricky way browsing through your sheets. I can not tell you if there is a more easy way, below is my tip.

First you need a list of your worksheets. For that, I wrote a simple Visual Basic macro (assume you are familiar with "how to run macros"). This will use the current sheet and will list all the sheet names one below the other. The list will appear starting from the top left cell in current sheet. Try it if you like the idea.

Alternatively, you can simply type in the worksheet names in a list.

Sub SheetNames()

For i = 1 To Worksheets.Count

ActiveSheet.Cells(i, 1).Value = Worksheets(i).Name

Next i

End Sub

And the result will be something like this, depending on your actual sheet names:

Sheet1

Sheet2

Sheet3

Sheet4

Sheet5

etc.

Then, you can use referecing excel formula to take the content of the needed cell: e.g.

- Assume your cell to refer to in each sheet is "B12".

- Assume that A1 contains the name of a worksheet.

Enter this formula next to the sheet names above, and you can copy it down simply. "A1" in the formula shall change as you paste downwards.

=INDIRECT(A1&"!B12")

And it will result the content of cell "B12" in sheet named in cell A1

Good luck! Tell me if it worked.

ps. The macro simply overwrites the cell contents in the active sheet. Prevent problems from unintended macro runs, so please run it only if required. Also I suggest to delete the macro from the file which you might pass on to someone.

regards,

Tony

I think you will need indirect referencing, a tricky way browsing through your sheets. I can not tell you if there is a more easy way, below is my tip.

First you need a list of your worksheets. For that, I wrote a simple Visual Basic macro (assume you are familiar with "how to run macros"). This will use the current sheet and will list all the sheet names one below the other. The list will appear starting from the top left cell in current sheet. Try it if you like the idea.

Alternatively, you can simply type in the worksheet names in a list.

Sub SheetNames()

For i = 1 To Worksheets.Count

ActiveSheet.Cells(i, 1).Value = Worksheets(i).Name

Next i

End Sub

And the result will be something like this, depending on your actual sheet names:

Sheet1

Sheet2

Sheet3

Sheet4

Sheet5

etc.

Then, you can use referecing excel formula to take the content of the needed cell: e.g.

- Assume your cell to refer to in each sheet is "B12".

- Assume that A1 contains the name of a worksheet.

Enter this formula next to the sheet names above, and you can copy it down simply. "A1" in the formula shall change as you paste downwards.

=INDIRECT(A1&"!B12")

And it will result the content of cell "B12" in sheet named in cell A1

Good luck! Tell me if it worked.

ps. The macro simply overwrites the cell contents in the active sheet. Prevent problems from unintended macro runs, so please run it only if required. Also I suggest to delete the macro from the file which you might pass on to someone.

regards,

Tony

joshi

please give me the formula of excel worksheet the student who get 35 or miore marks in each subject who pass, and less then 35 fail

dharmaveer

to copy the formula without changing cell referrence.... we have one way.

ex: in cell 'a1' we have a formula as 'c1+d1' and if we copy the cell/formula to a10 then the formula will be copied as 'c10+d10'.

if you dont want like this, then change formula in cell 'a1' as " $c$1+$d$1".

ex: in cell 'a1' we have a formula as 'c1+d1' and if we copy the cell/formula to a10 then the formula will be copied as 'c10+d10'.

if you dont want like this, then change formula in cell 'a1' as " $c$1+$d$1".

Gutty

The INDIRECT function works perfectly, except for one minor detail of the formula. As you stated in your example:

Write this formula in Cell B , C and any one else but not in Column A ..... Column A contains the values

A

1

2

3

4

6

and in Column B write this formula and drag it down

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

you will get the result

=Sheet1!B12

=Sheet2!B12

The only problem with it is that Excel needs the apostrophe (') when referenceing sheet names. so to actually get the value from the different sheets, you will need to add an apostrophe before referencing the sheet and also just before the "!"

The forumula would then be: =INDIRECT("'"&A1&"'!B12") where A1 is the worksheet name that you would like to grab the number from and B12 is the cell on that worksheet that you want to pull. Also note the "'" is actually quotes-apostrophe-quotes and there is also an added apostrophe before the !.

Write this formula in Cell B , C and any one else but not in Column A ..... Column A contains the values

A

1

2

3

4

6

and in Column B write this formula and drag it down

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

you will get the result

=Sheet1!B12

=Sheet2!B12

The only problem with it is that Excel needs the apostrophe (') when referenceing sheet names. so to actually get the value from the different sheets, you will need to add an apostrophe before referencing the sheet and also just before the "!"

The forumula would then be: =INDIRECT("'"&A1&"'!B12") where A1 is the worksheet name that you would like to grab the number from and B12 is the cell on that worksheet that you want to pull. Also note the "'" is actually quotes-apostrophe-quotes and there is also an added apostrophe before the !.

mubashir aziz

- Posts
- 191
- Registration date
- Sunday April 12, 2009
- Status
- Member
- Last seen
- February 16, 2010

Write this formula in Cell B , C and any one else but not in Column A ..... Column A contains the values

A

1

2

3

4

6

and in Column B write this formula and drag it down

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

you will get the result

=Sheet1!B12

=Sheet2!B12

A

1

2

3

4

6

and in Column B write this formula and drag it down

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

you will get the result

=Sheet1!B12

=Sheet2!B12

Nick

This helped me so much. I had an excel worksheet that had 100+ sheets. Using the macro and the hint about the single ' fo quoting a sheet name really helped! You have no idea!!!!!!!

Doubt

01.08.2010

Here if i drag this cell, its changing like this

01.08.2011

01.08.2012

But i need like this,

01.08.2010

02.08.2010

03.08.2010

What should i do for this

Here if i drag this cell, its changing like this

01.08.2011

01.08.2012

But i need like this,

01.08.2010

02.08.2010

03.08.2010

What should i do for this

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

Since this seem to be date. What you should do is add the date as per you local regional setting. Like for US, it would be mm/dd/yyyy or other places it would be dd/mm/yyyy. Then drag the dates. You can format the cells to look lik dd.mm.yyyyy

arif

i have a same kind of data in execle formate and i want to e mail the same kind of data in a fixed formate. what i do?

Dhanish Sharma

Hi,

You need to copy the whole formula then paste it into the spacial value in formulas then your Problem will be sort out.

Regards'

Dhanish

You need to copy the whole formula then paste it into the spacial value in formulas then your Problem will be sort out.

Regards'

Dhanish

babji

we have factory i enter on excel daily production but how to adding another sheet

we can see total

we can see total

seeBlack

PARTIAL ANSWER, MAKE CELL REFERENCE ABSOLUTE

COPY DOWN THEN EDIT EACH SHEET NAME

Reference (Description) Changes to

$A$1 (absolute (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.) column and absolute row) $A$1

A$1 (relative (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) column and absolute row) C$1

$A1 (absolute column and relative row) $A3

A1 (relative column and relative row) C3

COPY DOWN THEN EDIT EACH SHEET NAME

Reference (Description) Changes to

$A$1 (absolute (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.) column and absolute row) $A$1

A$1 (relative (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) column and absolute row) C$1

$A1 (absolute column and relative row) $A3

A1 (relative column and relative row) C3

Architek

I always change the cell name from B1 to something of a particular static description like FAXNUMBER so when I reference it in any of the sheets it points back to that one cell in the workbook.

bhanu

hi,

when we change in format cell 2001-04-15, by default its not coming in formula bar how it is can u help me.

when we change in format cell 2001-04-15, by default its not coming in formula bar how it is can u help me.

I tried the solution provided by you but somehow indirect function does not work. It gives error #Name? when I say give =INDIRECT(A1&"!B12") it say error at "!B12" Please help

B

A

1

2

3

4

6

and in Column B write this formula and drag it down

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

you will get the result

=Sheet1!B12

=Sheet2!B12

I faced same problem of putting all the sheets name in single sheet. I used your VB macro and it worked beautifully! Thanks :-)

Now I need to link these sheet names to respective sheet, so that if I click on the name of the sheet I can directly go to the respective sheet and view the data. How to do that?

Please hlep.

Thanks,

Shefali.