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

[zookeepertx]

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

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

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

Thank you for any help,

Jenny

 

[pgc01]

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.

 

[zookeepertx]

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