Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying a formula containing the INDIRECT function
Dear All
I have the following formula in cell B4: =IF(INDIRECT("'"&$A4&"'!B$16")="N/A","N/A",INDIRECT("'"&$A4&"'!B$16")) It refers to cell B16 in a worksheet where the name of that worksheet is in cell A4. If N/A is found in cell B16 of that worksheet then N/A appears in the cell where the formula is written, if not the contents of B16 of that worksheet appear instead. I want to drag this formula across to the right of my worksheet (to cells C4, D4 etc) and I need the B16 to change to C16, D16 etc accordingly. How do I modify formula so that B16 changes accordingly, please? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying a formula containing the INDIRECT function
If N/A is found in cell B16 of that worksheet then
N/A appears in the cell where the formula is written, if not the contents of B16 of that worksheet appear instead. =IF(INDIRECT("'"&$A4&"'!B$16")="N/A","N/A",INDIRECT("'"&$A4&"'!B$16")) You don't need the IF function. Assume you enter the first formula in cell A1: =INDEX(INDIRECT("'"&$A4&"'!B16:IV16"),COLUMNS($A1: A1)) Copy across as needed. -- Biff Microsoft Excel MVP "Pelham" wrote in message ... Dear All I have the following formula in cell B4: =IF(INDIRECT("'"&$A4&"'!B$16")="N/A","N/A",INDIRECT("'"&$A4&"'!B$16")) It refers to cell B16 in a worksheet where the name of that worksheet is in cell A4. If N/A is found in cell B16 of that worksheet then N/A appears in the cell where the formula is written, if not the contents of B16 of that worksheet appear instead. I want to drag this formula across to the right of my worksheet (to cells C4, D4 etc) and I need the B16 to change to C16, D16 etc accordingly. How do I modify formula so that B16 changes accordingly, please? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying a formula containing the INDIRECT function
Hi,
Try this: =INDIRECT("'"&$A4&"'!"&ADDRESS(16,COLUMN(B1))) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Pelham" wrote: Dear All I have the following formula in cell B4: =IF(INDIRECT("'"&$A4&"'!B$16")="N/A","N/A",INDIRECT("'"&$A4&"'!B$16")) It refers to cell B16 in a worksheet where the name of that worksheet is in cell A4. If N/A is found in cell B16 of that worksheet then N/A appears in the cell where the formula is written, if not the contents of B16 of that worksheet appear instead. I want to drag this formula across to the right of my worksheet (to cells C4, D4 etc) and I need the B16 to change to C16, D16 etc accordingly. How do I modify formula so that B16 changes accordingly, please? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying a formula containing the INDIRECT function
=INDIRECT("'"&$A4&"'!"&ADDRESS(16,COLUMN(B1)))
If you're going to use the ADDRESS function: =INDIRECT(ADDRESS(16,COLUMN(B1),,,$A4)) I would not use COLUMN(B1). =INDIRECT(ADDRESS(16,COLUMNS($A1:B1),,,$A4)) -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, Try this: =INDIRECT("'"&$A4&"'!"&ADDRESS(16,COLUMN(B1))) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Pelham" wrote: Dear All I have the following formula in cell B4: =IF(INDIRECT("'"&$A4&"'!B$16")="N/A","N/A",INDIRECT("'"&$A4&"'!B$16")) It refers to cell B16 in a worksheet where the name of that worksheet is in cell A4. If N/A is found in cell B16 of that worksheet then N/A appears in the cell where the formula is written, if not the contents of B16 of that worksheet appear instead. I want to drag this formula across to the right of my worksheet (to cells C4, D4 etc) and I need the B16 to change to C16, D16 etc accordingly. How do I modify formula so that B16 changes accordingly, please? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying a formula containing the INDIRECT function
Top posting WITH a sig is SO lame.
I have had good success with simply using the search and replace tools in notepad when drag style formula copying fails. A columnar paste from notepad goes in just fine. It works well when excel wants to auto-morph more figures than the user wants it to on a drag copy operation. On Sat, 29 Aug 2009 22:59:55 -0400, "T. Valko" wrote: =INDIRECT(ADDRESS(16,COLUMNS($A1:B1),,,$A4)) -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, Try this: =INDIRECT("'"&$A4&"'!"&ADDRESS(16,COLUMN(B1))) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Pelham" wrote: Dear All I have the following formula in cell B4: =IF(INDIRECT("'"&$A4&"'!B$16")="N/A","N/A",INDIRECT("'"&$A4&"'!B$16")) It refers to cell B16 in a worksheet where the name of that worksheet is in cell A4. If N/A is found in cell B16 of that worksheet then N/A appears in the cell where the formula is written, if not the contents of B16 of that worksheet appear instead. I want to drag this formula across to the right of my worksheet (to cells C4, D4 etc) and I need the B16 to change to C16, D16 etc accordingly. How do I modify formula so that B16 changes accordingly, please? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying a formula containing the INDIRECT function
On Aug 30, 3:02*am, Shane Devenshire
wrote: Hi, Try this: =INDIRECT("'"&$A4&"'!"&ADDRESS(16,COLUMN(B1))) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Pelham" wrote: Dear All I have the following formula in cell B4: =IF(INDIRECT("'"&$A4&"'!B$16")="N/A","N/A",INDIRECT("'"&$A4&"'!B$16")) It refers to cell B16 in a worksheet where the name of that worksheet is in cell A4. If N/A is found in cell B16 of that worksheet then N/A appears in the cell where the formula is written, if not the contents of B16 of that worksheet appear instead. I want to drag this formula across to the right of my worksheet (to cells C4, D4 etc) and I need the B16 to change to C16, D16 etc accordingly. How do I modify formula so that B16 changes accordingly, please?- Hide quoted text - - Show quoted text - Brilliant, Shane - thank you! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying a formula containing the INDIRECT function
On Aug 30, 11:59*am, "T. Valko" wrote:
=INDIRECT("'"&$A4&"'!"&ADDRESS(16,COLUMN(B1))) If you're going to use the ADDRESS function: =INDIRECT(ADDRESS(16,COLUMN(B1),,,$A4)) I would not use COLUMN(B1). =INDIRECT(ADDRESS(16,COLUMNS($A1:B1),,,$A4)) -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in ... Hi, Try this: =INDIRECT("'"&$A4&"'!"&ADDRESS(16,COLUMN(B1))) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Pelham" wrote: Dear All I have the following formula in cell B4: =IF(INDIRECT("'"&$A4&"'!B$16")="N/A","N/A",INDIRECT("'"&$A4&"'!B$16")) It refers to cell B16 in a worksheet where the name of that worksheet is in cell A4. If N/A is found in cell B16 of that worksheet then N/A appears in the cell where the formula is written, if not the contents of B16 of that worksheet appear instead. I want to drag this formula across to the right of my worksheet (to cells C4, D4 etc) and I need the B16 to change to C16, D16 etc accordingly. How do I modify formula so that B16 changes accordingly, please?- Hide quoted text - - Show quoted text - Thank you Biff - terrific! How on earth do you know this stuff so easily?!? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying a formula containing the INDIRECT function
How on earth do you know this stuff so easily?!?
I do this stuff every day, all day! Thanks for the feedback! -- Biff Microsoft Excel MVP "Pelham" wrote in message ... On Aug 30, 11:59 am, "T. Valko" wrote: =INDIRECT("'"&$A4&"'!"&ADDRESS(16,COLUMN(B1))) If you're going to use the ADDRESS function: =INDIRECT(ADDRESS(16,COLUMN(B1),,,$A4)) I would not use COLUMN(B1). =INDIRECT(ADDRESS(16,COLUMNS($A1:B1),,,$A4)) -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in ... Hi, Try this: =INDIRECT("'"&$A4&"'!"&ADDRESS(16,COLUMN(B1))) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Pelham" wrote: Dear All I have the following formula in cell B4: =IF(INDIRECT("'"&$A4&"'!B$16")="N/A","N/A",INDIRECT("'"&$A4&"'!B$16")) It refers to cell B16 in a worksheet where the name of that worksheet is in cell A4. If N/A is found in cell B16 of that worksheet then N/A appears in the cell where the formula is written, if not the contents of B16 of that worksheet appear instead. I want to drag this formula across to the right of my worksheet (to cells C4, D4 etc) and I need the B16 to change to C16, D16 etc accordingly. How do I modify formula so that B16 changes accordingly, please?- Hide quoted text - - Show quoted text - Thank you Biff - terrific! How on earth do you know this stuff so easily?!? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Name of named formula used in an INDIRECT function | Excel Worksheet Functions | |||
Copying a formula which uses indirect() | Excel Worksheet Functions | |||
copying the Hyperlink function result without copying the actual formula | Excel Worksheet Functions | |||
Can INDIRECT function reference a cell that contains a formula | Excel Worksheet Functions | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions |