SOLVED

referencing a cell based on a row value

Copper Contributor

Hi all, 

I am wondering if there is a way to reference the value in C29 by the string in A29 which is "Total Assets less Current Liabilities:"

 

Essentially when I generate a report from Sage 50, some months have different data and the figure I want to reference is in C28,27 ect.

 

So If "Total Assets less Current Liabilities:" gets moved to say A30 , my Equity Cell in F29 Looks for the Value "Total Assets less Current Liabilities:" and returns the value from the C column.

Sorry If I have explained this poorly.

I've spent many hours trying to google for a solution but have had no luck.

Thank you in advance

Carl

2 Replies
best response confirmed by Carllewis1989 (Copper Contributor)
Solution

@Carllewis1989 Not familiar with Sage 50, but I assume that the numbers in columns A:D come from that system, and that you added columns E and F yourself.

 

To be honest, the report doesn't seem to be very well designed, or perhaps the import has caused some numbers to be in odd locations. But never mind, to answer your specific question, you could enter

 

=INDEX(C1:C100,MATCH("Total Assets less Current Liabilities:",A1:A100,0))

 

in F29. This will return a number from column C, based on the row where the text "Total Assets......." is found in column A. Calling this number "Equity", however, is not correct I believe, as equity is represented by Total assets minus Total liabilities. But perhaps you intend to deduct Total long-term liabilities as well.

 

I limited the range from rows 1 to 100. You can adjust according to your needs.

 

Thank you very much Riny,

You are completely right about Equity, I've used the wrong figures!

I did look into the Index function, but I couldn't get it to work.

This has made my day and will help me greatly going on.

Thanks again.

Carl

1 best response

Accepted Solutions
best response confirmed by Carllewis1989 (Copper Contributor)
Solution

@Carllewis1989 Not familiar with Sage 50, but I assume that the numbers in columns A:D come from that system, and that you added columns E and F yourself.

 

To be honest, the report doesn't seem to be very well designed, or perhaps the import has caused some numbers to be in odd locations. But never mind, to answer your specific question, you could enter

 

=INDEX(C1:C100,MATCH("Total Assets less Current Liabilities:",A1:A100,0))

 

in F29. This will return a number from column C, based on the row where the text "Total Assets......." is found in column A. Calling this number "Equity", however, is not correct I believe, as equity is represented by Total assets minus Total liabilities. But perhaps you intend to deduct Total long-term liabilities as well.

 

I limited the range from rows 1 to 100. You can adjust according to your needs.

 

View solution in original post