[Solved-Workaround] Referencing a value in a cell

[leeelson]

Apologies if this has been asked before.

I have a multi page spread sheet. One column consists of either "Y" or "N". For each page, I add Y and N values for that page and display it in a "total" cell (Y=31 N=12). I also want to display the cumulative Y & N values. This requires referencing the cumulative values from the previous page. How do I reference the numerical value (e.g. 31 in above example) in a cell with text?

[leeelson]

I have a cell :

Code: Select all

="Y="&COUNTIF(AS2:AS28;"Y")&" N="&COUNTIF(AS2:AS28;"N")

I have another cell:

Code: Select all

="Y="&COUNTIF(BX2:BX28;"Y")&" N="&COUNTIF(BX2:BX28;"N")

I want to add the "Y" values and "N" values of these cells together and display this in the same way. I ultimately want to do this for 100 cells so it doesn't make sense to keep adding code.

[Lupp]

For each page, I add Y and N values for that page and display it in a "total" cell (Y=31 N=12).

May I suppose you displayed two numeric values in one cell on the sheets concernd using formulas like

Code: Select all

=COUNTIF(Sheet1.A2:A40;"Y")&"Y, "&COUNTIF(Sheet1.A2:A40;"N")&"N"

If so, this is gravely against the grain in spreadsheets.
(Editing: A meanwhile crossed in post cleared this.)


The only way to get a total across sheets without referencing every cell you want to include expressly, is to use SUM applied to a so called cuboid like in

Code: Select all

SUM(MyStartingSheet.A1:MyFinalSheet.A1)

but cuboids aren't arrays and therefore you cannot evaluate such a parameter of SUM in array mode. This in a addition to useless complications you get anyway when extracting the summands from a "pair-of-values-cell".
My advice:
1) Preferably redesign your sheets without splitting data you want to evaluate together into multiple sheets.
2) If you can't observe this advice for some urgent reason, dedicate per sheet one cell to the number of "Y" and one cell to the number of "N", and add these pure numbers on your survey sheet using the above "SUM(cuboid)" construct.

(Editing again:)

... so it doesn't make sense to keep adding code.

I don't understand this. What do you mean by "keep adding code"?

[leeelson]

Thanks for your input!

The only way to get a total across sheets without referencing every cell you want to include expressly, is to use SUM applied to a so called cuboid like in

Code: Select all

SUM(MyStartingSheet.A1:MyFinalSheet.A1)

I see 2 problems with this:
1) So if I don't double up, e.g.

Code: Select all

="Y="&COUNTIF(BX2:BX28;"Y")

then

Code: Select all

SUM((MyStartingSheet.A1:MyFinalSheet.A1)

doesn't seem to be able to interpret what is there since it is not purely a number and it can't make sense of summing text.
2) The cells which I want to sum do not appear in the same place on each sheet, hence I would have to spell out each cell on each sheet as my sheet list grows larger.

Is there any way to pick the numerical value part of a cell without the text? (e.g. 23 in a result that is N=23)

... so it doesn't make sense to keep adding code.

I don't understand this. What do you mean by "keep adding code"?

Well one way to do this is to explicity sum the cells on each sheet and add them together:

Code: Select all

="Y=" &(COUNTIF(Sheet4.O3:Sheet4.O28;"Y")+COUNTIF(Sheet3.O3:Sheet3.O30;"Y")+COUNTIF(Sheet2.O3:Sheet2.O33;"Y")+COUNTIF(P3:P28;"Y")) 

but after 100 sheets this gets pretty cumbersome.

[RusselB]

I have a cell :

Code: Select all

="Y="&COUNTIF(AS2:AS28;"Y")&" N="&COUNTIF(AS2:AS28;"N")

I have another cell:

Code: Select all

="Y="&COUNTIF(BX2:BX28;"Y")&" N="&COUNTIF(BX2:BX28;"N")

I want to add the "Y" values and "N" values of these cells together and display this in the same way. I ultimately want to do this for 100 cells so it doesn't make sense to keep adding code.

Try

Code: Select all

="Y="&countifs(AS2:AS28;"Y";BX2:BX28;"Y")&" N="&countifs(AS2:AS28;"N";BX2:BX28;"N")

To get the numbers from the cells that already have the counts is possible, but ends up being a much longer (and complicated) formula

[Lupp]

A cell containing the formula

Code: Select all

=SUMPRODUCT(BX2:BX28="Y")

