Two questions : named range display & alternate cell

Hi Guys

I have a table assigned as a NAMED RANGE called "my_table". How do I display this in a cell, using certain formula? ie. I want a cell to display the text "my_table" if I write certain formula in it. see attached spreadsheet, task#2.

Another question.
How do I select alternate cell if they are named ODD/EVEN as explained in attached spreadsheet.

I have spent quite a few days trying to get to these but no luck.

Any help would be much appreciated. I have attached the spreadsheet with two tabs. task#1 and task#2.

Thanks
Shahab

Hi and welcome to the forum.
Is the Task1 table truly representative?

Does the count of all the values in the rows always alternate between 4 & 5 and always offset by one column?
Is the table always a 4 x 9 matrix
Are the characters in column A always and only present in each row. i.e. does 'A' only appear in row 3
Are the characters in row 2 always and only present in each column. i.e. does the 2 in C2 ' only appear in column C

THanks Richard.

actual table is pretty big. I have attached the pdf. please check page-2 as well. that should answer all your questions.
headers are fixed. A/B/C/D (be aware, some letter are missing like I etc and 1/2/3/4)


only grey cells need to be populated.
Also, the text I mentioned in column-D ie the text "Pin-A2/Pin-A4 etc"..... this text doesnt need to say A2/A4 in it. it can be any plain text. just the text belonging to column C grid should show up in corr column-D. ie if cell C3 = sample, then cell D9 should dispaly "sample" as well.
the text in column C has to be "exactly" as written in the spreadsheet.
hopefully I answer all your questions to help me get an answer.


Shahab

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context. Pictures are rarely any use.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

Pease also note that you should not use PMs to prompt for action. It is against our rules.

As to task 1 paste the following into cell M3 and drag the fill handle down to M38:

Formula:

=IF(Q3="","",INDEX(A$3:A$6,AGGREGATE(15,6,(ROW(A$3:A$6)-ROW(A$2))/(B$3:J$6=Q3),1))&INDEX(B$2:J$2,AGGREGATE(15,6,(COLUMN(B$2:J$2)-COLUMN(A$2))/(B$3:J$6=Q3),1)))

Paste the following array formula** into cell N3, follow the instructions below for activating, and then double click the fill handle to copy the formula down:

Formula:

=IFERROR(INDIRECT(TEXT(SMALL(IF((B$3:J$6<>""),ROW(A$3:A$6)*100+COLUMN(B$1:J$1),10^10),ROWS(A$1:A1)),"R0C00"),)&"","")

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

As for task 2 paste the following into cell B13 and then drag the fill handle down:

Formula:

="MY_TABLE_"&INDEX(B$2:B$8,MATCH(A13,A$2:A$8,0))

Let us know if you have any questions.

As to task #1 try the following:
For cell P2 and down:

Formula:

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,ROW(A$3:A$8)*100+COLUMN(B$1:K$1)/(B$3:K$8<>""),ROWS(A$1:A1)),"R0C00"),)&"","")

For cell M2 and down:

Formula:

=CHAR(SUM(62,AGGREGATE(15,6,ROW(A$3:A$8)/(B$3:K$8<>""),ROWS(A$1:A1))))&RIGHT(TEXT(MOD(AGGREGATE(15,6,ROW(A$3:A$8)*100+COLUMN(B$1:K$1)/(B$3:K$8<>""),ROWS(A$1:A1))/100,1),".00"),2)-1

Note that these are regular formulas, just press enter to activate.
I don't have any ideas about task #2, however I'll look back later to see if someone else has resolved it.
Let us know if you have any questions.

Task #1
For cells B100 and down:

Formula:

=CHAR(SUM(62,AGGREGATE(15,6,ROW(A$3:A$91)/(B$3:EZ$91<>""),ROWS(A$1:A1))))&RIGHT(TEXT(MOD(AGGREGATE(15,6,ROW(A$3:A$91)*1000+COLUMN(B$1:EZ$1)/(B$3:EZ$91<>""),ROWS(A$1:A1))/1000,1),".000"),3)-1

For cells C100 and down:

Formula:

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,ROW(A$3:A$91)*1000+COLUMN(B$1:EZ$1)/(B$3:EZ$91<>""),ROWS(A$1:A1)),"R00C000"),)&"","")

I don't know VBA so I can't help you there. You may want to ask one of the moderators, I see Richard is a participant in this thread, how to proceed to get help from the VBA contributors.
Let us know if you have any questions.

Try pasting the following into cell B100 and copying down to cell B6907:

Formula:

=INDEX(A$3:A$91,AGGREGATE(15,6,(ROW(A$3:A$91)-ROW(A$2))/(B$3:EZ$91<>""),ROWS(A$1:A1)))&RIGHT(TEXT(MOD(AGGREGATE(15,6,ROW(A$3:A$91)*1000+COLUMN(B$1:EZ$1)/(B$3:EZ$91<>""),ROWS(A$1:A1))/1000,1),".000"),3)-1

Let us know if you have any questions.