Need excel to return blank on cell with page reference

2
votes

I need help with a cost sheet. I don’t have much Excel experience, but I’ll try my best to describe.

Column A lists the name of all 50 of our products.

Column B lists the numeric product code of all 50 products.

Column C lists the project hours for each of our 50 products. This field needs to be blank until a product’s individual cost sheet is filled out, then its total cost is populated in Column D below, and a lookup function populates column c with the project hours (listed in the lookup table).

Column D lists the individual cost for each of our 50 products. This field is displayed with a blank but has the page reference to the total cost on the individual cost sheet. Once the individual cost sheet is completed and the total cost displays in Column D, the lookup function populates column C with project hours.

Example: A1 Widgets (product name)

B1 800 (numeric product code)

C1 400 (project hours – not displayed until cost appears in D1 and then lookup)

D1 $1500 (displays blank until cost is entered in individual cost sheet. But it isn’t truly blank – the formula line shows the reference to Widget cost sheet, cell number (L51) of total Widget cost as follows: 

’Widgets’!L51

== I’ve tried the following formula in cell B1 of my cost sheet, but it only works if I delete the page reference in D1.

=IF(ISBLANK(D1),"",VLOOKUP(B1,'Project Hrs Lookup'!A4:C23,3,FALSE))

Should I be using something beside ISBLANK since cell D1 has a page reference and isn’t truly blank?

1
excelvlookup
Welcome to StackOverflow. I know you are here for an answer and not an exercise in perfectionism, but you can edit your question to make it more likely that you will get one. First, your question is about Excel, but you did not add the Excel tag. It also appears to be more about ISBLANK than VLOOKUP. Finally, your actual question is very focused, which is a good thing, but you include many surrounding details that don't seem to be important for understanding the problem. Try to get to the point as quickly as possible and include extra details afterwards if you think they might help. – Medo42

1 Answers

1
votes

Try the following formulas:

  • For D1 cell: =IF(’Widgets’!L51="","",’Widgets’!L51) - this will display cost ONLY in case it is filled in ’Widgets’!L51 cell.
  • For C1 cell (i suppose you misspelled as B1 in your request): instead of your initial formula use =IF(D1="","",VLOOKUP(B1,'Project Hrs Lookup'!$A$4:$C$23,3,FALSE)):
    • I replaced ISBLANK(D1) with D1="" - the difference is that ISBLANK will return TRUE only in case the cell is truly blank, i.e. has nothing. My solution checks cell for empty string - that's the difference (see the above formula for D1).
    • I also changed VLOOKUP table reference from your relative: 'Project Hrs Lookup'!A4:C23 to absolute: 'Project Hrs Lookup'!$A$4:$C$23 - in case you decide to copy your furmula to some other place (and I'm almost sure you will) - the reference will remain as it should be (I faced the very same issue million times on my own long time ago (: ). As another solution here - define Named range for your reference table and use it in your formulas: in case you need to change it - you'll do that in ONE place, and all the rest will work as expected.

Good luck with your solution!