Indirect formula
- Thread starter Rodrigues
- Start date
NARAYANK991
Excel Ninja
Hi Rodrigues ,
The following works , but it is cumbersome to use this construct since the address has to be manually entered for every cell.
=INDIRECT("'" & BasePath & "[Prod Log Days " & CurrentDate & ".xls]A - M'!$B$4")
How can you use INDIRECT on A4 , since A4 contains a value ?
Or do you want to lookup the value in A4 in the other file ? If so , then the formula will need to be just a VLOOKUP , as in :
=IFERROR(VLOOKUP(A5,INDIRECT("'" & BasePath & "[Prod Log Days " & CurrentDate & ".xls]A - M'!$A$4:$B$6"),2,FALSE),0)
Narayan
The following works , but it is cumbersome to use this construct since the address has to be manually entered for every cell.
=INDIRECT("'" & BasePath & "[Prod Log Days " & CurrentDate & ".xls]A - M'!$B$4")
How can you use INDIRECT on A4 , since A4 contains a value ?
Or do you want to lookup the value in A4 in the other file ? If so , then the formula will need to be just a VLOOKUP , as in :
=IFERROR(VLOOKUP(A5,INDIRECT("'" & BasePath & "[Prod Log Days " & CurrentDate & ".xls]A - M'!$A$4:$B$6"),2,FALSE),0)
Narayan
Narayan
On Book1 Columns A & B is a copy of columns A & B from Prod Log Days 161115, then I have others formulas on other cells (book1) looking at what is displaying on book1 from cells A22 to B1000 and extract what \I need. So column A has values and column B has alphanumeric.
The first formula "indirect" does work, it's hard as I have to change the cell ref one by one, but hopefully it will keep the formulas for each cell if saved a s a master.
Do you have/know any other way to look at Prod Log Days copy columns A and B and past them onto book1 using defined names?
Thanks for your help.
On Book1 Columns A & B is a copy of columns A & B from Prod Log Days 161115, then I have others formulas on other cells (book1) looking at what is displaying on book1 from cells A22 to B1000 and extract what \I need. So column A has values and column B has alphanumeric.
The first formula "indirect" does work, it's hard as I have to change the cell ref one by one, but hopefully it will keep the formulas for each cell if saved a s a master.
Do you have/know any other way to look at Prod Log Days copy columns A and B and past them onto book1 using defined names?
Thanks for your help.
Chihiro
Excel Ninja
I'd do something like following. Assuming data from previous date/sheets don't change.
Use formula like below to get data from closed workbook.
=IF('C:\Yourpath\[filename.xslx]Sheetname'!A4="","END",'C:\Yourpath\[filename.xslx]Sheetname'!A4)
Copy down until you see blank (IE."END").
Once data is pulled, paste over all formula as value, with exception of the "END" row at bottom.
Next day, just use Ctrl+F and change the blank row's formula and copy down.
Use formula like below to get data from closed workbook.
=IF('C:\Yourpath\[filename.xslx]Sheetname'!A4="","END",'C:\Yourpath\[filename.xslx]Sheetname'!A4)
Copy down until you see blank (IE."END").
Once data is pulled, paste over all formula as value, with exception of the "END" row at bottom.
Next day, just use Ctrl+F and change the blank row's formula and copy down.