(you may also do it with COUNTIF) will show the result as "13" e.g. as long as it is set to 'Numbers' format Genereal or format code #0 e.g. Setting the 'Numbers' format to #0" Y" will then display "13 Y", an you can even get the (imo malformed) "Y= 13" with the help of the code "Y= "#0. This kind of formatting does not interfere with any calculations.

[leeelson]

Thanks. Not perfect, but that helps.

[RusselB]

The main problem that I can see with Lupp's suggestion, is the fact that you can't have both the Y and N calculations in the same cell, contrary to how your current formula is setup
With careful cell width adjustment, you might be able to get two cells, one with the Y calculation and one with the N calculation to look like they are in one cell.

[Lupp]

An addendum probably applicable in very rare cases of the kind:
There actually is a set of standard functions creating and evaluating texts of a specific format which represent a pair of numeric values each. I am talking of the way complex numbers are implemented in spreadsheets. However, you cannot re-format these numbers by 'Numbers' format codes for the very reason that they already are texts, technically. Reformatting them with text functions would spoil the usability with functions made for complex numbers in the mentioned sense, on the other hand.
Also note: There is no function for complex numbers allowing for cuboids on parameter places.

[leeelson]

The main problem that I can see with Lupp's suggestion, is the fact that you can't have both the Y and N calculations in the same cell, contrary to how your current formula is setup
With careful cell width adjustment, you might be able to get two cells, one with the Y calculation and one with the N calculation to look like they are in one cell.

How can I split a cell without creating an extra column?

[RoryOF]

I think you have to create an extra column, but could remove the intervening border so that the double cell looks like one cell.

[leeelson]

I think you have to create an extra column, but could remove the intervening border so that the double cell looks like one cell.

Tried this:
To split a cell into multiple cells:

Position the cursor inside the cell.
Right-click and select Cell > Split on the pop-up menu, or select Table > Split Cells from the menu bar.

Didn't work. I have too many columns so I settled for another row.

[thinman3]

leeelson....

Have a look at the way this file is laid out for rows AO30 and AO33. AO30:AR30 are individual cells as well as
AO34:AR34 . When adding new data into sheets to the right of the first sheet, the formulas in AP33 & AR33 will automatically adjust the sheet number as they are set up with RELATIVE addressing. The 1st sheet has an ABSOLUTE address so it will not increase. Column & Row addresses are also ABSOLUTE.

Just make sure to adjust the formulas to reflect any changes you make to the layout of the Columns / Rows where the
" Y " and " N " are in AP30 & AR30.

SUM of _Y_and_N_ values #2.ods

(9.26 KiB) Downloaded 105 times

[leeelson]

leeelson....

Have a look at the way this file is laid out for rows AO30 and AO33. AO30:AR30 are individual cells as well as
AO34:AR34 . When adding new data into sheets to the right of the first sheet, the formulas in AP33 & AR33 will automatically adjust the sheet number as they are set up with RELATIVE addressing. The 1st sheet has an ABSOLUTE address so it will not increase. Column & Row addresses are also ABSOLUTE.

Just make sure to adjust the formulas to reflect any changes you make to the layout of the Columns / Rows where the
" Y " and " N " are in AP30 & AR30.

SUM of _Y_and_N_ values #2.ods

Thanks for your input.
2 problems with this approach: 1)different number of rows on each page so 2)cumulative sum becomes too long after e.g. 100 sheets (SUM(sheet1.a30;sheet2.a31;sheet3.a34........;sheet100.a30))

What does work is Lupp's suggestion of putting text into the cell format so the cell itself is a number. This allows simple cumulative formulae which doesn't grow longer with each sheet.

[thinman3]

AP33 formula::: SUM($Sheet1.$AP$30:Sheet2.$AP$30)
AR33 formula::: SUM($Sheet1.$AR$30:Sheet2.$AR$30)

For each of these formulas, all you need to do is change the reference of Sheet2.$AP$30 and Sheet2.$AR$30 to reflect the
sheet number of the last sheet in the file. If Sheet54 is the last numbered sheet tab at the bottom of the screen, simply change Sheet2.$AP$30 and Sheet2.$AR$30 to Sheet54.$AP$30 and Sheet54.$AR$30. If there is no data in the sheets
between Sheet2 and Sheet54, it won't matter. It will only SUM up the numbers on the sheets that have data entered.
Look at the formula on Sheet4 in cell(s) AP33 and AR33; it references Sheet1 to Sheet 4. To see what I mean, go to Sheet3
and delete all the data in AS2:AS28 and / or BX2:BX28. The results for "Y" & "N" should only be diminished by the total
that is currently displayed on Sheet3 in AP30 and AR30.

