How to use Address function to write in the found cell? Microsoft Excel

Question

I am trying to find a specific cell based on variable data. Currently, I'm using the "address" function to give the name of the cell, in this case Q10, and then filling it manually. Is there a way to make it so the cell will be filled or have something written in it automatically?

Worksheet, green highlight for the cell found with formula, blue highlight is that cell on the sheet

Answer 1

I think you don't need to use the ADDRESS formula here.

You want to format a cell based on the numbers in cells E12 and E13.

For that, you can use conditional formatting.

Set up a rule like this:

Your current implementation appears to use the decimals in cells E12 and E13 to create an address of row and column numbers, though it highlights coordinate (8,4) and your data, when rounded, would be (9,4).

This rule will place the highlight in coordinate (9,4). If you don't want that, then adjust the rule definition accordingly.

As you can see, when the coordinates change, the highlight moves.

While this will probably do the job for what you've set up so far, since you're using coordinates, perhaps an appropriately labelled x/y scatter plot would be easier?

Answer 2

Try this method:

1. Select the Range J5:AC13, go to Home- Conditional Formatting- New Rule...

2. Select the Use a formula to determine which cells to format- enter the formula and Format cells:

   =ADDRESS(14-$E$13,$E$12+9)="$"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"$"&ROW()