PDA

View Full Version : Managing Two Cells With Similar Ranges



NFLnut
11-01-2011, 08:44 PM
I am having trouble with a spreadsheet that I am working on. I am not even certain how to explain it without making it too confusing (I didn't even really know how to title this thread) -- specifically, I have a spreadsheet where in cell M9 I am using VLOOKUP to find a total value from a range in another worksheet (in range B2:G32 of a worksheet called "Lookups") determined by what is entered into cells G9:J9 on the main worksheet. I also have a similar formula in cell N9 which looks at what is entered into cells G9:J9 on the main worksheet, but only enters a value into N9 if any of the cells G9:J9 on the main worksheet contain data in a smaller range, B13:G16 from the "Lookups" worksheet. If a non-zero number is entered into cell N9 on the main worksheet, then M9 will subtract the amount from its total. :bug: (that was hard to even put into words, and I hope it actually makes sense ..)

I have this formula in M9: =IF(G9>0,VLOOKUP(G9,Lookups!$B$2:$G$32,3)+IF(H9>0,VLOOKUP(H9,Lookups!$B$2:$G$32,3)+IF(I9>0,VLOOKUP(I9,Lookups!$B$2:$G$32,3)+IF(J9>0,VLOOKUP(J9,Lookups!$B$2:$G$32,3)))-N9))

I thought I could do it with a formula in cell N9 like: =IF(G9>0,VLOOKUP(G9,Lookups!$B$13$:$G$16,3)+IF(H9>0,VLOOKUP(H9,Lookups!$B$13:$G$16,3)+IF(I9>0,VLOOKUP(I9,Lookups!$B$13:$G$16,3)+IF(J9>0,VLOOKUP(J9,Lookups!$B$13:$G$16,3))))

I have uploaded a sample workbook to make it easier to understand.

(BTW -- the "typo" in the first and second formula after "J9,..." is an error from this forum (the space in the word "Lookups") that I can't fix to read properly. It does not actually exist on the worksheet).

NFLnut
11-01-2011, 10:40 PM
I should add that although I tried those two formulas in M9 and N9, all I get is errors (in the form of #N/A ).