copying formula

[Guest]

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

 

[Alan]

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,

 

[RES]

=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

 

[Guest]

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 ?

 

[BOBF]

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

 

[Guest]

Sorry Bob, I don't understand your reply, do you have a suggestion how to
solve my problem ?

 

[Guest]

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

 

[Greg]

Assuming you want to refer to B5:B200 on every sheet, then try:

=IF(MAX(INDIRECT("Sheet"&ROW(B2)&"!B$5:B$200"))>(TODAY()-21),"not
due","due")

Greg

 

[Guest]

Greg, thanks for your suggestion, but please excuse my ingnorance, does the
text "Sheet" need to be replaced by sheet1, sheet2 etc.

 

[David McRitchie]

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"))>(TODAY()-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