Knowing when rows are added in another sheet

Topic

New Reply

I am creating a spreadsheet dashboard for a spreadsheet that tracks the progress of many activities as part of a project. Say I have a 100 activities that are listed by row, with various tasks listed in columns. The main sheet has some additional columns that I do not want included in the dashboard, so the dashboard only uses 4 of the columns from the full sheet and then colors them based on their percentage of completeness.

The row numbers for both sheets are the same. So in the dashboard, I refer to the other page as:

B4 – contains (=’Updates’!B4)

That works great, until a row is added in the Updates sheet. Then all of my references are off by a row from that point forward. I can not use an absolute reference because that will not help if a row is added either.

I can not figure out a way for the rows to match up. Column A is duplicated in both sheets. I think that may be the way to match up say column B with column D of the appropriate row, but do not know how or if that is the best way.

Thanks,

Andy

Reply | Quote

Replies

You could use the INDIRECT function: the formula

=INDIRECT(“Updates!B4”)

will remain the same when rows are inserted or deleted. To avoid the hassle of having to modify the formula for each row, you could use

=INDIRECT(“Updates!B”&ROW())

This can be filled down.

Alternatively, if the values in column A are unique, you could use VLOOKUP formulas, for example in B4:

=VLOOKUP($A4,Updates!$A$1:$B$60000,2,FALSE)

This formula can be filled down too.

Reply | Quote

You could use the INDIRECT function: the formula

=INDIRECT(“Updates!B4”)

will remain the same when rows are inserted or deleted. To avoid the hassle of having to modify the formula for each row, you could use

=INDIRECT(“Updates!B”&ROW())

This can be filled down.

Alternatively, if the values in column A are unique, you could use VLOOKUP formulas, for example in B4:

=VLOOKUP($A4,Updates!$A$1:$B$60000,2,FALSE)

This formula can be filled down too.

Thanks Hans!

VLOOKUP is the ticket. The only issue is the changing range on the other sheet. My solution was to set my lower row limit much higher than the existing 100 rows of data. I made it 200.

Thanks again!

Andy

Reply | Quote