Home |
Search |
Today's Posts |
#1
|
|||
|
|||
copying formula
I need to copy the formula "=IF(MAX(Sheet2!B5:B200)(TODAY()-21),"not
due","due") in sheet1 from cell b2 to all the cells between b3 and b200. Each cell should refer to a new worksheet, ie b3 refers Sheet3, b4 to Sheet4 and so on. When I try to fill it in or copy and paste, only the cell range changes, and I don't seem to be able to relate it to the sheets required. I'd be grateful for any help please JG |
#2
|
|||
|
|||
As far asI know, it cant be done, you need to change the sheet names
manually. I would however be delighted to be proved wrong! Regards, "JG" wrote in message ... I need to copy the formula "=IF(MAX(Sheet2!B5:B200)(TODAY()-21),"not due","due") in sheet1 from cell b2 to all the cells between b3 and b200. Each cell should refer to a new worksheet, ie b3 refers Sheet3, b4 to Sheet4 and so on. When I try to fill it in or copy and paste, only the cell range changes, and I don't seem to be able to relate it to the sheets required. I'd be grateful for any help please JG |
#3
|
|||
|
|||
=IF(MAX(INDIRECT(A1&"!B5:B200"))(TODAY()-21),"not due","due")
should sort it out where A1 contains text Sheet2 Copy down so A2 contains Sheet3 etc. HTH RES |
#4
|
|||
|
|||
Thanks, but I am typing =IF(MAX(INDIRECT(Sheet2&"!B5:B200))(TODAY()-21),"not
due","due") and Excel tells me that there is an error, any thoughts please ? " wrote: =IF(MAX(INDIRECT(A1&"!B5:B200"))(TODAY()-21),"not due","due") should sort it out where A1 contains text Sheet2 Copy down so A2 contains Sheet3 etc. HTH RES |
#5
|
|||
|
|||
Note how you can combine 2 or more values into a single
string variable, which then slots into your formula. I use a similar technique to automatically select the number of rows of data present - it varies every day - prior to printing. bobf -----Original Message----- As far asI know, it cant be done, you need to change the sheet names manually. I would however be delighted to be proved wrong! Regards, "JG" wrote in message ... I need to copy the formula "=IF(MAX(Sheet2!B5:B200) (TODAY()-21),"not due","due") in sheet1 from cell b2 to all the cells between b3 and b200. Each cell should refer to a new worksheet, ie b3 refers Sheet3, b4 to Sheet4 and so on. When I try to fill it in or copy and paste, only the cell range changes, and I don't seem to be able to relate it to the sheets required. I'd be grateful for any help please JG . |
#6
|
|||
|
|||
Sorry Bob, I don't understand your reply, do you have a suggestion how to
solve my problem ? "BOBF" wrote: Note how you can combine 2 or more values into a single string variable, which then slots into your formula. I use a similar technique to automatically select the number of rows of data present - it varies every day - prior to printing. bobf -----Original Message----- As far asI know, it cant be done, you need to change the sheet names manually. I would however be delighted to be proved wrong! Regards, "JG" wrote in message ... I need to copy the formula "=IF(MAX(Sheet2!B5:B200) (TODAY()-21),"not due","due") in sheet1 from cell b2 to all the cells between b3 and b200. Each cell should refer to a new worksheet, ie b3 refers Sheet3, b4 to Sheet4 and so on. When I try to fill it in or copy and paste, only the cell range changes, and I don't seem to be able to relate it to the sheets required. I'd be grateful for any help please JG . |
#7
|
|||
|
|||
If you use the formula as you wrote
=IF(MAX(INDIRECT(Sheet2&"!B5:B200))(TODAY()-21),"not due","due") Excel is looking at the Indirect function and looking for a Name Sheet2 which does not exist therefore #REF It needs to be text so =IF(MAX(INDIRECT("Sheet2"&"!B5:B200))(TODAY()-21),"not due","due") would work However this will not increment to Sheet3 as you copy down The solution is to create the list in one column A1 Sheet2 A2 Sheet3 A4 Sheet4 etc.. which can be done by filling down then use the function to reference the cells containing the sheet name =IF(MAX(INDIRECT(A1&"!B5:B200"))(TODAY()-21),"not due","due") =IF(MAX(INDIRECT(A2&"!B5:B200"))(TODAY()-21),"not due","due") Hope this clarifies RES |
#8
|
|||
|
|||
Assuming you want to refer to B5:B200 on every sheet, then try:
=IF(MAX(INDIRECT("Sheet"&ROW(B2)&"!B$5:B$200"))(T ODAY()-21),"not due","due") Greg "JG" wrote: I need to copy the formula "=IF(MAX(Sheet2!B5:B200)(TODAY()-21),"not due","due") in sheet1 from cell b2 to all the cells between b3 and b200. Each cell should refer to a new worksheet, ie b3 refers Sheet3, b4 to Sheet4 and so on. When I try to fill it in or copy and paste, only the cell range changes, and I don't seem to be able to relate it to the sheets required. I'd be grateful for any help please JG -- Greg phobos78-marslink-net Replace dashes and move in by 1 planet to reply. |
#9
|
|||
|
|||
Greg, thanks for your suggestion, but please excuse my ingnorance, does the
text "Sheet" need to be replaced by sheet1, sheet2 etc. "Greg" wrote: Assuming you want to refer to B5:B200 on every sheet, then try: =IF(MAX(INDIRECT("Sheet"&ROW(B2)&"!B$5:B$200"))(T ODAY()-21),"not due","due") Greg "JG" wrote: I need to copy the formula "=IF(MAX(Sheet2!B5:B200)(TODAY()-21),"not due","due") in sheet1 from cell b2 to all the cells between b3 and b200. Each cell should refer to a new worksheet, ie b3 refers Sheet3, b4 to Sheet4 and so on. When I try to fill it in or copy and paste, only the cell range changes, and I don't seem to be able to relate it to the sheets required. I'd be grateful for any help please JG -- Greg phobos78-marslink-net Replace dashes and move in by 1 planet to reply. |
#10
|
|||
|
|||
Hi JG,
You might help answer your own question, if you tried the formula, or split it up into components to see what it does. .. You use the fill handle to copy the formula down . http://www.mvps.org/dmcritchie/excel/fillhand.htm Use of INDIRECT will use the address of the value returned as opposed to the value -- indirect reference http://www.mvps.org/dmcritchie/excel/indirect.htm =IF(MAX(INDIRECT("Sheet"&ROW(B2)&"!B$5:B$200"))(T ODAY()-21),"not due","due") ROW(B2) returns 2 because that is the row that B2 is on ROW() if it were used would return the row that the formula is on you might be able to see what is happening if you test with just this part in a cell it could be on any row because it is B2 that indicates the row used in the formula to be used to complete the sheet number2 ="Sheet"&ROW(B2)&"!B$5:B$200" which will give you a clearer idea of the what the full formula is doing Sheet2!B$5:B$200 Sheet3!B$5:B$200 Sheet4!B$5:B$200 Sheet5!B$5:B$200 Sheet6!B$5:B$200 Which is like what you asked for, those are sheet numbers and not sheetnames. HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "JG" wrote in message ... Greg, thanks for your suggestion, but please excuse my ingnorance, does the text "Sheet" need to be replaced by sheet1, sheet2 etc. "Greg" wrote: Assuming you want to refer to B5:B200 on every sheet, then try: =IF(MAX(INDIRECT("Sheet"&ROW(B2)&"!B$5:B$200"))(T ODAY()-21),"not due","due") Greg "JG" wrote: I need to copy the formula "=IF(MAX(Sheet2!B5:B200)(TODAY()-21),"not due","due") in sheet1 from cell b2 to all the cells between b3 and b200. Each cell should refer to a new worksheet, ie b3 refers Sheet3, b4 to Sheet4 and so on. When I try to fill it in or copy and paste, only the cell range changes, and I don't seem to be able to relate it to the sheets required. I'd be grateful for any help please |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble copying and pasting a formula | Excel Discussion (Misc queries) | |||
Trouble copying and pasting a formula | Excel Worksheet Functions | |||
copying LOOKUP formula | Excel Worksheet Functions | |||
Paste is is copying in formula, but display is wrong. | Excel Discussion (Misc queries) | |||
Paste is is copying in formula, but display is wrong. | Excel Worksheet Functions |