Forum Discussion
Cell reference in formulas
As Mr. mathetes already mentioned, which I can only agree with.
Please always describe your plans step by step in your concerns, preferably with a file (without sensitive data) or photos. Always state your Excel version, operating system, yes, ask also storage medium, file extension, etc.. This way you can be helped much faster and more precisely.
To get back to your question, as far as I understand, there are two questions... I have attached what I could understand.
First part of the question: A large report has 13 columns and 300+ rows that is convert to a table. How to extract multiple data items from that table to build a new, smaller report ? (short version, as far as I understand it)
You can extract multiple data items from a large table in Excel to build a new, smaller report by using cell references in formulas. A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. In one or several formulas, you can use a cell reference to refer to:
- Data from one or more contiguous cells on the worksheet.
- Data from cells on other worksheets in the same workbook.
- Data from cells in other workbooks.
For example, if you have a table with 13 columns and 300+ rows and you want to extract data from column B and rows 5 through 10, you could use the cell reference B5:B10 in your formula. This would tell Excel to look at the data in column B and rows 5 through 10 when calculating the result of the formula. You can also use structured references when working with tables. Structured references are an easy way to refer to data in an Excel table. Instead of using cell references, such as A1 and R1C1, you can use structured references that reference table names in a formula. For example, if your table is named Table1 and you want to sum the data in column B, you could use the formula =SUM(Table1[Column2]).
Second part of the question: Try to use XLOOKUP in the formula, want use the data in the top cell of the new spreadsheet as the name in a named range in the original table, using the XLOOKUP formula below it. Can I use the word "Barbara" in that top cell as the "name of a range" part of a reference? (short version, as far as I understand it)
You can use the XLOOKUP function to search for a value in a range and return a corresponding value in another range. In your case, you can use the value “Barbara” in the top cell of your new spreadsheet as the lookup value in the XLOOKUP formula.
For example, if “Barbara” is in cell A1 of your new spreadsheet, and you want to search for that value in a named range called “Names” in your original table and return a corresponding value from another named range called “Values”, you could use the following formula:
=XLOOKUP(A1, Names, Values).
This would search for the value “Barbara” in the named range “Names” and return the corresponding value from the named range “Values”.
It’s important to note that the value in the top cell of your new spreadsheet must match one of the values in the named range you are searching.
If it doesn’t, XLOOKUP will return an error.
So much time invested, although it would be much easier if it were a little more concrete.
One should consider that this time is missing with someone else who needs just as much help. Everyone will be helped with it, even those who are not involved in this message. With your permission, here's a tip: Welcome to your Excel discussion space!
Thank you for your understanding and hope to have helped.
...if not please just ignore me.