# Formula to display cell contents of visible cell

#### andrewb90

##### Well-known Member
Hello,

I am trying to figure out how to have a formula that will display the contents of only 1 of the 5 different cells. either G5, G205, G405, G605, or G805. Only 1 of those cells would be visible at any given time, and the value of that cell is what I want to display.
I hope this is making sense.

- Andrew

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hello,

I am trying to figure out how to have a formula that will display the contents of only 1 of the 5 different cells. either G5, G205, G405, G605, or G805. Only 1 of those cells would be visible at any given time, and the value of that cell is what I want to display.
I hope this is making sense.

- Andrew

What is the exact range (including the header row) to which you apply autofilter? And what is the expected result: text or number?

The formula would be in H1.

The expected result is text. (Actually the text is going to be one of five things, "Week 1", Week 2", "Week 3", "Week 4", "Week 5") I just need to know which one is currently visible and have that returned in H1.

The formula would be in H1.

The expected result is text. (Actually the text is going to be one of five things, "Week 1", Week 2", "Week 3", "Week 4", "Week 5") I just need to know which one is currently visible and have that returned in H1.

We still don't know the range under autofiler.

Since the value you are interested in is one of the cells that can appear at every 200th row...

Control+shift+enter, not just enter:
Rich (BB code):
``````=LOOKUP(REPT("z",255),
IF(SUBTOTAL(3,OFFSET(G5,ROW(G5:G1000)-ROW(G5),0)),
IF(MOD(ROW(G5:G100)-ROW(G5),200)=0,G5:G1000)))
``````
Note that the formula takes up the range from G5 on deliberately.

I did that, but when I hid row 5, I didn't see the value change.

Additionally, I would like to be able to apply this to various cells, and not all of them are 200 rows apart.

I did that, but when I hid row 5, I didn't see the value change.

Additionally, I would like to be able to apply this to various cells, and not all of them are 200 rows apart.

Are you applying autofilter at all as your topic table implies ("visible")?

no I'm not using autofilter.

I did that, but when I hid row 5, I didn't see the value change.

Additionally, I would like to be able to apply this to various cells, and not all of them are 200 rows apart.

Are you applying autofilter at all as your topic table implies ("visible")?

no I'm not using autofilter.

But you are hiding a row manually. Is that what you are doing?

Yes. Actually, I have several macros that hide the rows at various times.

Yes. Actually, I have several macros that hide the rows at various times.

Control+shift+enter, not just enter:

=LOOKUP(REPT("z",255),IF(SUBTOTAL(103,OFFSET(G5,ROW(G5:G1000)-ROW(G5),0)),IF(MOD(ROW(G5:G1000)-ROW(G5),200)=0,G5:G1000)))

The notion of every 200th row is still assumed. If you can't maintain such an assumption, it's probably better to carry out this task also with a macro.

Replies
1
Views
103
Replies
2
Views
471
Replies
1
Views
128
Replies
5
Views
120
Replies
2
Views
351

1,207,420
Messages
6,078,435
Members
446,337
Latest member
nrijkers

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back