Hello, I need help with a formula to tell me 9 values. These values are on 9 separate tabs/worksheets. They are always at the bottom row in a specific column - but the row changes every week. I have a totals tab in this workbook. I would like to stop manually going to each tab and pressing Control+End to see the total and manually going back to the totals tab to enter the value. Is there a formula to help me find these 9 values automatically and display them on my totals tab?
I'm assuming "value" means number.
This formula will return the last (bottom-most) number from Sheet2 column A:
=LOOKUP(1E100,Sheet2!A:A)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Why not display your total on the top of your file (in the same cell) on each sheet (e.g. cell G1)?
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Yes by value I mean number. It's always a number. The 9 worksheets are automatically produced by an export and the calculated value (aka number) in the bottom-most corner is automatically calculated. I have asked for the programmers to change this to the top row (so we can save time every week), but no luck there.
The formula:
=LOOKUP(1E100,Sheet2!A:A)
Keeps changing to:
=LOOKUP(1E+100,Sheet2!A:A)
and it's opening a new window... and it wants me to open a file.
The change to 1E+100 is OK.
It's opening a window because it can't find a sheet named Sheet2 and it wants you to select a file that does contain Sheet2. You need to add the appropriate sheet name. Replace Sheet2 with whatever sheet name you want to perform the calculation on.
If the sheet name contains space characters or numbers then use single quotes around the name:
=LOOKUP(1E100,'My Sheet'!A:A)
=LOOKUP(1E100,'25'!A:A)
Re: Find and display a specific field
I tried that.. putting the name of the sheet in where it says Sheet 2... and it still wants to open a file.
I tried that... opened up the file (that is already open) and then selected the correct tab... It changed the formula to this:
=LOOKUP(1E+100,'[Sheet 2]Sheet 2'!A:A)
The plus symbol will not go away, but this formula does give me the result I need!!! Thank you!!!
1E+100 is scientific notation, a shorthand method of expressing very long numbers.
1E+100 is the number 1 followed by 100 zeros.
So, instead of typing in:
100000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000
We can simplify it to 1E+100.
Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks