Excel Formulas
Solved/Closed
BEAR833
-
Oct 14, 2008 at 05:32 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 23, 2010 at 08:33 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 23, 2010 at 08:33 AM
Related:
- Excel Formulas
- Date formula in excel dd/mm/yyyy - Guide
- Number to words in excel formula - Guide
- Little alchemy formulas - Guide
- Excel grade formula - Guide
- Excel free download - Download - Spreadsheets
15 responses
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
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".
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 !.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Aug 17, 2010 at 10:22 PM
Aug 17, 2010 at 10:22 PM
Like this murphy
='sheet1'!B12 + B22
='sheet1'!B12 + B22
Didn't find the answer you are looking for?
Ask a question
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
165
Apr 30, 2009 at 06:16 AM
Apr 30, 2009 at 06:16 AM
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
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
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
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Aug 23, 2010 at 08:33 AM
Aug 23, 2010 at 08:33 AM
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
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
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
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.
when we change in format cell 2001-04-15, by default its not coming in formula bar how it is can u help me.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 21, 2010 at 08:56 AM
May 21, 2010 at 08:56 AM
Bhanu, in formula bar it will not show the formatted text. Only cell shows the formatted text
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
Dec 20, 2008 at 06:08 AM
thanks for providing a exact solution of the query. it is really help me out in this problem. however i have one more query related to this problem that when run this macro the list of worksheet will printed from first cell of the worksheet. Col is easily changed from(i,1) to (i,3) however the row position is not changed, i try many different combination like using nested loob but cant get the required solution
actually i want to list my sheetname in sheet9(6,3)
i want to print the worksheet from where i needed not from the first cell of current sheet
your efforts would be highly appreciated in this matter
thnks
BR,
KHUBS
Feb 18, 2009 at 04:06 AM
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
Feb 20, 2009 at 10:44 PM
Apr 8, 2009 at 09:13 AM
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.
Apr 29, 2009 at 11:46 AM