How to list cell location dependent upon another cell's value.

If a cell in column A has a value of 1, i would like the address of another cell in that another column (for instance B) to appear in the C column.
Not the contents of cell B, but the actual cell location, such as B1, would need to appear in column C

=ADDRESS(A1,2) would give $B$1

Hi Comp,

I agree that you are looking for something dealing with the Address function. See if the attached is what you want. If not then be more specific and give a better example or better yet a sample workbook with expected result.

Thanks, but i may not be explaining this correctly. Here is a serious brain teaser.....a massive puzzle!

I have a program that outputs a CSV file with 8 separate strings of numbers (each string is roughly 300 numbers together, and each sequence is listed sequentially). These strings need added together.

In the attached sheet the values i need to combine are in the B column. The J column has the formula i use to add the values together =SUM(B1,B298,B595,B892,B1189,B1486,B1783,B2080), and by cascading this formula down the J column i can add the first 300 values.

The unfortunate aspect is that the first cell of each string isn't always in the same position. After B1 the beginning cell address can change with different outputs. For example, another output might require =SUM(B1,B315,B605, etc)

I have figured a means of filtering the data by dividing other properties of the data output. (the data output is columns A and D). If the value in column F equals 1 then I need the corresponding address of the B column in the same row inserted automatically into the formula.

Formula:

Please Login or Register  to view this content.

Regards, TMS

You are a Genius! Seriously that is amazing, and the =SUMIF($F$1:$F$2376,$F1,$B$1:$B$2376) is exactly what i was looking for!
My intention was to get started with the basic version of what i need to see if it is even possible, and then move into the more complex version by building off of the basic knowledge.

So here is the really hard part, and may be impossible.

The string of values has this same repeating sequence of 8 outputs that need added, but the outputs continue and continue one after the other.

After each series of 8 are added, another series of 8 begins, but the ending point of each string can be different because of the fluctuating string lengths.

I have been altering these values manually for months with each output, the most tedious undertaking in the history of man I believe.

Here is another version of the same chart, except expanded. The other columns (N, R) have the other successive strings. This usually stretches out to 16 series in length, but there is an upload limitation for the file size so i truncated it.

The rule still holds that if you filter column F by the 1 value the corresponding B columns are those that need added, it is just a continuation of the same theme ad nauseum.

These columns can be listed sequentially if needed, just each equaling 300 values before the next 300 begin. I hope this is described correctly!

Thanks for the rep and the kind words.

And thanks for your personal email. Unfortunately, it's not really a case of "how much will it cost to fix the problem".

I no longer understand the "rules of engagement". The SUMIF seemed to work effectively for the original sample data. However, with the additional rows, it does not return the same (correct) results. And I don't know what additional conditions to apply to the original formula, let alone for the additional columns.

I would recommend that you mark this thread as Solved, given that your original question has been answered. And then start a new thread, providing a link to this thread for background. In that way, you will generate new interest as the new thread will show as Unanswered ... many people, including myself, only pick up on threads with a zero or very low response count ... especially if the person seen to be answering (?) the question is an Expert or above.

Regards, TMS



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Just as a feed into the next thread, when you create it, I have been trying to understand the relationship between the cells. See the attached updated example. However, I am finding some inconsistency in that the first eight cells are separated by 297, the second set of cells are separated by 296 and the third are separated by 296 or 295.

HTML Code: 

HTML Code: 

HTML Code: 

So ... I am a little bemused

Thank you very much for the continued help. I have started a new thread here.

As a service to those who may come across this thread in the future and need help, i will post the following reply to the last post, but it is also posted in the new thread...

The length inconsistency is fine, I still require each string added up 300 times, even if the strings are different lengths.(falls within an accepted sampling error rate) So i would need the first set of 8 combined for 300 cells, followed by the next string of 8 combined for another 300, repetitively for 16 iterations. In teh Complexity!! example above, i have each successive string combined in a new column, but if they were listed sequentially in one column that would also be fine, if it simplifies things.