In a new cell, finding the true-value location derived from an if statement equation

[hamurabi5]

I have an if statement equation where depending on a value in another cell, the location of the true value of the if statement changes. In an unrelated cell I want to be able to reference the true value found in the if statement. How do i do that?


Example of concept:
=if(a1=1,b1,if(a1=2,b2)) In another cell how do i reference back to that if statement telling me whether the true value of the if statement was from cell b1 or b2? Ultimately, this new cell will be offset based on the location of the true if statement and will be used going across sheets in the same workbook.

[FDibbins]

Hi and welcome to the forum

there is probably more to this that what you have said, but try this...

=if(=if(a1=1,"b1",if(a1=2,"b2"))

[hamurabi5]

Yeah, it's a little more complicated than that. The original if statement returns a value from another sheet. The cell next to the if statement is based on the value below the answer in the if statement. So, for this second cell, the value from the if statement doesnt matter but the location to find its own value is directly related to the location of the value found in the if statement.

Essentially, this second formula has to go back to the if statement and say 'where did you find your value? i want the value underneath that location' So i know how to offset i just dont know how to reference back to the value location of the if statement.

[FDibbins]

Can you upload a small sample workbook, showing what you have and what you ant?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[MrShorty]

I sometimes think this sort of "long string of if's testing the same cell for different values" is easier to work with if you build it as a lookup table.

Since the return values appear to all come from column B, the MATCH() function should be able to tell you what entry in column B is returned (http://office.microsoft.com/en-us/ex...945.aspx?CTT=1) Something like =MATCH(result,$B$1:$B$100,0) will return the row number corresponding to result.

[hamurabi5]

Example File attached.

I work with a group that does audits, audits can consist of multiple buildings, floors, etc... hence in single spreadsheet will be multiple audit sheets (here there are audit1 and audit2) I have to take the info from the audit details and put it in a single master detail sheet. Audit info is entered vertically for ease of use on site and the master detail has to be horizontal for other reasons. There are reasons for this and i deleted them but the horizontal master detail sheet does translate into a lot more data.

The top table in the audit detail is the existing tech and the solution tech and then going down to the second table, staying in the same column is the rooms in which the above existing and solution tech applies and the quantity in each location.

On the master detail page, the first 2 columns are audit sheet and solution #. Meaning once i have the formulas correct, i can enter the audit sheet to pull info from and the tech solution for that location. I have to manually copy and paste the locations that apply to a certain tech because that is always going to vary. So from spreadsheet to spreadsheet i dont know if a solution tech is going to apply to one room or fifty and the quantity in those rooms is always going to vary. so location and quantity are manual. Everything else however should be able to self populate by me typing the solution number and the tab name to pull the info from.


Column E on the master detail, 'category' i put the basic if statement in that pulls info from the sheet i want (the real if statementconsists of about 15 ifs). I know i could put these long if statements in each cell that reference the inputs in colummn a and b but i want to simplify it. So for example, on the audit sheet underneath category is type. So on the detail, i want the formula in type to see where the category on the left is pulling info from and then take the value underneath from the correct audit sheet in the correct solution column.


Does this make sense?

[MrShorty]

I think it makes sense.

If I've understood, I would use an HLOOKUP() (http://office.microsoft.com/en-us/ex...997.aspx?CTT=1) formula for all entries. The MATCH() function can be used to determine which row to pull data from. If desired, you could still include the INDIRECT() function to specify which spreadsheet to extract data from.