Trying to write a formula where one of the references links to a cell with a formula in it.

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
503
Office Version
  1. 365
Platform
  1. Windows
Hello everyone!

I'm afraid this will be a complicated question, but I'll do the best I can to explain. (It would be easier if I could upload my workbook, but the forum won't seem to let me do that.

  • I have two sheets that I'm working with in a workbook. They're called "MONTH END TOTALS" and "COMPLETE2". MONTH END TOTALS gets its information from COMPLETE2.
  • MONTH END TOTALS has a table on it, with the data starting in C4. C2 has the year in it(for example - 2017); C3 has AUG, D3 has SEP and so on over to JUL.
  • COMPLETE2, column A, starting in row 2 consists of dates formatted as 11/08/17.
  • Data is being added to COMPLETE2 on a regular basis, so the number of rows changes.
  • There also may be empty rows on COMPLETE2
  • I need to use the last row number with data on COMPLETE2 as the end of the reference range in the formulas on MONTH END TOTALS

MONTH END TOTALS, cell C3 needs to count how many rows on COMPLETE2 have the same month/year in column A as is represented by C3/C2 on MONTH END TOTALS. (So, if COMPLETE2 has its last row of data in row 100, but 5 of those rows are empty, the range used in the other formulas would still be A2:A100. If 30 of those rows have dates anywhere in - for example - AUG of 2017, then formula in C4 on MONTH END TOTALS should return "30".

I had planned to have each formula in the table find the last data row on COMPLETE2 but I realized that was going to be really complicated, so I put a formula in O2 of MONTH END TOTALS to calculate that. Then I planned for any other formulas on that sheet to use O2 as a reference to the upper end of the range. So, O2 now has this in it
Code:
=VALUE(MATCH(99^99,COMPLETE2!A:A,1))
and that works fine.

But, no matter how hard I try and how long I play with it, I can NOT get the dang formulas to correctly use O2 correctly and the formulas all fail! I've been working on this for DAYS, whenever I don't have stuff to do; I mean literally DAYS!

I CAN get a formula to give me the month/year of a single cell on COMPLETE2 using this:
Code:
=MONTH(INDIRECT("COMPLETE2!A" & $O$2))&"/"&YEAR(INDIRECT("COMPLETE2!A" & $O$2))
so I know that it's possible to USE O2 in a formula. But when I try to get a formula to refer to COMPLETE2!$A$2:$A$(value in O2) it comes crashing down.

I've tried dozens of different syntaxes and nothing works. My most hopeful one had "COMPLETE2!$A$2:$A$" & O2 or varying versions of that, but I either get an error or a 0. If I go through Evaluate Formula, it never turns this - "COMPLETE2!$A$2:$A$20" into this - COMPLETE2!$A$2:$A$20 so maybe that's part of my problem?

Anyway, I've droned on long enough; sorry about that. If I've been TOTALLY confusing, please let me know & I'll try again. This dang thing has got me just about to tear my hair out! :oops:

Thank you for any help,

Jenny
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Jenny

Not clear to me.

Please post a working example that does not work how you expect.
- just 3-4 rows of data
- a formula that you think is not working the way you expect.

This way we can test.


Ex.:
I just did a simple example that you can test.

- wrote 10 in O2
- wrote numbers in some of the cells in A2:A10
- used the formula: =COUNT(INDIRECT("A2:A"&O2))

No problem. I get the number of cells in A2:A10 that contain a number value, as expected.

Post some simple example like this that does not work the way you think it should.
 
Last edited:

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
503
Office Version
  1. 365
Platform
  1. Windows
Oooh, I think I have something (sort of) working! I have to leave in a few minutes for the weekend, but I'll test it more on Monday and come back here to let you know how it's going.

Thanks for your reply!

Jenny
 

Watch MrExcel Video

Forum statistics

Threads
1,127,639
Messages
5,626,040
Members
416,155
Latest member
bokboxes

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