Automatically Pull 3 different values from Inconsistent Cells

**AZero** · 07-06-2011, 10:08 AM

The goal of my project is to calculate weekly demand for a certain type of server. The primary obstacles are a purchasing database that has a wide range of positions for user inputs. I have attached a file so you can see the formulas on the left I came up with. Unfortunately some of the references did not carry over, but you can see what I was trying to do with the indexing. I will end up pivoting with the 'Insert RAM', 'Short Desc', and 'Model'. The model I was planning on getting a list of actual models since many of those are not qualified models and doing a vlookup comparison to fill in those. Regardless it would be a similar if statement formula assuming I can find a consistent enough pattern of placement on all of the entries. I just got kind of lucky that the RAM amount was almost never on the bottom row. I put in this formula in the B column to make it work and this is what I am worried about:

=IF(AND(D2="Top",ISERROR(VALUE(E2))),IF(AND(NOT(D3="Bot"),NOT(ISERROR(VALUE(E3)))),F3,IF(AND(NOT(D4="Bot"),NOT(ISERROR(VALUE(E4)))),F4,IF(AND(NOT(D5="Bot"),NOT(ISERROR(VALUE(E5)))),F5,"MORE"))))

I am working with a database that has multiple lines per entry and an inconsistent entry point for each entry. There are about 83K entries and I do not have access to edit the database, I just have to work with it. The entries take up about 5-15 lines depending on how the user input the data. The data I need finds itself randomly placed in between about 5-6 different cells. I solved the problem with a nested IF statement that is of course enormous.

I was just thinking there HAS to be a formula out there that could have made this easier. On top of that every time I change the filter it causes the program to freeze for minutes at a time without showing the processor progress. This makes me think that the formulas are inefficient.

After solving one of these problems with a nested IF statement, I am starting to worry about the amount of processing time to verify another component of this which will take an even more complicated IF statement.

I am going to need to recreate this on a much larger scale and I would like to have some formulas that are not so complicated and laggy.

**NBVC** · 07-06-2011, 11:07 AM

Here is a simplified formula which reduces the number of functions you have to evaluate... that might work better....

Please Login or Register  to view this content.

**AZero** · 07-06-2011, 01:43 PM

Thanks for the response

So the goal basically was to drag the model to the 'Top' so the index function could populate the rest of the cells. In creating another nested IF statement I ran into another problem:

If there is an instance where there are only 3 entries and the nested IF statement pulls all the way down to say 5 or 6 entries, it could actually pull a lower entry up into the 'Top' portion of a completely different entry.

I sort of solved that for a few that I saw by adjusting the range, but bar looking through all the records is there a way do a running range adjustment based on adjacent entries? Some way to limit the number of nested if statements based on whether the 'Top' value was already filled by another formula?

**NBVC** · 07-06-2011, 01:47 PM

You can use a helper column that only identifies if the TOP value is filled, then in another cell, you can see if that helper is TRUE, then stop (i.e return a blank or other value)... then it will only process the remaining part of the formula if the logical statement is False.