Formula to display cell contents of visible cell

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
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

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
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.
 

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
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

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
no I'm not using autofilter.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
Yes. Actually, I have several macros that hide the rows at various times.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,244
Messages
5,443,317
Members
405,227
Latest member
Ankit Jain

This Week's Hot Topics

Top