| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using row number or column letters in formula
Now I'm trying to use a row number in a formula so I don't have to type the
same formula individually on each row 12 times! My formula is currently this =SUM(INDIRECT("'"&'Control Sheet'!$F$57&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$58&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$59&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$60&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$61&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$62&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$63&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$64&"'!G7")) The formula pulls the value of one cell in each worksheet and totals them. I normal just drag the formula across and down and it will change G7 to H7 across, G7 to G8 down and so on, but now that I've added the INDIRECT function dragging doesn't work. I am thinking that I could change the location of the cell G7 to G(row number) where row number is the same as the row I am entering the formula in. If I can do this with row number, is it possible with column letter and what if column letter would not always be the same as the column I am in? Thanks as always Chris |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
using row number or column letters in formula
Hi,
If you wish to sum the value in the same cell of the some worksheets, get all the worksheets together and then use =sum(first:last!G7) First is the tab of the first worksheet from where you want to pick up the data Last is the tab of the last worksheet from where you want to pick up the data -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Chris" wrote in message ... Now I'm trying to use a row number in a formula so I don't have to type the same formula individually on each row 12 times! My formula is currently this =SUM(INDIRECT("'"&'Control Sheet'!$F$57&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$58&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$59&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$60&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$61&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$62&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$63&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$64&"'!G7")) The formula pulls the value of one cell in each worksheet and totals them. I normal just drag the formula across and down and it will change G7 to H7 across, G7 to G8 down and so on, but now that I've added the INDIRECT function dragging doesn't work. I am thinking that I could change the location of the cell G7 to G(row number) where row number is the same as the row I am entering the formula in. If I can do this with row number, is it possible with column letter and what if column letter would not always be the same as the column I am in? Thanks as always Chris |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
using row number or column letters in formula
Oh, that looks interesting, thanks. I've not heard of that before and could
place those sheets next to each other so I'm going to try it. Regards Chris "Ashish Mathur" wrote in message ... Hi, If you wish to sum the value in the same cell of the some worksheets, get all the worksheets together and then use =sum(first:last!G7) First is the tab of the first worksheet from where you want to pick up the data Last is the tab of the last worksheet from where you want to pick up the data -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Chris" wrote in message ... Now I'm trying to use a row number in a formula so I don't have to type the same formula individually on each row 12 times! My formula is currently this =SUM(INDIRECT("'"&'Control Sheet'!$F$57&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$58&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$59&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$60&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$61&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$62&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$63&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$64&"'!G7")) The formula pulls the value of one cell in each worksheet and totals them. I normal just drag the formula across and down and it will change G7 to H7 across, G7 to G8 down and so on, but now that I've added the INDIRECT function dragging doesn't work. I am thinking that I could change the location of the cell G7 to G(row number) where row number is the same as the row I am entering the formula in. If I can do this with row number, is it possible with column letter and what if column letter would not always be the same as the column I am in? Thanks as always Chris |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
using row number or column letters in formula
Would you tell me the format please? I've tried this
=SUM('Sheet 1'!:'Sheet 6'!G7) and I've tried =SUM('Sheet 1'!G7:'Sheet 6'!G7) the first one doesn't work at all and the second returns the #VALUE! error "Ashish Mathur" wrote in message ... Hi, If you wish to sum the value in the same cell of the some worksheets, get all the worksheets together and then use =sum(first:last!G7) First is the tab of the first worksheet from where you want to pick up the data Last is the tab of the last worksheet from where you want to pick up the data -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Chris" wrote in message ... Now I'm trying to use a row number in a formula so I don't have to type the same formula individually on each row 12 times! My formula is currently this =SUM(INDIRECT("'"&'Control Sheet'!$F$57&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$58&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$59&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$60&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$61&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$62&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$63&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$64&"'!G7")) The formula pulls the value of one cell in each worksheet and totals them. I normal just drag the formula across and down and it will change G7 to H7 across, G7 to G8 down and so on, but now that I've added the INDIRECT function dragging doesn't work. I am thinking that I could change the location of the cell G7 to G(row number) where row number is the same as the row I am entering the formula in. If I can do this with row number, is it possible with column letter and what if column letter would not always be the same as the column I am in? Thanks as always Chris |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
using row number or column letters in formula
I'd try:
=sum('sheet 1:sheet 6'!g7) Actually, I'd add a couple of sheets to the outside of this grouping and call them Start and End and use: =sum('start:end'!g7) Then I could add (or subtract) sheets from this "sandwich" to play what-if games--or even add new sheets and not have to adjust the formula. If you get a #value! error with these, then look at the data in G7 of each of the sheets. I'd expect an error in at least one of the cells. Chris wrote: Would you tell me the format please? I've tried this =SUM('Sheet 1'!:'Sheet 6'!G7) and I've tried =SUM('Sheet 1'!G7:'Sheet 6'!G7) the first one doesn't work at all and the second returns the #VALUE! error "Ashish Mathur" wrote in message ... Hi, If you wish to sum the value in the same cell of the some worksheets, get all the worksheets together and then use =sum(first:last!G7) First is the tab of the first worksheet from where you want to pick up the data Last is the tab of the last worksheet from where you want to pick up the data -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Chris" wrote in message ... Now I'm trying to use a row number in a formula so I don't have to type the same formula individually on each row 12 times! My formula is currently this =SUM(INDIRECT("'"&'Control Sheet'!$F$57&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$58&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$59&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$60&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$61&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$62&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$63&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$64&"'!G7")) The formula pulls the value of one cell in each worksheet and totals them. I normal just drag the formula across and down and it will change G7 to H7 across, G7 to G8 down and so on, but now that I've added the INDIRECT function dragging doesn't work. I am thinking that I could change the location of the cell G7 to G(row number) where row number is the same as the row I am entering the formula in. If I can do this with row number, is it possible with column letter and what if column letter would not always be the same as the column I am in? Thanks as always Chris -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
using row number or column letters in formula
OMG - it works! Thank you loads, you probably saved me 2 hours work but
better than that, you've taught me something that i can use all over the place. Also, your tip about xtra sheets is impressive if only because it's so simple. Thanks again Chris "Dave Peterson" wrote in message ... I'd try: =sum('sheet 1:sheet 6'!g7) Actually, I'd add a couple of sheets to the outside of this grouping and call them Start and End and use: =sum('start:end'!g7) Then I could add (or subtract) sheets from this "sandwich" to play what-if games--or even add new sheets and not have to adjust the formula. If you get a #value! error with these, then look at the data in G7 of each of the sheets. I'd expect an error in at least one of the cells. Chris wrote: Would you tell me the format please? I've tried this =SUM('Sheet 1'!:'Sheet 6'!G7) and I've tried =SUM('Sheet 1'!G7:'Sheet 6'!G7) the first one doesn't work at all and the second returns the #VALUE! error "Ashish Mathur" wrote in message ... Hi, If you wish to sum the value in the same cell of the some worksheets, get all the worksheets together and then use =sum(first:last!G7) First is the tab of the first worksheet from where you want to pick up the data Last is the tab of the last worksheet from where you want to pick up the data -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Chris" wrote in message ... Now I'm trying to use a row number in a formula so I don't have to type the same formula individually on each row 12 times! My formula is currently this =SUM(INDIRECT("'"&'Control Sheet'!$F$57&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$58&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$59&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$60&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$61&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$62&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$63&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$64&"'!G7")) The formula pulls the value of one cell in each worksheet and totals them. I normal just drag the formula across and down and it will change G7 to H7 across, G7 to G8 down and so on, but now that I've added the INDIRECT function dragging doesn't work. I am thinking that I could change the location of the cell G7 to G(row number) where row number is the same as the row I am entering the formula in. If I can do this with row number, is it possible with column letter and what if column letter would not always be the same as the column I am in? Thanks as always Chris -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel, how do I change the column headings from letters to number | Excel Discussion (Misc queries) | |||
| How can I change column designation from number to letters? | Excel Discussion (Misc queries) | |||
| how can I sort a column that consists of sizes (letters not number | Charts and Charting in Excel | |||
| how i write a number on a column A and see that number on letters. | Excel Worksheet Functions | |||
| How do I enter a formula in a cell so that letters= a number i.e.. | New Users to Excel | |||
Hybrid Mode
