#1
|
|||
|
|||
Vlookup - if the result is blank then enter a formula
I have a spreadsheet with forecast dates and completion dates. What I want to do is a vlookup to capture actual dates. However, if the result is blank then I want to enter a formula.
This is what I have but doesn't work: =IF(VLOOKUP(A4,Sheet1!$A:$BF,30,0)=" ",V4+5,VLOOKUP(A4,Sheet1!$A:$BF,30,0)) and =IFERROR(VLOOKUP(B5,Sheet1!$B:$BF,29,0),V5+5) So in sheet1 is what I call the raw data. Sheet 2 has a vlookup to pull the information from sheet1. Now I need to the formula in sheet2. If the vlookup returns a blank then B2+5. So in sheet2 cell C3, if the vlookup returns a blank then B3+5. I have attached a sample spreadsheet. Thank you in advance for your assistance. |
#2
|
|||
|
|||
I don't see anything in column V on sheet2 ???!!!
But I see a major problem with your data structure! Currently you have 3 tasks. What happens, when you get 4th task? And what happens when the number of tasks will be about 100 or more? Unless it is an once only project, in best scenario, you have to redesign your tables continuously over many years! |
#3
|
|||
|
|||
Try….....................
In "Sheet2" B2, copied across and down : =IFERROR(1/(1/VLOOKUP($A2,Sheet1!$A$2:$G$6,MATCH(B$1,Sheet1!$A$1 :$G$1,0),0)),A2+5) Regards Bosco |
#4
|
|||
|
|||
My apologies, the "V" should be "B". The report isn't going to get any larger. This is one project. Any new projects have new reports created.
|
#5
|
|||
|
|||
Thank you so much bosco_yip!
|
#6
|
|||
|
|||
Quote:
Simply with different design you can have a single report of this kind, which works for long time. And probably some other reports (like yearly one) too. And you get an archive of tasks. I think some 10000-20000 tasks will be OK without slowing the workbook too much down. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
IF Formula not returns result - Help | Haha88 | Excel | 6 | 02-11-2018 02:07 PM |
Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula | innkeeper9 | Excel | 2 | 09-13-2016 08:59 PM |
How To Move Focus From VLOOKUP Result to Report Sheet | swindon.expat | Excel | 2 | 04-10-2016 12:15 AM |
formula, 2 criteria, one result | cjdstephenson | Excel | 4 | 06-02-2015 09:17 PM |
Fetching more than one result with VLookup on multiple sheets | lilvillaf | Excel | 2 | 06-12-2014 05:39 PM |