Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
Bianca777 pe Simpatie.ro
 | Femeie
24 ani
Brasov
cauta Barbat
24 - 61 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
I need a formula on row 1 in a sheet to be dragged across columns and return the value in a cell in row 2, then "pause" for 3 columns, then return the value in the next cell in row 2.
I have groups of 4 columns for several questionnaires and would like the formula on row 1 to display the code of the questionnaire existing in only 1 of the 4 columns on row 2.
Example:
- in cell F2 I have the code of the 1st questionnaire which is CH-1
- in cell J2 I have the code of the 2nd questionnaire which is CH-2
- in cell N2 I have the code of the 3rd questionnaire which is CH-3
and so on for several columns
- I want a formula in row 1 to display CH-1 in range F1:I1, then automatically display CH-2 in range J1:M1 and so on
The following formula can surely be adapted for dragging across rows, but here it is for columns:
=OFFSET($F$2;0;INT((COLUMN()-COLUMN($F$1))/4)*4)
Source:
ChatGPT
|
|
| pus acum 2 saptamani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
To take this further, I need to use an INDIRECT using the values on row 1 to extract values from certain ranges from sheets named as the values in row 1.
- in cell F4 I have the following formula that gets the value from sheet CH-1 cell K6
- when dragged right across all columns pertaining to CH-1 it will get the values of cells L6, M6, and N6 respectively
- when dragged right across columns starting from CH-2 (columns J-M), it should once again get values from cells K6 to N6 from the sheet with name CH-2, and so on
- when dragged down across all required rows, the formula will get the values from cells K6, K7 and so on, L6, L7 and so on, and so on for the rest of columns in CH-1, then again from K6, K7 and so on for CH-2, and so on and so forth
=IFERROR(IF(INDIRECT("'" & F$1 & "'!" & CHAR(75 + MOD(COLUMN() - COLUMN($F$4); 4)) & (6 + ROW() - ROW($F$4))) = ""; ""; INDIRECT("'" & F$1 & "'!" & CHAR(75 + MOD(COLUMN() - COLUMN($F$4); 4)) & (6 + ROW() - ROW($F$4))));"")
Note: added the IFERROR function to prevent errors due to inexistent sheets. I only have Q1 to Q3 so far, but expect 7 in total. So I prepared the DASHBOARD that will extract all values from all sheets I will later add to this Excel file.
Source:
ChatGPT
|
|
| pus acum 2 saptamani |
|