Formula to display cell contents of visible cell

[andrewb90]

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

 

[Aladin Akyurek]

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?

 

[andrewb90]

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.

 

[Aladin Akyurek]

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:

=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.

 

[andrewb90]

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.

 

[Aladin Akyurek]

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")?

 

[andrewb90]

no I'm not using autofilter.

 

[Aladin Akyurek]

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?

 

[andrewb90]

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

 

[Aladin Akyurek]

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.