Excel Formulas [Solved/Closed]

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

I am having difficulty copying fromulas on excell from different sheets to form an analysis page.

I have 52 different sheets and need to copy the formulas to an analysis sheet so that i have the amount of each sheet on one page but for it to remain as the same cell. I have tried dragging the formula down but it stays as

=Sheet1!B12
=Sheet1!B13 and so on. the cell number needs to stay the same but the sheet needs to change in each cell down.

can someone help?

15 replies

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
29
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Hello,

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
> B
I just utilized this solution for my spreadsheet and I got the error as well. I created my sheet list by typing their names in as there were only 10 sheets for me. I fixed the error by making sure that the sheet names in my list had the single quotes around them. The single quote ' in a cell will cause the cell to treat the characters as if the format was text bypassing the current cell formats. This means that I had to put TWO single quote marks before my sheet names and one after. So my sheet name list read ''sheet 1' ''sheet 2' and so on - and then the formula worked like a charm and saved me countless hours!
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
213 > B
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
Hi Tony,

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.
I had the same problem and simply wrote a list of my sheets and than used the indirect command as you proposed. It worked. Thanks a lot!
plz show me excel formulas
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
in exel, what is the formula,when you are copying the data on sheet 1 into sheet2 or sheet 3...in direct way not like highlight then copy,then paste to the shet2...not like that i need formula in direct copying data from sheet 1 to sheet2 or sheet3
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".
Thank you so much for this. I have been trying to find out how to do this for ages. :)
That works great! Thank you!
thanks a lot. exactly what i wanted.
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 !.
What would the formula be to add another cell after the B12. I have tried several ways but nothing works. (sheet name + Cell B12 and Cell B22). I would really appreciate the help. Thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Like this murphy
='sheet1'!B12 + B22
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
213
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

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!!!!!!!
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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
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?

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
i didnt understand wat u r asking ....
this site is not giving so comlete and brief info . dont u think so ??
we have factory i enter on excel daily production but how to adding another sheet
we can see total
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
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.
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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Bhanu, in formula bar it will not show the formatted text. Only cell shows the formatted text
daily work sheet atomistic adding another sheet