Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substituting cell content into a formula
I have a workbook that has worksheets for each month, containing a table of
rows of customer order info for the month. All tables are formated the same. I have a report page that nicely performs calculations and formats a report for a sinlge row of data from a single worksheet for a one customer. How do I set up my formulas so that I can substitute a different month (worksheet name) and row number in the formulas to get the save report for a different customer and/or month? More simply put, I have a hard coded formula =Jan!A4 in my report that I'd like to change the worksheet and row specification to say =Feb!A22 based on the contents of a cell containing the text "Feb" and another cell containing "22"? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substituting cell content into a formula
"John" wrote in message
... More simply put, I have a hard coded formula =Jan!A4 in my report that I'd like to change the worksheet and row specification to say =Feb!A22 based on the contents of a cell containing the text "Feb" and another cell containing "22"? Hi John, If "Feb" is in cell A1 and "22" is in cell A2, the following formula will do what you're looking for: =INDIRECT(A1 & "!A" & A2) -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substituting cell content into a formula
=indirect("'" & a1 & "'!" & b1)
if a1 and b1 are the two cells. John wrote: I have a workbook that has worksheets for each month, containing a table of rows of customer order info for the month. All tables are formated the same. I have a report page that nicely performs calculations and formats a report for a sinlge row of data from a single worksheet for a one customer. How do I set up my formulas so that I can substitute a different month (worksheet name) and row number in the formulas to get the save report for a different customer and/or month? More simply put, I have a hard coded formula =Jan!A4 in my report that I'd like to change the worksheet and row specification to say =Feb!A22 based on the contents of a cell containing the text "Feb" and another cell containing "22"? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substituting cell content into a formula
Oops. I had "A22" in B1.
Don't use my formula! Dave Peterson wrote: =indirect("'" & a1 & "'!" & b1) if a1 and b1 are the two cells. John wrote: I have a workbook that has worksheets for each month, containing a table of rows of customer order info for the month. All tables are formated the same. I have a report page that nicely performs calculations and formats a report for a sinlge row of data from a single worksheet for a one customer. How do I set up my formulas so that I can substitute a different month (worksheet name) and row number in the formulas to get the save report for a different customer and/or month? More simply put, I have a hard coded formula =Jan!A4 in my report that I'd like to change the worksheet and row specification to say =Feb!A22 based on the contents of a cell containing the text "Feb" and another cell containing "22"? -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substituting cell content into a formula
Rob, this is great. Solves everything except if I later move column "A" to
another location in the referenced worksheet. How do I get the actual column LETTER dynamically so I can make this reference follow future changes to the referenced WS. I tried modifying your suggetion something like: =INDIRECT($AA$2 & "!" & COLUMN(INDIRECT($AA$2 & !A1)) & $AA$4) where $AA$2 is reprot cell containing the ref WS name & $AA$4 the row ref. This all seems a little convoluted and complicated to me, and I may have the syntax screwed up, but it won't work anyway, because COLUMN return a col number, not the col letter. How do I get the Col LETTER? Is there a simpler formula to do this? "Rob Bovey" wrote: "John" wrote in message ... More simply put, I have a hard coded formula =Jan!A4 in my report that I'd like to change the worksheet and row specification to say =Feb!A22 based on the contents of a cell containing the text "Feb" and another cell containing "22"? Hi John, If "Feb" is in cell A1 and "22" is in cell A2, the following formula will do what you're looking for: =INDIRECT(A1 & "!A" & A2) -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substituting cell content into a formula
Hi John,
Since you're already hard-coding the Sheet name and row number in other cells I'd just add the column letter to the list of inputs as well. A1 = "Feb" A2 = "22" A3 = "A" =INDIRECT(A1 & "!" & A3 & A2) Now you can change any of the three source cells to move the reference as appropriate. To answer your other question, you can get the column letter for any column with the following formula: =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","") -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "John" wrote in message ... Rob, this is great. Solves everything except if I later move column "A" to another location in the referenced worksheet. How do I get the actual column LETTER dynamically so I can make this reference follow future changes to the referenced WS. I tried modifying your suggetion something like: =INDIRECT($AA$2 & "!" & COLUMN(INDIRECT($AA$2 & !A1)) & $AA$4) where $AA$2 is reprot cell containing the ref WS name & $AA$4 the row ref. This all seems a little convoluted and complicated to me, and I may have the syntax screwed up, but it won't work anyway, because COLUMN return a col number, not the col letter. How do I get the Col LETTER? Is there a simpler formula to do this? "Rob Bovey" wrote: "John" wrote in message ... More simply put, I have a hard coded formula =Jan!A4 in my report that I'd like to change the worksheet and row specification to say =Feb!A22 based on the contents of a cell containing the text "Feb" and another cell containing "22"? Hi John, If "Feb" is in cell A1 and "22" is in cell A2, the following formula will do what you're looking for: =INDIRECT(A1 & "!A" & A2) -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Substituting for cell | Excel Discussion (Misc queries) | |||
how to get formula content of a cell? | Excel Worksheet Functions | |||
Cell content / formula bar different | Excel Discussion (Misc queries) | |||
excel is substituting unicode for a defined name of a cell | Excel Discussion (Misc queries) | |||
substituting two text occurances in same cell. | Excel Worksheet Functions |