Formula to display cell contents of visible cell

andrewb90

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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Aladin Akyurek

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

ADVERTISEMENT

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,203
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,075

ADVERTISEMENT

no I'm not using autofilter.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

andrewb90

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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,129,569
Messages
5,637,109
Members
416,959
Latest member
Mohzein

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top