As for the sheets having different number of rows, simply make all the sheets the same length as the longest sheet and
adjust the formulas accordingly. For instance, for the formula in either AP30 or AR30 , COUNTIF($AS$2:$BX$28;"N"); if one sheet goes to $BX$55 and another sheet goes to $BX$1000, change $BX$28 to $BX$1000.

[leeelson]

AP33 formula::: SUM($Sheet1.$AP$30:Sheet2.$AP$30)
AR33 formula::: SUM($Sheet1.$AR$30:Sheet2.$AR$30)

For each of these formulas, all you need to do is change the reference of Sheet2.$AP$30 and Sheet2.$AR$30 to reflect the
sheet number of the last sheet in the file. If Sheet54 is the last numbered sheet tab at the bottom of the screen, simply change Sheet2.$AP$30 and Sheet2.$AR$30 to Sheet54.$AP$30 and Sheet54.$AR$30. If there is no data in the sheets
between Sheet2 and Sheet54, it won't matter. It will only SUM up the numbers on the sheets that have data entered.
Look at the formula on Sheet4 in cell(s) AP33 and AR33; it references Sheet1 to Sheet 4. To see what I mean, go to Sheet3
and delete all the data in AS2:AS28 and / or BX2:BX28. The results for "Y" & "N" should only be diminished by the total
that is currently displayed on Sheet3 in AP30 and AR30.

As for the sheets having different number of rows, simply make all the sheets the same length as the longest sheet and
adjust the formulas accordingly. For instance, for the formula in either AP30 or AR30 , COUNTIF($AS$2:$BX$28;"N"); if one sheet goes to $BX$55 and another sheet goes to $BX$1000, change $BX$28 to $BX$1000.

The problem is that the cells to be summed are not always in the same row. Thus Sheet54.$AP$30 is the wrong cell (has wrong non-zero data). The proper cell is Sheet54.$AP$31 and for sheet 1001, it's Sheet1001.$AP$28. This makes my sum 1001 terms long and not feasible.

[RusselB]

Could you enter a referencing formula into a cell on each sheet (eg: on Sheet54 in cell AAA1 put =AP31) so that you have the same cell address (AAA1) on each sheet referring to the correct address?
Then you could use the SUM function as exampled by thinman
The only other alternative, is to manually insert rows/columns to make the information you want to SUM in the same row and column.
BTW: Do you really have over 1000 sheets?

[leeelson]

BTW: Do you really have over 1000 sheets?

Not yet

[thinman3]

The problem is that the cells to be summed are not always in the same row. Thus Sheet54.$AP$30 is the wrong cell (has wrong non-zero data). The proper cell is Sheet54.$AP$31 and for sheet 1001, it's Sheet1001.$AP$28. This makes my sum 1001 terms long and not feasible.

I've read and re-read your posts and have found no information as to what is being used to determine the " Y " or " N " entries; so , please provide a sample file ( with @ least 3 sheets ) with the " data " you are using , including the Y / N
results. Additionally, provide a narrative as to how the Y / N totals should be summed across multiple sheets. If the data is on different rows on different sheets, it would seem that unique data would have to be entered on Sheet1 and / or Sheet2 and / or Sheet3 to be able to arrive at a SUM for the Y / N entries.

There are many capable forum members here to help with your post; help us, help you.

[leeelson]

I've created a greatly truncated and abbreviated version. I need to know the number of "Y" and "N" values on each sheet and the cumulative number of "Y" and "N" values on that sheet + previous sheets. Several rules: no columns can be added. I need to minimize the number of rows added: they can only be added at the bottom. Also, changing the number of data rows (Y or N values) is not allowed. Moving data (Y or N values) from one sheet to another is not allowed. Although I don't show it, the Y or N values may be in different columns on different sheets. Hope this helps.

[RusselB]

Your latest post, with the restrictions you have specified, makes this impossible to my knowledge of spreadsheets

[leeelson]

Your latest post, with the restrictions you have specified, makes this impossible to my knowledge of spreadsheets

I'm getting that impression. Lupp's workaround using cell formatting works pretty well. That's what I'm using now.